mysql - 如何将一对多关系与指向同一个表id的多个键链接起来？

Question

我有一个表files、一个表users、staff和client。文件表包含一行made_by、一行published_by和一行about_client，分别指向员工、员工和客户。

一个用户必须是员工或客户。一个文件可以有多个作者(made_by 行)，但只有一个用户可以发布它(published_by)，并且只能是关于一个客户端(about_client)。

首先，我在文件上放置了 3 个外键:

    CONSTRAINT `made_by_fk`    FOREIGN KEY (`made_by`)    REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `published_by_fk`    FOREIGN KEY (`published_by`)    REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `about_client_fk`    FOREIGN KEY (`about_client`)    REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

但是经过一番研究，我发现我应该将外键放在员工/客户上，问题来了，fk是可选的。工作人员不一定是文件的作者，或者客户可能没有附加任何文件。

我有点迷失了。

DROP TABLE IF EXISTS `files`;
CREATE TABLE IF NOT EXISTS `files` (
    `id`                INT             NOT NULL AUTO_INCREMENT
                                        PRIMARY KEY,
    `made_by`           INT             NOT NULL,
    `published_by`      INT             NOT NULL,
    `about_client`      INT             NOT NULL,
    `creation_date`     DATETIME        NOT NULL,
    `modification_date` DATETIME        NULL 
                                        ON UPDATE CURRENT_TIMESTAMP,
    `path`              VARCHAR(255)    NOT NULL,
    `title`             VARCHAR(100)    NOT NULL UNIQUE,
    `category`          INT             NOT NULL,
    `type`              VARCHAR(30)     NOT NULL,
    `size`              INT             NOT NULL,

    CONSTRAINT `made_by_fk`    FOREIGN KEY (`made_by`)    REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `published_by_fk`    FOREIGN KEY (`published_by`)    REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `about_client_fk`    FOREIGN KEY (`about_client`)    REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)
    ENGINE   = InnoDB
    COLLATE  = utf8_unicode_ci;

DROP TABLE IF EXISTS `staff`;
CREATE TABLE IF NOT EXISTS `staff` (
    `id`                INT             NOT NULL AUTO_INCREMENT
                                        PRIMARY KEY,
    `job`               INT             NOT NULL,
    `password`          VARCHAR(255)    NOT NULL
)
    ENGINE   = InnoDB
    COLLATE  = utf8_unicode_ci;

DROP TABLE IF EXISTS `users`;
CREATE TABLE IF NOT EXISTS `users` (
    `id`        INT             NOT NULL AUTO_INCREMENT
                                PRIMARY KEY,
    `name`      VARCHAR(100)    NOT NULL,
    `surname`   VARCHAR(100)    NOT NULL,
    `email`     VARCHAR(50)     NOT NULL UNIQUE
)
ENGINE   =  InnoDB
COLLATE  =  utf8_unicode_ci;

DROP TABLE IF EXISTS `clients`;
CREATE TABLE IF NOT EXISTS `clients` (
    `id`                INT         NOT NULL AUTO_INCREMENT
                        PRIMARY KEY,
    `phone`             VARCHAR(30) NOT NULL,
    `age`               INT         NOT NULL,
    `date_of_birth`     DATE        NOT NULL,
    `security_number`   VARCHAR(99) NOT NULL UNIQUE
)
    ENGINE   =  InnoDB
    COLLATE  =  utf8_unicode_ci;

Answer 1

如果可以有多个 made_by，它不能是 file 表中的一列，因为它只能包含一个值。

您需要另一个表 file_made_by 来表示文件和作者之间的多对多关系。

CREATE TABLE file_made_by (
    file_id INT NOT NULL,
    author_id INT NOT NULL,
    PRIMARY KEY (file_id, author_id),
    FOREIGN KEY (file_id) REFERENCES files (id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (author_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
);

clients 和 staff 表还应包含 user_id 列，该列是 users.id 的外键.

如果 about_client 必须与客户相关，而不是员工，那么它应该是 clients.id 的外键，而不是 users.id.

如果只允许员工成为作者，则 file_made_by.author_id 应该是 staff.id 的 FK，而不是 users.id 。与 files.published_by 相同。