Composite foreign keys, Part 2

Digg This!     StumbleUpon     Del.icio.us

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

Let us redesign the tables of Part 1 using non-composite keys, and let's use surrogate keys, implemented using auto_increment, autonumber, identity, or sequence columns (depending on which database you use). Now all the foreign keys will be non-composite.

DIVISION
DVNO DVNAME
  1  Executive
  2  Marketing
  3  Human Resources
  5  Information Technology


DEPARTMENT
DPNO DVNAME          DVNO
  1  Executive         1 
  2  Executive Staff   1 
  3  Payroll           3
  4  Personnel         3
  5  Management        3 
  6  Development       5 
  7  Processing        5 
  8  Management        5


SECTION
SCNO SCNAME          DPNO
  1  Analysis          6
  3  Programming       6
  4  Data Entry        7 
  5  Operations        7 
  7  Section Managers  8
  9  Project Leaders   8


EMPLOYEE
EMPNO EMPNAME        SCNO
 024  J.Programmer     3 
 027  T.Keypunch       4 
 034  P.H.Boss         7 

A major advantage of the non-composite key structure is that it saves space and is generally more efficient. For very large tables, the space savings when avoiding composite keys can be substantial. Also, database queries are executed faster when searching numeric indexes, which most surrogate keys use, than when searching character indexes, which most natural keys would require. Finally, indexes involving multiple columns, required for composite keys, are less efficient than single column indexes.

It is important to emphasize that in neither structure, composite or non-composite, is it necessary to reveal the actual values of the keys.

A disadvantage of non-composite keys, however, is that you lose transitivity. Queries which could utilize transitive relationships based on composite keys are no longer simple. To find all the employees in the Information Technology division, you need --

select EMPNO, EMPNAME
  from DIVISION
inner 
  join DEPARTMENT
    on DIVISION.DVNO = DEPARTMENT.DVNO
inner 
  join SECTION
    on DEPARTMENT.DPNO = SECTION.DPNO
inner 
  join EMPLOYEE
    on SECTION.SCNO = EMPLOYEE.SCNO
 where DIVISION.DVNO = 5

So what you gain in space and simplicity of relationships using non-composite keys, you lose in the complexity of even the simplest queries. Compare the above to the same query in Part 1. Note that the multi-join queries will run efficiently -- it's just a bigger pain to write them.