Designing a database for blood donors

[iancg1]

Good day all! I'm Ian, a youth volunteer from the Philippine Red Cross. We have this program called Pledge 25 where we recruit young people (between 18 to 25 years of age) to be regular blood donors and pledge to donate blood at least 3 to 4 times a year.

Despite the intensive blood drives, our blood bank still runs out of precious blood during emergencies. Many people still die due to not enough blood. To remedy this, I thought that a database could be made from Pledge 25 donors so that they could easily be contacted in case of emergency. Also, the database would serve the second purpose of monitoring their donations so they can receive their awards (little tokens of appreciation) on time.

I only have experience with Excel, and I read at the Microsoft website that Access would be better for this kind of database since a lot of information is involved. Can anyone please help our cause and help design the database? I read up about normalization of the database but I don't know how to do that in this situation, given my limited background with databases (I'm a Psychology student).

The following information need to be in the database:

Personal Information:
First Name
Middle Name
Last Name
City Address
Home Address
Date of Birth
Blood Type
ID Number (each pledge 25 member is given a unique ID number)
Contact Number
E-Mail Address

Serial Number and Date of Donation:
Numbering (to count their donations)
Serial Number (each unit of blood their donate is assigned a unique serial number)

Awards:
Type of Award
Date Issued
Date Received

I attached the form that we use to give a better picture.

Thank you in advance for helping us!

 

[Galaxiom]

All the Personal infromation should be fields in a table. I would call it Donor.
You could use the ID number as a natural Primary Key (PK).

A list of all blood groups should be a table. This will allow it to be used as a dropdown selection when adding new donors. The case is probably marginal but my preference would be to use a BloodTypeID as a PK.

Alternatively the blood type could be stored as a text field in the Donor table. My way would have BloodTypeID and BloodTypeName in the BloodType table and the ID would be used for the blood type value in the Donor table records.

Donation table should have the DonationSerial (PK), DonorID (FK) and DonationDate. An opening number of donations would be recorded (in the Donor table) but this would remained fixed with new donors default to zero. The ongoing number of donations would be calculated as required from a count of the records added to that opening count.

AwardType should be a table with AwardTypeID (PK) and AwardName. This will also be used as a dropdown.

Award table will have AwardID (PK), AwardTypeID (FK), IssueDate, ReceiveDate, DonorID (FK).

 

[iancg1]

Thanks for replying. PK is Primary Key right? What is FK? Sorry, I'm very new to this.

 

[RainLover]

Foreign Key

 

[iancg1]

Thanks. For the Blood Type table, there are only three basic types, A, B, and 0, and each can either be Rh (+) or (-). Which will be BloodTypeID and BloodTypeName?

 

[Galaxiom]

Foreign Key is the Primary Key from another table. It is used to refer to a record from the other table. For example the Donor table BloodTypeID is recorded as an FK which is the value from the PK of the BloodType table.

 

[iancg1]

How do I assign a foreign key?

 

[Galaxiom]

Thanks. For the Blood Type table, there are only three basic types, A, B, and 0, and each can either be Rh (+) or (-). Which will be BloodTypeID and BloodTypeName?

I would record:
1 | A+
2 | A-
3 | B+
4 | B-
5 | 0+
6 | 0-

However my recollection is there is also AB+ and AB- but perhaps these are very rare in your poulation.

 

[Galaxiom]

How do I assign a foreign key?

FK is not a special assignment. It is simply a number field.

In the Database Relationships it is joined to the appropriate PK.

 

[iancg1]

Thanks, I'm sorry that includes AB+ and AB- so that makes 8

 

[iancg1]

This might be a problem, donors become full-fledged members when they make their first donation, which must be recorded. However, their ID card (with their ID number) will be given to them around a week later. Can their donation be recorded even if they don't have an ID number yet?

 

[Galaxiom]

This might be a problem, donors become full-fledged members when they make their first donation, which must be recorded. However, their ID card (with their ID number) will be given to them around a week later. Can their donation be recorded even if they don't have an ID number yet?

OK. Use a synthetic Primary Key for the Donor table.
This is an automumbered field and is used for the relationships to FKs in other tables.

The DonorNumber can be recorded later as an ordinary field.
Set this field's properties: Indexed (No Duplicates)
This will prevent the same number vbeing assinged twice.

There are advantages to using an autonumbered PK.

 

[iancg1]

Thanks. So now the synthetic DonorID (autonumbered) will be the one related with the other tables and not the ID number issued by our office?

 

[Galaxiom]

Yes. That is right.
The autonumber key is only meaningful to the database and is completely "behind the scenes". An autonumber field should never have any meaning to the user because is cannot be controlled.

 

[iancg1]

Thanks. At this point are the tables good to go?

 

[iancg1]

By the way, what does "reinforce referential integrity" mean when creating relationships?

 

[iancg1]

Can someone check if I have the relationships correct? Thanks!

 

[iancg1]

How do I know if I should use a one-to-one, one-to-many, or many-to-many relationship?

 

[Galaxiom]

Unfortunately I can't read the ImageShack image.
You can attach directly to your post in the Advanced editor view.

Better still post a sample of the database here. We can checkout the field properties properly. If you alreay have a lot of data in the table then delete most of it then compact the database. You can also zip it which make it a tiny upload.

 

[iancg1]

Hi there, I attached the database as a zip file. I appreciate the feedback.