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.
Sorry, it was my mistake. After i edited the post once yesterday and i unable to edit it for the second times.
Thanks for your guidance and time.
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?
orCode:Donors : donorID, bloodGroupID, bloodTypeID, ..etc
BloodGroups : bloodGroupID, remarks..etc
BloodTypes : bloodTypeID, remarks..etc
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?
Oh ya, sorry forgot to mention it.
Bloodgroup is A,B,AB,O...
Bloodtype is positive, negative..
This is because I need to keep track of the blood details like O+ in my blood inventory.
So that i can't put bloodgroup attribute and bloodtype attribute in my donors table right?
Depending on your needs you may not need that level of granularity on your data. A BloodGroup table could have the following simple structure:
And the data for this table could be:Code:BloodGroupID, varchar(3), primary key
This would cover all blood groups.Code:A+
A-
B+
B-
AB+
AB-
O+
O-
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.
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.Code:BloodGroups : BloodGroupID
Donors : DonorsID, BloodGroupID
Appointments : AppointmentID, DonorID, Date, Time
BloodInventory : BloodInventoryID, AppointmentID
You just need 3:
This way: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;
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.Code:BloodGroups 1:N Donors
Donors 1:N Appointments
Appointments 1:1 BloodInventory
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.
Okay.
And if i have a seeker it will link to the BloodGroups table? E.gCode:Donors : donorID, bloodGroupID ...etc
BloodGroups : bloodGroupID, quantity ..etc
BloodBanks: bloodBankID,..etc
Appointments : appointmentID, bloodBankID, donorID..etc
BloodInventory : bloodInventoryID, bloodBankID, appointmentID,..etc
Code:Seeker : seekerID,..
BloodRequests : seekerID, bloodGroupID, qty...
BloodGroups : bloodGroupID...
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.
My guess is a seeker is a person in the queue waiting for blood.
Also, OP's user name is ironic.
Sorry, i just want to ensure everything go in right way since no one there to tell me.
If my question bothered you guys then i apologize for it and thank you.