CODEWITHSUNDEEP
database-design
prjndhi
prjndhi

Reputation: 1965

Why can't many to many relationships be shown in the data dictionary?

I have read and seen that many-to-many relationships in an ER diagram can be shown by adding another table which includes the primary key of both tables as reference keys. Here payment_party_id is the primary key of the payment_party table and receipt_party_id is the primary key of the receipt_party table. What is the logical reason behind that:

  field                  key
  ---------------------  -----------------------       
  payment_party_id       reference key
  receipt_party_id       reference key

Can anyone explain why this additional table is added only with reference keys?

Upvotes: 0

Views: 577

Answers (1)

Joel Brown
Joel Brown

Reputation: 14408

Many-to-many relationships can be shown directly on logical entity-relationship drawings. Some modelling tools don't depict logical ERDs. They only depict physical ERDs. In a physical ERD a many-to-many relationship is implemented using an intersection table.

Any intersection table requires a foreign key (OP: "reference key") to each table that is part of the relationship. If the intersection table implements a simple many-to-many relationship, then the combination of the two foreign keys also form the compound primary key of the intersection table.

In other words, the example from the question would in fact look like this:

field                  key
---------------------  -------------------------------------
payment_party_id       primary key, foreign (reference) key
receipt_party_id       primary key, foreign (reference) key

Upvotes: 2

Related Questions