Thread: i have some question regarding to blood bank management system database

Originally Posted by tryharder1

The way to do so is as shown below, right?

Why have you just now created a BloodBags table? You already have blood bags in the inventory table. One blood bag per record in the inventory table.

Can i know whether there is better way to do this or usually combine blood group and blood type together by creating a new table?

Code:

Donors : donorID, bloodGroupID, bloodTypeID, ..etc
BloodGroups : bloodGroupID, remarks..etc
BloodTypes : bloodTypeID, remarks..etc

or

Code:

Donors : donorID, bloodID, ..etc
Bloods : bloodID, bloodGorup, bloodTypeID, remarks..etc
BloodTypes : bloodTypeID, remarks..etc

What is a blood type and how is it different from a blood group?

Depending on your needs you may not need that level of granularity on your data. A BloodGroup table could have the following simple structure:

Code:

BloodGroupID, varchar(3), primary key

And the data for this table could be:

Code:

A+
A-
B+
B-
AB+
AB-
O+
O-

This would cover all blood groups.
You could then put a BloodGroupID field on the Donors table alone. Because the BloodInventory table will point indirectly to the Donors table through the Appointments table, you can always establish a JOIN to get to the blood group.

Code:

BloodGroups : BloodGroupID
Donors : DonorsID, BloodGroupID
Appointments : AppointmentID, DonorID, Date, Time
BloodInventory : BloodInventoryID, AppointmentID

With those fields alone, you have all your basic structure set and ready to take the foreign keys to establish the relationship between the table.
You just need 3:

Code:

ALTER TABLE Donors
    ADD CONSTRAINT Donors_fk_BloodGroupID  FOREIGN KEY (BloodGroupID) REFERENCES BloodGroups(BloodGroupID)
    ON UPDATE CASCADE ON  DELETE RESTRICT;
ALTER TABLE Appointments
    ADD CONSTRAINT Appointments_fk_DonorID FOREIGN KEY (DonorID) REFERENCES Donors(DonorID)
    ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE ONLY BloodInventory
    ADD CONSTRAINT BloodInventory_fk_AppointmentID FOREIGN KEY (AppointmentID) REFERENCES Appointments(AppointmentID)
    ON UPDATE CASCADE ON DELETE RESTRICT;

This way:

Code:

BloodGroups 1:N Donors
Donors 1:N Appointments
Appointments 1:1 BloodInventory

To make sure that the last relationship is a 1:1 and not a 1:N (meaning that of course 1 appointment can only generate one blood entry in the inventory), when you are creating the BloodInventory table, the AppointmentID field must have a UNIQUE constraint. Alternatively, you don't need the BloodInventoryID field and you can just make AppointmentID the primary key of the BloodInventory table.

EDIT: pardon, the relationship between Appointments and BloodInventory is actually "1:0 or 1", not "1:1". An appointment may be cancelled and not generate an entry in the inventory. The above establishes exactly that possibility.

I don't know what a seeker is and I don't care. I think between these two pages or replies and your own study of database design, you can finish whatever you are doing.
Enough.