I've got a three tables, A, B and C. A has numerous B, and B has numerous C. What's the easiest way design primary secrets and relationship of these two. Sorry to request a creating problem, but I'm not sure whether or not 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 individually OR
  3. use foreign key associations without needing any other tables (if that's the case please let me know the way the key posts should come)

Thanks greatly.

Option (3) seems like probably the most natural:

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

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

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

To question:

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

Always build your database model the logical associations inside your data, not the opposite way round!

I'd use three. You shouldn't have for further tables inside a 1:many relationship. The numerous-side holds an overseas key pointing towards the one-side.

Option 3 is what you want.

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