In Part 1 of this article, we examined tables that were related using composite foreign keys. Now, let's consider an alternative.
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.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.