Blood Bank Information System Database Design

Blood Bank Information System Database Design

The capstone project entitled “Blood Bank Information System” is a database system that records and manages the transactions of blood donation and blood issuance. The main purpose of this system is to keep an organize records management of blood inventory. It would be a great help in the properly monitoring of blood available in the blood bank and for easy processing of blood request.

This article will provide you with the list of tables and field/columns for every table in the design of database structure/schema of blood bank information system. The team will later provide a video tutorial on how to create the database in PHPMyAdmin.

This database design has 8 tables with their respective fields and columns as well as their relationships among each other.

Blood Bank Information System Database Design - List of Database Tables — Blood Bank Information System Database Design – List of Database Tables

tbl_blood_group – the different of blood types will be encoded and stored in this table. The blood group table has 4 columns

blood_group_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
blood_group_name – name of the blood group (A, AB, O, etc)
description – important notes and details about the blood type.
user_id – the user who encodes, manage and update the blood group information. It is a foreign key that connects to the user table.

Create SQL Statement – the statement below is used to create the tbl_blood_group, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_blood_group` (
`blood_group_id` int(11) NOT NULL,
`blood_group_name` varchar(5) NOT NULL,
`description` varchar(100) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_donor – blood donor information is stored and managed on this table. The donor table of the blood bank information system is the profiling record of donors and it has 13 columns.

donor_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
donor_complete_name – complete name of the donor.
donor_phto – profile image of the donor uploaded to the system.
blood_group_id – this is the foreign key that links to the blood group table.
complete_address – complete address of the donor.
region – the name of the region.
province – the name of the province.
city – the name of the city.
contact – contact information (landline, mobile phone number).
email_address – personal email address of the donor.
age – age of the donor.
gender – gender of the donor.
remarks – other important and relevant health information about the donor.
user_id – the user who encodes, manage and update the donor information. It is a foreign key that connects to the user table.

Create SQL Statement – the statement below is used to create the tbl_donor, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_donor` (
`donor_id` int(11) NOT NULL,
`donor_complete_name` varchar(30) NOT NULL,
`donor_photo` blob NOT NULL,
`blood_group_id` int(11) NOT NULL,
`complete_address` varchar(100) NOT NULL,
`region` varchar(50) NOT NULL,
`province` varchar(50) NOT NULL,
`city` varchar(30) NOT NULL,
`contact` varchar(15) NOT NULL,
`email_address` varchar(30) NOT NULL,
`age` int(3) NOT NULL,
`remarks` varchar(100) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_recipient – the donated blood will proceed to those people who are in need of blood transfusion or the blood recipients. The blood bank information system stores the personal information of the recipient in the tbl_recipient 13 columns.

recipient_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
recipient _complete_name – complete name of the recipient.
recipient_phto – profile image of the recipient uploaded to the system.
blood_group_id – this is the foreign key that links to the blood group table.
complete_address – complete address of the recipient.
region – the name of the region.
province – the name of the province.
city – the name of the city.
contact – contact information (landline, mobile phone number).
email_address – personal email address of the recipient.
age – age of the recipient.
gender – gender of the recipient.
remarks – other important and relevant health information about the recipient.
user_id – the user who encodes, manage and update the recipient information. It is a foreign key that connects to the user table.

Create SQL Statement – the statement below is used to create the tbl_ recipient, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_recipient` (
`recipient_id` int(11) NOT NULL,
`recipient_complete_name` varchar(30) NOT NULL,
`recipient_photo` blob NOT NULL,
`blood_group_id` int(11) NOT NULL,
`complete_address` varchar(100) NOT NULL,
`region` varchar(50) NOT NULL,
`province` varchar(50) NOT NULL,
`city` varchar(30) NOT NULL,
`contact` varchar(15) NOT NULL,
`email_address` varchar(30) NOT NULL,
`age` int(3) NOT NULL,
`remarks` varchar(100) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_blood_collection – the blood bank information system stores the blood collected from donors in this table. The blood collection table has 9 columns.

blood_collection_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
donor_id – this is the information of the donor that links to the donor table of the blood bank information system.
hospital – it refers to the hospital or clinic where the blood donation occur.
blood_group_id – this is the blood type of the donor that links to the blood group table.
no_of_bags – number of bags collected from the donor.
date_of_collection – the date of blood collection.
nurse_doctor_in_charge – the nurses or doctor in-charge of the blood collection from the donor.
remarks – any important or relevant information about the blood donation.
user_id – the user who encodes, manage and update the blood collection information. It is a foreign key that connects to the user table.

Create SQL Statement – the statement below is used to create the tbl_ blood_collection, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_blood_collection` (
`blood_collection_id` int(11) NOT NULL,
`donor_id` int(11) NOT NULL,
`hospital` varchar(100) NOT NULL,
`blood_group_id` int(11) NOT NULL,
`no_of_bags` float NOT NULL,
`date_of_collection` datetime NOT NULL,
`nurse_doctor_in_charge` varchar(30) NOT NULL,
`remarks` varchar(100) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_blood_request – request for blood will be encoded, stored and archived in this table of blood bank information system. The blood request table has 12 columns.

request_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
control_number – control number of the transaction or request.
requested_by – the name of the person who requested for blood.
recipient_id – the name of the person who is need of blood. This is a foreign key that links to the recipient table.
date_of_request – the date the request was processed.
blood_group – type of blood needed. This is a foreign key that connects to the blood group table.
no_of_bags – number of blood bags needed by the recipient.
amount_per_bag – the cost per bag.
purpose – the reason why the blood is needed.
request_status – this columns has a value of approved or declined.
remarks – important notes or message about the transaction.
user_id – the user who encodes, manage and update the recipient information. It is a foreign key that connects to the user table.

Create SQL Statement – the statement below is used to create the tbl_ blood_request, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_blood_request` (
`request_id` int(11) NOT NULL,
`control_number` varchar(15) NOT NULL,
`requested_by` varchar(30) NOT NULL,
`recipient_id` int(11) NOT NULL,
`date_of_request` datetime NOT NULL,
`blood_group` int(11) NOT NULL,
`no_of_bags` int(3) NOT NULL,
`amount_per_bag` float NOT NULL,
`purpose` varchar(100) NOT NULL,
`request_status` int(1) NOT NULL,
`remarks` varchar(100) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_blood_issued – the blood bank information system also stores and archive records of issued blood to their recipients. The blood issued table has 7 columns.

blood_issue_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).

request_id – it refers or connects to the blood request table. The protocol states that it cannot issue a blood if there is no record of request.
issued_by – person who issued the blood.
issued_date – date the blood was given.
issued_to – the person or receiver of the blood.
amount_paid – amount paid by the receiver.
user_id – the user who encodes, manage and update the blood issued information. It is a foreign key that connects to the user table.

Create SQL Statement – the statement below is used to create the tbl_ blood_issued, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_blood_issued` (
`blood_issue_id` int(11) NOT NULL,
`request_id` int(11) NOT NULL,
`issued_by` varchar(30) NOT NULL,
`issued_date` datetime NOT NULL,
`issued_to` varchar(30) NOT NULL,
`amount_paid` float NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_user – this table will store the information of personnel who can access the records of the system.

user_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
username – username of the personnel used to login together with the password.
password – password of the personnel used to login together with the username.
avatar – this will hold the profile image of the user.
fullname – the complete name of the personnel or user.
contact – contact number of the personnel (mobile/cellphone number).
email – email address of the personnel/user.
user_category_id – the user group or category of the user.
status – the value of this column is 0 or 1, 0 means deactivated or inactive, 1 is activated or active.

Create SQL Statement – the statement below is used to create the tbl_user, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_user` (
`user_id` int(11) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`avatar` blob NOT NULL,
`fullname` varchar(50) NOT NULL,
`contact` varchar(15) NOT NULL,
`email` varchar(30) NOT NULL,
`user_category_id` int(1) NOT NULL,
`status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_user_group – this table store the information of the user group which includes the functions they can and can’t access in the system.

user_group_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
group_name – name of the category or user group.
description – information on what the user group is all about.
allow_add – this column is to allow or prevent user from adding a record.
allow_edit – this column is to allow or prevent user from editing or updating a record.
allow_delete – this column is to allow or prevent user from removing or deleting a record.
allow_print – this column is to allow or prevent user from printing a report.
allow_import – this column is to allow or prevent user from importing records to the system.
allow_export – this column is to allow or prevent user from exporting records from the system.

Create SQL Statement – the statement below is used to create the tbl_user_group, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_user_group` (
`user_group_id` int(11) NOT NULL,
`group_name` varchar(30) NOT NULL,
`description` varchar(50) NOT NULL,
`allow_add` int(1) NOT NULL,
`allow_edit` int(1) NOT NULL,
`allow_delete` int(1) NOT NULL,
`allow_print` int(1) NOT NULL,
`allow_import` int(1) NOT NULL,
`allow_export` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Indexes for dumped tables

--
-- Indexes for table `tbl_blood_collection`
--
ALTER TABLE `tbl_blood_collection`
ADD PRIMARY KEY (`blood_collection_id`),
ADD KEY `user_id` (`user_id`),
ADD KEY `blood_group_id` (`blood_group_id`),
ADD KEY `donor_id` (`donor_id`);

--
-- Indexes for table `tbl_blood_group`
--
ALTER TABLE `tbl_blood_group`
ADD PRIMARY KEY (`blood_group_id`),
ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `tbl_blood_issued`
--
ALTER TABLE `tbl_blood_issued`
ADD PRIMARY KEY (`blood_issue_id`),
ADD KEY `request_id` (`request_id`),
ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `tbl_blood_request`
--
ALTER TABLE `tbl_blood_request`
ADD PRIMARY KEY (`request_id`),
ADD KEY `user_id` (`user_id`),
ADD KEY `recipient_id` (`recipient_id`),
ADD KEY `blood_group` (`blood_group`);

--
-- Indexes for table `tbl_donor`
--
ALTER TABLE `tbl_donor`
ADD PRIMARY KEY (`donor_id`),
ADD KEY `blood_group_id` (`blood_group_id`),
ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `tbl_recipient`
--
ALTER TABLE `tbl_recipient`
ADD PRIMARY KEY (`recipient_id`),
ADD KEY `blood_group_id` (`blood_group_id`),
ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `tbl_user`
--
ALTER TABLE `tbl_user`
ADD PRIMARY KEY (`user_id`),
ADD KEY `user_category_id` (`user_category_id`);

--
-- Indexes for table `tbl_user_group`
--
ALTER TABLE `tbl_user_group`
ADD PRIMARY KEY (`user_group_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_blood_collection`
--
ALTER TABLE `tbl_blood_collection`
MODIFY `blood_collection_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_blood_group`
--
ALTER TABLE `tbl_blood_group`
MODIFY `blood_group_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_blood_issued`
--
ALTER TABLE `tbl_blood_issued`
MODIFY `blood_issue_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_blood_request`
--
ALTER TABLE `tbl_blood_request`
MODIFY `request_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_donor`
--
ALTER TABLE `tbl_donor`
MODIFY `donor_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_recipient`
--
ALTER TABLE `tbl_recipient`
MODIFY `recipient_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_user`
--
ALTER TABLE `tbl_user`
MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_user_group`
--
ALTER TABLE `tbl_user_group`
MODIFY `user_group_id` int(11) NOT NULL AUTO_INCREMENT;

Blood Bank Information System Database Design

Constraints for dumped tables

--
-- Constraints for table `tbl_blood_collection`
--
ALTER TABLE `tbl_blood_collection`
ADD CONSTRAINT `tbl_blood_collection_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_blood_collection_ibfk_2` FOREIGN KEY (`blood_group_id`) REFERENCES `tbl_blood_group` (`blood_group_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_blood_collection_ibfk_3` FOREIGN KEY (`donor_id`) REFERENCES `tbl_donor` (`donor_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tbl_blood_group`
--
ALTER TABLE `tbl_blood_group`
ADD CONSTRAINT `tbl_blood_group_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tbl_blood_issued`
--
ALTER TABLE `tbl_blood_issued`
ADD CONSTRAINT `tbl_blood_issued_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_blood_issued_ibfk_2` FOREIGN KEY (`request_id`) REFERENCES `tbl_blood_request` (`request_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tbl_blood_request`
--
ALTER TABLE `tbl_blood_request`
ADD CONSTRAINT `tbl_blood_request_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_blood_request_ibfk_2` FOREIGN KEY (`recipient_id`) REFERENCES `tbl_recipient` (`recipient_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_blood_request_ibfk_3` FOREIGN KEY (`blood_group`) REFERENCES `tbl_blood_group` (`blood_group_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tbl_donor`
--
ALTER TABLE `tbl_donor`
ADD CONSTRAINT `tbl_donor_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_donor_ibfk_2` FOREIGN KEY (`blood_group_id`) REFERENCES `tbl_blood_group` (`blood_group_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tbl_recipient`
--
ALTER TABLE `tbl_recipient`
ADD CONSTRAINT `tbl_recipient_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_recipient_ibfk_2` FOREIGN KEY (`blood_group_id`) REFERENCES `tbl_blood_group` (`blood_group_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tbl_user`
--
ALTER TABLE `tbl_user`
ADD CONSTRAINT `tbl_user_ibfk_1` FOREIGN KEY (`user_category_id`) REFERENCES `tbl_user_group` (`user_group_id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

Free Download .sql file

Our team can modify the project based on your specific business requirements.

You may visit our facebook page for more information, inquiries and comments.

Hire our team to do the project.

Post navigation

Similar Articles

IPO Model Conceptual Framework of Customer Feedback Information System

Herbal Information System Chapter 4 Methodology

Legal Age Checker in CSharp