RDBMS数据库中的多对多关系 [英] Many-to-many relations in RDBMS databases

问题描述

已经尝试使用数据透视表来跟踪关系数据库中的多对多关系的最佳方法，但它会导致以下任一方法：

• 规范化被遗忘




• 空或空的列

为了支持你采取了什么方法多对多关系？

解决方案

在表格中跟踪多对多关系，专门为该关系（有时称为连接表）。该表将关系建模为两个一对多关系指向相反的方向。

  CREATE TABLE customer（
 customer_id VARCHAR NOT NULL，
 name VARCHAR NOT NULL，
 PRIMARY KEY（customer_id））; 
 
 CREATE TABLE发布（
 issn VARCHAR NOT NULL，
名称VARCHAR NOT NULL，
 PRIMARY KEY（issn））; 
 
  - 订阅的多对多关系。 
 CREATE TABLE subscription（
 customer_id VARCHAR NOT NULL，
 FOREIGN KEY customer_id REFERENCES customer（customer_id），
 issn VARCHAR NOT NULL，
 FOREIGN KEY issn REFERENCES publication（issn ），
开始TIMESTAMP NOT NULL，
 PRIMARY KEY（customer_id，issn））; 
  

然后，您可以使用连接表通过它连接其他表

   - 哪些客户订阅名为您的花园Gnome的出版物？ 
 SELECT客户* 
从客户
 JOIN订阅
 ON subscription.customer_id = customer.customer_id 
 JOIN发布
 ON subscription.issn = publication.issn 
 WHERE 
 publication.name ='你的花园Gnome'; 
 
  - 客户指定Fred Nurk订阅哪些出版物？ 
 SELECT publication。* 
 FROM publication 
 JOIN subscription 
 ON subscription.issn = publication.issn 
 JOIN customer 
 ON subscription.customer_id = customer.customer_id 
 WHERE 
 customer.name ='Fred Nurk'; 
  

What is the best way of handling many-to-many relations in a RDBMS database like mySQL?

Have tried using a pivot table to keep track of the relationships, but it leads to either one of the following:

• Normalization gets left behind

• Columns that is empty or null

What approach have you taken in order to support many-to-many relationships?

解决方案

Keep track of a many-to-many relationship in a table specifically for that relationship (sometimes called a junction table). This table models the relationship as two one-to-many relationships pointing in opposite directions.

CREATE TABLE customer (
    customer_id VARCHAR NOT NULL,
    name VARCHAR NOT NULL,
    PRIMARY KEY (customer_id));

CREATE TABLE publication (
    issn VARCHAR NOT NULL,
    name VARCHAR NOT NULL,
    PRIMARY KEY (issn));

-- Many-to-many relationship for subscriptions.
CREATE TABLE subscription (
    customer_id VARCHAR NOT NULL,
        FOREIGN KEY customer_id REFERENCES customer (customer_id),
    issn VARCHAR NOT NULL,
        FOREIGN KEY issn REFERENCES publication (issn),
    begin TIMESTAMP NOT NULL,
    PRIMARY KEY (customer_id, issn));

You then use the junction table to join other tables through it via the foreign keys.

-- Which customers subscribe to publications named 'Your Garden Gnome'?
SELECT customer.*
FROM customer
    JOIN subscription
        ON subscription.customer_id = customer.customer_id
    JOIN publication
        ON subscription.issn = publication.issn
WHERE
    publication.name = 'Your Garden Gnome';

-- Which publications do customers named 'Fred Nurk' subscribe to?
SELECT publication.*
FROM publication
    JOIN subscription
        ON subscription.issn = publication.issn
    JOIN customer
        ON subscription.customer_id = customer.customer_id
WHERE
    customer.name = 'Fred Nurk';

这篇关于RDBMS数据库中的多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！