Three level database - foreign keys
- by poke
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.
