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.
