How to link one to many relationship with multiple keys pointing on the same table id?

Question

I have a table files and a table users and staff and client. The file table contain a row made_by a row published_byand a row about_client pointing to a staff, a staff and a client respectively.

One user must be either a staff or a client. One file can have multiple author (made_by row), but only one user can publish it (published_by), and it can only be about one client (about_client).

First, I put 3 foreign key on files:

    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

but after a little research, I found out that I should put the foreign key on staff / client, and here come the problem, the fk are OPTIONAL. A staff is not obligatory an author of a file, or the client maybe don't have any file attached to it.

I am a bit lost.

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;

Barmar · Accepted Answer · 2019-07-17T23:46:12.427

1

If there can be multiple made_by, it can't be a column in the file table, since that can only contain one value.

You need another table, file_made_by that represents the many-to-many relationship between files and authors.

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

The clients and staff tables should also include a user_id column that's a foreign key to users.id.

If about_client has to be about a client, not staff, then it should be a foreign key to clients.id rather than users.id.

And if only staff are allowed to be authors, file_made_by.author_id should be a FK to staff.id rather than users.id. The same with files.published_by.

edited Jul 17 '19 at 23:46

answered Jul 17 '19 at 23:34

Barmar

741,623
53
500
612

I did not know you could have two primary key ? I ended up with this conclusion, but I had my table `file_made_by`with id int not null auto_increment primary key, file -- file_fk pointing to to file id and user -- user_fk pointing to user id. – Yolan Maldonado Jul 18 '19 at 00:06
You can't have two primary keys. You can have a multi-column primary key. – Barmar Jul 18 '19 at 00:07
If you want a separate `id` column, you can do `id INT NOT NULL AUTO_INCREMENT PRIMARY KEY` and later `UNIQUE KEY (file_id, author_id)`. That makes them unique, but not primary. – Barmar Jul 18 '19 at 00:08
For those that want to learn about primary key in column: https://stackoverflow.com/questions/2626158/why-use-multiple-columns-as-primary-keys-composite-primary-key – Yolan Maldonado Jul 18 '19 at 00:24

How to link one to many relationship with multiple keys pointing on the same table id?

1 Answers1