CODEWITHSUNDEEP
databaseumlmodeling
tomsullivan1989
tomsullivan1989

Reputation: 2780

modelling two one-to-many relationships database relationships in uml

I have a database design with 3 tables, call them A, B and C, with the following one-to-many relationships:

A-<B>-C

Both A and B have an auto-incremented IDs as primary keys.

My problem is that if I have a record in table B which has a field containing an ID=12, for example, there is no way of knowing which of table A or C's primary keys this corresponds to. Both tables will have a primary key ID=12. I think this is an example of a fan trap, but any research I have done all relates to relationship such as:

A-<B-<C

and the solution is to restructure the tables. I'm not sure that this will work here. Do I need a table that sits between table B and tables A and C?

Upvotes: 0

Views: 174

Answers (2)

Gilbert Le Blanc
Gilbert Le Blanc

Reputation: 51445

My problem is that if I have a record in table B which has a field containing an ID=12, for example, there is no way of knowing which of table A or C's primary keys this corresponds to.

You have to add a column to B that lets you know whether the foreign key points to A or C.

Your other alternative is to have two nullable foreign keys in B, one pointing to A, and the other pointing to C.

I suspect that your tables could be normalized a bit more, so you have a structure like this:

                D
   --------------------------
   |            |           |
   A            B           C
  ---          ---         ---

Upvotes: 0

Shamwow
Shamwow

Reputation: 51

You have to add a foreign key to one of the tables to have that relationship. In your example you would add the PK of table B as an FK in tables A, C

Upvotes: 1

Related Questions