|
|
|
Creating MS Access RelationshipsHow 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 DataThe 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 tablesNext, 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:
Figure 2 shows the table designs created from the table headings (fields) given in the raw data. Creating RelationshipsAt 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:
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. ActivityOpen 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
The copyright of the article Creating MS Access Relationships in Office/Business Software is owned by Harry P. Schlanger. Permission to republish Creating MS Access Relationships in print or online must be granted by the author in writing.
|
|
|
|
|
|
|
|