|
|
|
Technology
>>
Software
>>
Creating MS Access Relationships
|
Creating MS Access Relationships
How to Correctly Design and Link Tables with Referential Integrity
Mar 22, 2009 © Harry P. Schlanger
A database design begins with identifying entities from business data. Tables are designed following
conventions, and linked. Only then are tables populated with data.
A
database design
scenario is illustrated here by the Merrie sailing club, which 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 objective of the club is to acquire a database to
store the club's 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.
Figure 1. Raw Data
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. Table Design
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
Figure 3. Table Relationships
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 - Create Access Relationships
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 an article by this author: Programming MS Access Applications (coming).
The copyright of the article Creating MS Access Relationships is owned by Harry P. Schlanger.
Permission to republish in print or online must be granted by the author in writing.
Website construction by Gum Leaf Designs © 2009
|
|
Custom Search
Links:
Website Construction:
Gum Leaf Designs © 2009
|
