zigil zigil - 21 days ago 6
SQL Question

Non-identifying relationship cardinality

Can someone please help me to find out:

Why it is meaningless for Non-identifying relationships to have n:m cardinality?

Answer

n:m cardinality indicates a many-to-many relationship, which is implemented by creating a third table with references to each of the tables in the many-to-many relationship.

For example, a many-to-many relationship in an e-commerce system might be between products and orders. A given product can be part of many orders, and a given order can include many products.

Products >----< Orders

The way to implement this is with a "middle" table, where each row references exactly one product and one order.

Products ----< LineItems >---- Orders

There's an identifying relationship between LineItems and Products, and another identifying relationship between LineItems and Orders. In each case, the foreign key in LineItems is part of its primary key, therefore it's an identifying relationship.

But in this data model, there's no foreign key reference directly between Products and Orders, in either direction. They rely on the LineItems table to hold those references.

Therefore there's no identifying or non-identifying relationship between Products and Orders in an n:m cardinality. Neither of them contain a foreign key to the other.