問題描述
如何設計n多對多關係以使sql查詢更容易 (How to design n many to many relationship in order make sql query easily)
我在 3 個表之間有 3 個多對多關係,遵循此規則:
表 A 和表 B 之間存在多對多關係。
li>表A和表C之間存在多對多關係。
表B和表C之間存在多對多關係。
我會在它們之間使用關聯表,但我會尋找最好的方法來處理它。在我看來,我認為創建映射表,其中將包含 idA、idB、idC 作為外鍵,並且每個表與映射表之間具有一對多的關係。
參考解法
方法 1:
All relationships are represented in tables as associations between columns/values representing entity identifiers. Relationships are not between tables ‑ that's a confusion between conceptual modelling (where relationships exist between entities) and physical modelling (where tables can be joined based matching values).
You propose a single ternary relationship table instead of properly implementing 3 separate binary relationship tables. I assume all the columns will be nullable? That's going to prevent you from implementing a primary key, and depending on your DBMS, unique constraints may be problematic too. Not to mention figuring out what it means when all 3 fields in a row are populated.
Do it the right way, and create 3 separate relationship tables in which you at least have a unique constraint on the two entity columns.
方法 2:
Usually when we have many‑to‑many relationship between two tables, a new table is created for the "many‑to‑many" relationship, where primary keys of both tables are added as foreign keys, you can study it in detail, i'm in the entry level, on the basis of my knowledge i answered.
(by Mamadou Berthé、reaanb、Zia Ullah)