Three Levels Database - Foreign Keys

Question

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

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

Thus, the possible values ​​for table C will 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 ... 

Now I'm not sure how to set foreign keys; or if they should be set for primary keys in general. My idea was to have a foreign key on table B. B.a_id -> A.a_id and two foreign keys on C. C.a_id -> A.a_id and ( C.a_id, C.b_id ) -> ( B.a_id, B.b_id ) .

Should I configure foreign keys? Is a foreign key required from C->A ? Or do I generally need foreign keys, given that all of these columns are part of the primary keys?

Thanks.

Answer 1

If you already have a foreign key between table B and table A, to ensure that table B contains only the entries that have a value for a_id that exists in table A, then an additional FK between table C and table A on a_id not required. This requires, of course, that the FK relation between table B and table A be fixed, active, and not disconnected or circumvented in any way.

Creating an FK relationship between table C and table B already ensures that TableC.a_id can only refer to a valid a_id value (since this is guaranteed in table B through the FK relationship between table B and table A).

Answer 2

First of all, foreign keys are necessary to confirm the existence of records in the parent table, while primary keys state the uniqueness of records in the table. Therefore, you need both.

Generally speaking, you want to avoid having composite primary keys. Therefore, 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 do not need a foreign key between table C and table A, since this relationship is implied by foreign keys between table C and table B, as well as table B and table A.

change

What's wrong with using primary key connections?

When connecting table C to table B, one row less is indicated. In addition, the number of columns accumulates during the distribution of foreign keys, so table D will have a composite primary key of four columns. At some point, it starts to seem silly. I once worked on a system that had table J with nine primary key columns and two data columns.

Another thing is that compound keys can also be associated with business keys. Spreading such as foreign keys can be a real pain in the neck. Once we have decided to use surrogate (synthetic) keys for one table — auto-increment, sequence, manual, whatever — consistency suggests that we must use the same mechanism for primary keys in all our tables.

There are some ORM tools that make it difficult to use compound keys. I do not suggest this as a good reason not to use complex keys, because I strongly object to the limitations of the ORM tools that control my data model, I just point it out.

On the other hand, there may be advantages to using composite keys. I worked on one system where we had to make many requests in the format

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

It was not necessary to connect table D with table C to table B to get to table A, it was a definite blessing. This would be even more true for databases implementing a virtual private database, when it should restrict access to all our tables based on the fact that users have access to a subset of the tows in table A.

So this is not a strict rule. People take this on both sides of the argument strongly. Throughout my career, I have strongly supported complex primary keys, but now I usually prefer single-column primary keys, and complex business keys are enforced with unique constraints when necessary.

In short, complex primary keys are not mistaken simply cumbersome. One column, surrogate primary keys are probably the industry standard. However, there are situations where complex primary keys are the right choice.