Creating MS Access Relationships
How to Correctly Design and Link Tables with Referential Integrity
A database design begins with identifying entities from business data. Tables are designed following conventions, and linked. Only then are tables populated with data.
The Merrie sailing club started back in the late 1950s. The club consists of management, part-time caretakers to manage arrival and departure of guests and a Booking Officer. The business data collected is shown in Figure 1. The club needs a database to store the data and so replace the manual system.
Normalization of Data
The first step to designing a database is to determine what the entities are (people, things involved that are identified by a Noun). It can be seen from the raw data (Figure 1) that the small table contains data about Lodge locations and prices, and the large table contains a mix of data about Customer and the Booking form (notice, the nouns are the entities).
The process of breaking down these large tables, which always contain redundant duplicate values, into smaller tables, is called Normalization.
Creating tables
Next, the designer converts these entities into MS Access 2007 tables by following naming conventions simply by adding a prefix “tbl” in front of the name of each Noun entity. Then the designer decides which table is the main table and which tables are look-up tables.
The tables are now: tblLodge (look-up), tblBooking (main table), and tblCustomer (look-up). Tables can be created by:
- Clicking the Create tab and selecting Table Design (the blue design icon)
- Start typing a unique ID named after the table name, e.g. CustomerID
- Entering all the field names, which correspond to the headings from the raw data given
- Entering all the data types. For example, the unique ID would be AutoNumber and a Primary Key would be set only on this field, a date would become Date/time, a dollar value field would be set to Currency, and field data consisting of characters would be set to Text.
Figure 2 shows the table designs created from the table headings (fields) given in the raw data.
Creating Relationships
At this stage, there is no data in the tables. This is important, as creating new relationships between tables requires them to be empty. Figure 3 shows the relationships created from the tables, according to the following steps:
- Close any open tables
- Click on the Database Tools tab
- Right click and select Show Table…
- Click on each table and the Add button in turn
- Shift the main Booking table in the middle and the lookup tables on each side (click and drag the top of each table to move them)
- Click on CustomerID in the customer lookup table and drag onto CustomerID in the main Booking table
- Check the Enforce Referential integrity checkbox and click Create
- Similarly create a link for LodgeID by clicking and dragging from the lookup table to the main table
Referential Integrity is important as it forces the user to the enter data in correct sequence. For example, customer records in the lookup table tblCustomer should exist before entering customer data in tblBooking. Similarly, Lodge records should exist in the lookup table tblLodge before entering lodge data in tblBooking.
Activity
Open MS Access 2007 and create tables as explained above, then create the table relationships with referential integrity. When completed, enter the raw data given in Figure 1. Ensure you enter data in the lookup tables first, followed by data in the main table.
For more Information read the Microsoft tutorial MS Office 2007 Guide to Table Relationships and other articles by the author: Programming MS Access Applications and Using Mail Merge in MS Office 2007
