modeling a 3-way relationshsip

Question

A has many B, but each B has only one A. C has many B, but each B has only one C.

A has many C, but each C has only one A.

note that from the first 2 assertions, A and C have a many-to-many relationship. However, from line 3, it is clear that A and C has actually a one-to-many relationship. I need to model a relational database such that each B that C has actually also belongs to the same A that C belongs to.

How can I achieve this?

Answer 1

Here is one way:

• A has an arbitrary candidate key, A_id
• C has a candidate key (A_id, C_id) where C_id need be unique only wrt A_id
• C formalizes C <<--> A with a foreign key A_id
• B has a candidate key (A_id, C_id, B_id) where B_id need be unique only wrt (A_id, C_id)
• B formalizes B <<--> A with a foreign key A_id
• B formalizes B <<--> C with a foreign key (A_id, C_id)