Attachment 14968
1st, donor can online make appointment to make donation.
2nd, the donation detail will stored in the related blood bank inventory where the bloodbank of donor donate blood.
Anyone familiar with this kind of system can give me some guideline?
I think there are some problem existed on my diagram, any advice will be appreciated. Thanks in advance.
I'm sorry if anything there is not clear. I will improve it.
It worries me somewhat that the diagram is so circular. It implies that the process can start anywhere. Also I think there is a mistake. There are many appointments at many blood banks, and many to many relationships are usually split up with another table - perhaps organize appointments into Blood Drives? Many appointments to one blood drive, and many blood drives to one bank.
You have some unnecessary tables.
- The tables "Blood inventory" and "Blood" are the exact same thing. They both contain blood samples. So I would drop "Blood".
- The table "Donation" doesn't seem to do anything that can't be done by the Appointments table. A "confirmed" or "donated" field on this last table will confirm the Appointment to have resulted in a donation.
- What distinguishes inventories in a Blood Bank? If a Blood Bank doesn't have more than one inventory of blood, there's no need for a "Blood Inventory" table.
The resulting diagram after these three changes would be:
Code:Donors -||---0< Appointments
A Donor has several Appointments.
Blood Bank -||---0< Appointments
A Blood Bank has several Appointments.
Appointments -||---0|- Samples
An Appointment can have 0 or 1 Samples.
Thanks for your feedback.Quote:
Originally Posted by Mario F.
I put donation table is because i need to know when the donor donated such as donationdate,bloodtype,quantity,etc.
For blood inventory, lets say i have more than one blood bank, and blood black is a location where the blood stored so i need to keep track of the blood availability of the blood bank.
The blood drive is it serves as inventory where the blood being stored?Quote:
Originally Posted by whiteflags
No, it would literally just be a table with blood drive id, appointment id and blood bank id.
Oh, now i confused where should i placed the inventory to store blood details.
Could we make this more concrete by adding some fields? I don't know much about this so the following is just meant as a starting point.
As a simplification I've assumed that the blood samples are stored at the appointment location and are never moved.Code:Location: location_id, address, ...
Donor: donor_id, name, blood_type, ...
Appointment: appt_no, location_id, donor_id, date, ...
Sample: appt_no, amount, ...
If an Appointment has no associated Sample then there won't be a Sample record with that appt_no.
About the many-to-many relationship between appointments and locations, shouldn't it just be one-to-many? An appointment has one location. A location has many appointments.
Yes, my blood bank is a location. One blood bank got many appointment, and an appointment has only one blood bank. The problem is i need to keep of bloood and where it is suitable to be placed? Is it links with appointment or blood bank? And my donor attribute has blood group so do i still need blood id attribute in appointment?
If you want to start with a concrete example, instead of doodling random diagrams on a computer, then I suggest you start with a pack of 5x3 Index cards.
On 4 of them, write the names
Fred
Barney
Wilma
Betty
These are your donors. You can then list other attributes on the card such as blood type.
Then imagine what an Appointment would look like? Who is involved? Where are the M:N relationships?
Repeat this process to complete your diagram.
Anyone can help me whether my attribute is correct & also my entity? Any feedbacks will be appreciated, thanks.Code:Blood Bank (Location) : bloodbankID, name, address, contact, ...
Appointment : appointmentID, donorID, bloodbankID, date, time, ...
Donor : donorID, name, address, blood group, status, ...
Appointment Details : appointmentID, qty, desc
Blood Type : bloodType, desc, blood type, qty
Blood Inventory : bloodID, bloodType, bloodbankID, expirationDate, desc ...
It's looking better.Quote:
Originally Posted by tryharder1
A few notes:
--------------
- "Appointment Details" has a 1:1 relationship with Appointment, for which reason you don't really need the former table. Move all fields in "Appointment Details" to "Appointments"
- "Appointments", not Appointment. "Blood Types" not Blood Type. "Blood Banks", not Blood Bank, etc. Get into the habit of pluralizing your table names. Or use names with the meaning of a collective, like Blood Inventory.
- "Donors" should have a bloodtypeID field, not a bloodgroup field.
- Blood Inventory should have a single primary key BloodInventoryID. Each entry in this table is a blood bag. The relationship between BloodTypes, BloodBanks and Donors (which you are missing) with this table is a 1:N relationship.
Other notes:
--------------
- Tables names should not have spaces.
- Names should be CamelCase, or lowercase_with_underscores. Depending on the server.
- For simplicity, ID fields are always called simply ID (or id) when they are the primary key. On the secondary tables, the relationship is established with the singular form of the table name. Like so:
table: customers
id, integer
first_name, varchar(50)
last_name, varchar(50)
table: invoices:
id, integer
inv_date, date
customer, integer
So, the customer field in table invoices, is the customer id in table customers.
The foreign key would look something like:
ALTER TABLE invoices ADD CONSTRAINT invoices_fk_customer FOREIGN KEY (customer) REFERENCES customers(id) ON UPDATE CASCADE ON DELETE RESTRICT
Some people may prefer customer_id, or CustomerID in the invoices table. I find that redundant. But that would be ok too.
Okay, thank you.
Can i know what do you mean by "The relationship between BloodTypes, BloodBanks and Donors (which you are missing) with this table is a 1:N relationship."
Is that mean i need to create new table or the relationship between them is incorrect?
You were missing a DonorsID field in the BloodInventory table.
This way you can later create the foreign key that links DonorsID in the BloodInventory table with DonorsID in the Donors table, creating a relationship between a blood bag and its donor.
Oh, thanks for remind.
The way to do so is as shown below, right?
Code:Donors : donorID, name, address, bloodTypeID, status, ...
BloodBags : donorID, bloodInventoryID, ..etc
BloodInventory : bloodInventoryID, bloodTypeID, bloodbankID, expirationDate, desc ...