Three level database - foreign keys

Question

I have a three level database with the following structure (simplified to only show the primary keys):

Table A: a_id
Table B: a_id, b_id
Table C: a_id, b_id, c_id

So possible values for table C would be something like this:

a_id  b_id  c_id
   1     1     1
   1     1     2
   1     1     3
   1     2     1
   1     2     2
   2     1     1
   2     2     1
   2     2     2
...

I am now unsure, how foreign keys should be set; or if they should be set for the primary keys at all. My idea was to have a foreign key on table B B.a_id -> A.a_id, and two foreign key on C C.a_id -> A.a_id and ( C.a_id, C.b_id ) -> ( B.a_id, B.b_id ).

Is that the way I should set up the foreign keys? Is the foreign key from C->A necessary? Or do I even need foreign keys at all given that all those columns are part of the primary keys?

Thanks.

Answer 1

If you already have a foreign key between table B and table A to make sure table B only contains entries that have a value for a_id that exists in table A, then the extra FK between table C and table A on a_id is unnecessary. This requires, of course, that the FK relation between Table B and Table A is encorced, active, and not disabled or circumvented in any way.

Making the FK link between table C and table B already guarantees that TableC.a_id can only refer to a valid value of a_id (since that's guaranteed in table B through the FK relation between Table B and Table A).

Answer 2

First of all, foreign keys are necessary to assert the existence of records in a parent table whereas primary keys assert the uniqueness of records with a table. So you need both.

Generally speaking you want to avoid have compound primary keys. So your tables should look like this:

Table A: a_id (pk)
Table B: b_id (pk), a_id (fk)
Table C: c_id (pk), b_id (fk)

You don't need a foreign key between Table C and Table A, because that relationship is implied by the foreign keys between Table C and Table B, and Table B and Table A.

edit

What is bad about using compound primary keys?

It is one less line to type when joining Table C to Table B. Also the number of columns accumulates as we propagate foreign keys, so Table D would have a compound primary key of four columns. At some point this starts to feel silly. I once worked on a system which had a Table J with nine primary key columns and two data columns.

The other thing is, compound keys can also be associated with business keys. Propagating those as foreign keys can be a real pain in the neck. Once we have made the decision to use surrogate (synthetic) keys for one table - autoincrement, sequence, guid, whatever - consistency suggests that we should use the same mechanism for primary keys on all our tables.

There are some ORM tools which make it hard to use compound keys. I don't offer that as a good reason for not using compound keys, because I object strongly to the limitations of ORM tools driving my data model, I just point it out.

On the other hand there can be advantages to using compound keys. I worked on one system where we had to do a lot of queries of the format

select D.* 
from  D 
    join A on ( D.a_id = A.id )
where A.some_col = 'whatever'

Not having to join Table D to Table C to Table B to get to Table A was a definite boon. This would be even truer of databases implementing Virtual Private Database, when he have to restrict access to all our tables on the basis that users have access to a subset of tows in Table A.

So it is not a hard and fast rule. People feel strongly about this, on both sides of the argument. Over the course of my career I have vehemently supported compound primary keys but I now find myself usually coming down in favour of single column primary keys, with compound business keys enforced with unique constraints when appropriate.

In short, compound primary keys are not wrong just unwieldy. Single column, surrogate primary keys are probably the industry standard. However, there are situations where compound primary keys are the right choice.