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

Microsoft Access Data Model Example

Personal Contacts, Addresses & Contact Details scenario

The following data model is designed to hold information relating to Address Details and Personal Contact Information and Contact Numbers. 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:

  • Addresses
  • Occupants
  • Contact Types

The Entities are related as follows:

  • One Address can have zero, one or many Occupants
  • One Occupant can have zero, one or many Contact Types
  • One Contact Type can have zero, one or many Occupants.

The design allows an Occupant to have multiple Contact Types (home phone, work phone, mobile phone, fax number or e-mail addresses) assigned to them. The Occupant can also have the same type of Contact Type associated with them multiple times, due to the composite Primary Key applied in the LINK table - tblOccupantContactDetails.

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

  1. How many Occupants live at a certain Address?
  2. What is the main Contact Number or E-mail address of a certain Contact (Occupant)?
  3. Can we contact an Occupant using an alternative contact detail?

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 Personal Contacts and Addresses

Please download the data model relating to Relationships for Personal Contacts and Addresses which is contained in the zipped up Microsoft Word document.