I have had a look at your database and you are definitely on the right track. I have basically brainstormed the concept after the only absolutely essential change listed first. You might well discard some ideas but I would highly recommend the separate address table.
The limit is really your imagination. The importance of the systems analysis of the database purpose cannot be overemphasised in any design and will save a lot of work modifying it later. Talk to the people who use the system to discuss the kind of information they think might be useful.
Donor.BloodType must be an Number field because it is related to BloodType.BloodTypeID which is a number.
A Comment field on the Donor table might be useful to record any general information.
Maybe add a Checkbox to the Donor table to indicate Unvailable. When a Donor has not been able to be contacted by you this box would be ticked. When looking for a donation these records would be sorted to the bottom of the potenial donors. Could save time when it matters most.
Better still record an availability code. 1 = Always, 2 = Usually, 3 = Sometimes, 4 = Rarely, 5 = Deceased or something similar. This way donors can be sorted by who is easiest to find and it may save precious minutes in an emergency.
Inactive donors remain on file but have a low ranking. Althought it might sound crazy to keep dead people on file it can be a useful campaign tool to encourage their children to keep up a family tradition later. Always try to think beyond the immediate purpose because users always do once they start using the database.
The rankings could also be achieved by recording every attempt to contact a donor in a ContactSuccess table and indicating success or otherwise. This would rely on operators using it properly. Probably a bit advanced just yet.
Addresses usually have multiple fields allocated for AddressLine1, AddressLine2, City etc
Multiple address lines facilitate formatting address labels for postal correspondence. This also allows you to search by City which would be quite complicated in a single combined address field.
Since you are using multiple adresses for each Donor the addresses are best moved out into a separate table with multiple records for each donor. This will allow you any number of addresses per donor.
I would also add the phone number for each location to this table incase their mobile is not responding. Someone at the address may be able to advise you as to the donors whereabouts.
The Addresses table might have fields:
DonorID (FK) Identifies the associated Donor.
Address1
Address2
State
City
Phone
Type
The Type field populated with a FK from an AddressType table in much the same way as the BloodType table is related to Donor.BloodTypeID.
It might include the fields like:
1 | Correspondence
2 | Local
3 | Rural
4 | Relation
5 | Other
6 | Redundant
This way you can search for the type of address. For example only local addresses might be shown when the patient condition does not allow time for a journey from the rural region. Many designers never provide a way to delete a recod but simply flag it obsolete. This prevents accidental disasters.
New developers often fear that the database will grow too large if old data is retained but with good design several million records are not a problem.
I would also consider adding a Journey field to this table to indicate how long it would take for the donor to reach the clinic. Closest could be called first in emergency.
A text Comments field in the Address table might be useful too.
For example it might record: Telephone at neighbour's house. Will ring back.
Things like best contact times are useful when the location might be unattended during the day due to work comittements for example.
You might even consider adding a field to record when the person is most likely to be found at this address. Checkbox fields like WeekendOnly can be useful. (Access can recognise days of the week from Dates very easily and knows what day it is currently.)
Maybe later you could consider another table for a searchable series of dates to indicate when they have indicated they will be or are usually at an address but that is probably a bit advanced for you just yet.
In fact the ContactSuccess table mentioned earlier would be connected into the address records and with some advanced programming it could be possible for the database to learn the liklihood of finding a donor at a particular address based on the date and time. But perhaps I am jumping too far ahead now.
However in this case I would definitely add an AddressID PK field to the Address table since the ContactSuccess table would need to relate to it. Probably a good idea to add it now in anticipation of the future.
Usually it is a good idea to reduce the field sizes where possible. In the case of BloodType, field size could be Byte since this is the smallest type (allows for up to 255 values). Although it does not reduce the size of the storage on disk I believe it does reduce the RAM allocated and slightly speeds up the access to records.
Similarly you could reduce the name and address lines from the default 255 to a more appropriate value. This helps keeps users from entering too much into the field for display.
Spaces in field and table names are best avoided so use DonationDate rather than [Date of Donation]. It saves a lot of typing especially those brackets. Indeed avoid space in all onject names including the database filename itself.
BTW, Keep the file name to eight characters maximum for best results on network connections too.
The limit is really your imagination. The importance of the systems analysis of the database purpose cannot be overemphasised in any design and will save a lot of work modifying it later. Talk to the people who use the system to discuss the kind of information they think might be useful.
Donor.BloodType must be an Number field because it is related to BloodType.BloodTypeID which is a number.
A Comment field on the Donor table might be useful to record any general information.
Maybe add a Checkbox to the Donor table to indicate Unvailable. When a Donor has not been able to be contacted by you this box would be ticked. When looking for a donation these records would be sorted to the bottom of the potenial donors. Could save time when it matters most.
Better still record an availability code. 1 = Always, 2 = Usually, 3 = Sometimes, 4 = Rarely, 5 = Deceased or something similar. This way donors can be sorted by who is easiest to find and it may save precious minutes in an emergency.
Inactive donors remain on file but have a low ranking. Althought it might sound crazy to keep dead people on file it can be a useful campaign tool to encourage their children to keep up a family tradition later. Always try to think beyond the immediate purpose because users always do once they start using the database.
The rankings could also be achieved by recording every attempt to contact a donor in a ContactSuccess table and indicating success or otherwise. This would rely on operators using it properly. Probably a bit advanced just yet.
Addresses usually have multiple fields allocated for AddressLine1, AddressLine2, City etc
Multiple address lines facilitate formatting address labels for postal correspondence. This also allows you to search by City which would be quite complicated in a single combined address field.
Since you are using multiple adresses for each Donor the addresses are best moved out into a separate table with multiple records for each donor. This will allow you any number of addresses per donor.
I would also add the phone number for each location to this table incase their mobile is not responding. Someone at the address may be able to advise you as to the donors whereabouts.
The Addresses table might have fields:
DonorID (FK) Identifies the associated Donor.
Address1
Address2
State
City
Phone
Type
The Type field populated with a FK from an AddressType table in much the same way as the BloodType table is related to Donor.BloodTypeID.
It might include the fields like:
1 | Correspondence
2 | Local
3 | Rural
4 | Relation
5 | Other
6 | Redundant
This way you can search for the type of address. For example only local addresses might be shown when the patient condition does not allow time for a journey from the rural region. Many designers never provide a way to delete a recod but simply flag it obsolete. This prevents accidental disasters.
New developers often fear that the database will grow too large if old data is retained but with good design several million records are not a problem.
I would also consider adding a Journey field to this table to indicate how long it would take for the donor to reach the clinic. Closest could be called first in emergency.
A text Comments field in the Address table might be useful too.
For example it might record: Telephone at neighbour's house. Will ring back.
Things like best contact times are useful when the location might be unattended during the day due to work comittements for example.
You might even consider adding a field to record when the person is most likely to be found at this address. Checkbox fields like WeekendOnly can be useful. (Access can recognise days of the week from Dates very easily and knows what day it is currently.)
Maybe later you could consider another table for a searchable series of dates to indicate when they have indicated they will be or are usually at an address but that is probably a bit advanced for you just yet.
In fact the ContactSuccess table mentioned earlier would be connected into the address records and with some advanced programming it could be possible for the database to learn the liklihood of finding a donor at a particular address based on the date and time. But perhaps I am jumping too far ahead now.
However in this case I would definitely add an AddressID PK field to the Address table since the ContactSuccess table would need to relate to it. Probably a good idea to add it now in anticipation of the future.
Usually it is a good idea to reduce the field sizes where possible. In the case of BloodType, field size could be Byte since this is the smallest type (allows for up to 255 values). Although it does not reduce the size of the storage on disk I believe it does reduce the RAM allocated and slightly speeds up the access to records.
Similarly you could reduce the name and address lines from the default 255 to a more appropriate value. This helps keeps users from entering too much into the field for display.
Spaces in field and table names are best avoided so use DonationDate rather than [Date of Donation]. It saves a lot of typing especially those brackets. Indeed avoid space in all onject names including the database filename itself.
BTW, Keep the file name to eight characters maximum for best results on network connections too.
