The following data model is designed to hold information relating to a child's nursery/playgroup. For this scenario we need to define the following facts:
These facts define the requirements which the Database must meet and should be agreed between the Database User and the Database Designer prior to physical creation.
The draft facts have been defined as:
The Entities required should include:
The Entities are related as follows:
When asking questions of the database we may need to know:
The following data model allows these questions to be answered and allows the information contained above to be stored logically and in a structured manner.
To view the model please go to: Relationships for the Nursery/Playgroup Scenario
Please download the data model relating to Relationships for a Nursery/Playgroup Scenario which is contained in the zipped up Microsoft Word document.
Update:
I received the following e-mail from a reader, regarding this design:
"I really enjoy your site and have learned a lot in my visits here.
I also found the Nursery/Childs Playgroup Scenario to be informative. I can think of one way to avoid storing duplicate data by adding a Postal Code Table which would include the following fields:
This table would be linked to tblParents with a foreign key lngPostalCodeID added.
We could use the PostalCode for the PK field, but I have been told that some PostalCodes include 2 cities."
I agree that this would cut down on duplication of Address details, as we are storing the same address (providing both Parents live at the same address) for each Child they have at the nursery/playgroup.
We created a similar data model example, that uses a table for Address Details and a separate table for Occupant Details in the Data Model example for Personal Contacts, Addresses & Contact Details scenario