1

I have a three tables, A, B and C. A has many B, and B has many C. What is the best way design primary keys and relationship among these two. Sorry to ask a designing problem, but I don't know whether to use

  1. one extra table to map A(pkey) - B(pkey) - C(pkey) OR
  2. two exta tables to map A-B and B-C separately OR
  3. use foreign key relationships without using any extra tables (if so please tell me how the key columns should come)

Thank you very much.

Zerone
  • 566
  • 4
  • 10
  • 24
  • 1
    Is B always dependent on A? Stated another way can a B exist without an A? If B cannot exist without A then maybe the PK for B should also include the FK from A. If the same with C then maybe the PK for B (which includes the PK for A) should also be in the PK for C. – Paul Morgan Dec 26 '11 at 18:45
  • Thanks its a valuable point! Yes B cannot exist without A and the same for B and C. Thank you. – Zerone Jan 12 '12 at 15:33

3 Answers3

3

Option (3) sounds like the most natural:

A: { id, *, *, ... };

B: { id, a_id references(A.id), *, *, ... }

C: { id, b_id references(B.id), *, *, ... }

To query:

SELECT ... FROM A JOIN B ON (A.id = B.a_id) JOIN C ON (B.id = C.b_id);

Always make your database model the logical relationships in your data, not the other way round!

Kerrek SB
  • 464,522
  • 92
  • 875
  • 1,084
2

I'd use three. THere's no need for additional tables in a 1:many relationship. The many-side holds a foreign key pointing to the one-side.

okrumnow
  • 2,346
  • 23
  • 39
2

Option 3 is the way to go.

A {a_pk, ...}
B {b_pk, ..., a_pk_as_fk}
C {c_pk, ..., b_pk_as_fk}
Raihan
  • 10,095
  • 5
  • 27
  • 45