RDBMS 数据库中的多对多关系

问题描述

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

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

为空或 null 的列

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 数据库中的多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！