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.