databasedev.co.uk - database solutions and downloads for microsoft access

Microsoft Access Data Model Example

Nursery/Childs Playgroup Scenario

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:

  • Children
  • Parents
  • Nursery Sessions
  • Nursery Register

The Entities are related as follows:

  • A Parent can have many Children
  • A Child can have multiple Parent details recorded
  • The Nursery holds many different Sessions (a session could be a Monday morning 8:00am - 12:00pm)
  • A Child could be Registered against many Sessions (the child may go to nursery only three mornings a week)

When asking questions of the database we may need to know:

  1. Can we locate the preferred contact details of a particular childs parents
  2. How many children are registered for each session this week
  3. How much does each Parent owe in nursery fees for their children

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:

  • lngPostalCodeID (Autonumber Primary Key)
  • strPostalCode (Text)
  • strCity (Text)
  • strRegionState (Text)
  • strCountry (Text) - In case one of the parents lives in another country.

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