CODEWITHSUNDEEP
mysqldatabasedatabase-designrelational-databaseentity-relationship
Zerone
Zerone

Reputation: 566

handling three tables with two one-to-many relationship among them

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.

Upvotes: 1

Views: 2151

Answers (3)

Raihan
Raihan

Reputation: 10405

Option 3 is the way to go.

A {a_pk, ...}
B {b_pk, ..., a_pk_as_fk}
C {c_pk, ..., b_pk_as_fk}

Upvotes: 2

okrumnow
okrumnow

Reputation: 2416

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.

Upvotes: 2

Kerrek SB
Kerrek SB

Reputation: 477040

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!

Upvotes: 3

Related Questions