0

I have two tables TableA and TableB which have a relation of one to many

enter image description here

Later I am told that there must be an additional table that links the initial tables with additional information. To carry this out using the entity relationship model, I have in mind these two proposals:

enter image description here

enter image description here

Which of the two options would be the most recommended?

For this third table can you define some type of cardinality, with respect to the two tables already related?

1
  • In the future, please provide SHOW CREATE TABLE. You will need such in the long run, and it provides an unambiguous specification of the tables for us to critique.
    – Rick James
    Jul 14, 2020 at 5:28

1 Answer 1

0

If the relationship between TableA and TableB is meant to be one-to-many then you don't need a third table (TableC).

However, if TableA to TableB are meant to have a many-to-many relationship, then you would need a third table (TableC). In this case you would have foreign keys in TableC to both TableA and TableB. Normally these two foreign keys in TableC would have a unique constraint (and probably index) applied to the combination of these two keys (a compound key).

Often this compound key is also used as the primary key of TableC. This doesn't have to be the case. It's possible for TableC to have it's own single column unique key. It depends on the nature of the data you're storing in TableC.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.