Q
Problem solve Get help with specific problems with your technologies, process and projects.

Composite foreign keys, Part 2

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.


This was last published in September 2002

Dig Deeper on Oracle database design and architecture

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close