%dim crumb crumb="Link Database Tables" crumb1="Relational Table Design Table Linking" crumb2="Links Between Access Tables" %>
|
Linking Database Tables with Unique Numeric FieldsDefinition Examples and RecommendationsLinking 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:
Table Design Example - Master Table Listed below is a simple example of an Employee table: Employee_ID (indexed unique, or indexed no duplicates) (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) 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. |
|
Contact Information <% Response.write "Copyright 2000-" & year(now) & " Blue Claw Database Design" %>
Microsoft Access 2007, Access 2010, MS Access 2013 |