Home  Fees/Services  Access Examples  Tutorials  Access Downloads  Articles  Search  Contact  Privacy  Links
Relational Table Design Table Linking
 






Link Database Tables
0th Normal Form (0nf)
First Normal Form
Second Normal Form
3rd Normal Form
Table Design Summary
Table Design Tutorial
Access Join Tables

Table Level Events

Article:
Repair Corrupted Table


Linking Database Tables with Unique Numeric Fields

Definition Examples and Recommendations

Linking Database Tables

In a relational database you will have multiple tables used to store data. These tables are often setup in a master - detail manner.  For consistency and efficiency you should create a numeric field which will contain a unique number for each record in your master table. The following are examples of the this field in popular database management systems:

  • Microsoft Access - autonumber field

  • SQL/Server - GUID (global unique identifier)

  • Oracle - internally generated sequential integer usually created by a common stored procedure

Table Design Example - Master Table

Listed below is a simple example of an Employee table:

Employee_ID            (indexed unique, or indexed no duplicates)
SSN                        (SSN and Employee_Name together create a primary key)
Employee_Name   
(SSN and Employee_Name together create a primary key)

(note that SSNs are not always unique)

You may look at this design and say you have always seen the Employee_ID set as the table primary key.  No matter what your teacher or books say - this would be incorrect.  See why you almost never use an artificially generated numeric ID as a primary key.

{ added 08/22/09: ok, ok. I have been reading on various posts that people don't like my arrogant statement about primary key description (above)... You are right.  There are two reasons why I made the statement: 1) originally I didn't know how to tell Access to create a unique composite index (more than one field). and 2) the primary key shows up nicely in the relationships design window thereby letting me know what the unique index is on the table.  This method as no bad effect on the functioning of the database although it can make upsizing to SQL/Server more difficult since SQL/Server likes the autonumber field to be the primary key. }

In our example and in most every table you create you will have essentially two primary keys.  The bold data fields which are defined to the database as the primary key, and the unique numeric field which can be considered a pseudo or artificial primary key.

Table Design Example - Detail Table

In the Employee_Hours table below we will store hours worked for each employee for each day they work:

Employee_ID        (Employee_ID and Work_Date together create a primary key)
Work_Date          
(Employee_ID and Work_Date together create a primary key)
Work_Hours

In the table design above the Employee_ID is called a foreign key. A foreign key is an field that uniquely defines records in another table, in our case, the Employee table

The primary key of the Employee_Hours table is the combination for the foreign key (Employee_ID) and the Work_Date.  What this primary key does for us is prevent accidental entry of two sets work hour records for the same employee on the same date.  Prevention of such duplicate records in one of the most important aspects of relational database design.










A Blue Claw Database Design Article:

Repair Corrupted Access Table
 







Blue Claw Database Design Downloadable Tutorial:
DoCmd.OpenForm & OpenArgs VBA Example.  







A Blue Claw Software Design Template:

Barcode-Device Inventory Database
 











Microsoft Office:
MS Access 2003
Access 2007
Access 2010
Access 2013
Access 2016


Contact Information

Copyright 2000-2017 Blue Claw Database Design

Microsoft Access 2007, Access 2010, MS Access 2013