0

Are there any general design considerations (good/bad/neutral) for using a foreign key of one table as the primary key in another table?

For example, assume the following tables as part of a film catalogue:

titles
------
 id


episodes
--------
 title_id (PK/FK)

Episodes could obviously be done with both an id and a title_id, where id would be the PK and title_id would be UNIQUE, but since title_id is already unique, and, technically, identifies the episode, would there be anything to consider in just using it as the PK? What about in general? What design considerations can you see to this?

Thanks for your thoughts!

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
Mr Mikkél
  • 2,577
  • 4
  • 34
  • 52
  • Should it be possible to have multiple episodes with the same title (e.g. title acts as a series title)? Can there be a title without at least one episode? – Branko Dimitrijevic Oct 08 '14 at 23:45
  • There cannot be multiple episodes with the same title. Yes, there can be a title without an episode. There are titles of different types. Titles of type episode will have an episode record. – Mr Mikkél Oct 09 '14 at 20:06
  • Then this looks appropriate. You are in fact implementing a form of inheritance (see the "Physical Representation" section of [this post](http://stackoverflow.com/a/12037398/533120)). Adding a surrogate key would not be beneficial in this case. – Branko Dimitrijevic Oct 10 '14 at 11:44

1 Answers1

0

The answer to your question is basically the description of the technique known as "shared primary key". Accordingly, I've replaced the two tags about primary-key and foreign-key with the single tag shared-primary-key.

Shared primary key is a design where the PK of one table is also an FK that references the PK of another table. As the tag wiki for shared-primary-key indicates, this is useful for one-to-one relationships, whether they are mandatory or optional. These relationships are sometimes called IS-A relationships, as in "an automobile is a vehicle". The relationship between vehicles and autos is also known as a class/subclass or type/subtype relationship.

Like any design technique, it has its benefits and its costs.

Edit in reply to comment:

The biggest benefit to shared primary key is that it enforces the 1-to-1 nature of the relationship. Having this rule enforced in the database is generally more productive than trying to make sure that all the application code follows the rule.

A secondary benefit is that is makes the join between the two tables simple and fast. It's fast (for some database systems) because of the indexes built to support PKs are used by the optimizer to speed up the join.

A third benefit is that a third table can reference both of these two tables with the same FK.

The cost is that there is some programming involved in adding a new entry to both tables. The PK from the primary table has to be copied into the secondary table, and the system typically won't do this for you. Also, the join, while fast, is not free.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
  • Well, sure... that's what I'm asking. What kinds of benefits and costs? (Thank you for pointing me to the shard-primary-key tag.) – Mr Mikkél Oct 09 '14 at 21:11