sql - RDBMS数据库中的多对多关系

Question

在像mySQL这样的RDBMS数据库中处理多对多关系的最佳方法是什么？

尝试使用数据透视表来跟踪关系，但这会导致以下任一情况:

规范化落后于

空或为空的列

您采取了什么方法来支持多对多关系？

Answer 1

在表中专门针对该关系(有时称为联结表)跟踪多对多关系。该表将该关系建模为指向相反方向的两个一对多关系。

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));

然后，您可以使用联结表通过外键通过 加入其他表。

-- 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';