CODEWITHSUNDEEP
mysqlsqldatabase
Yolan Maldonado
Yolan Maldonado

Reputation: 23

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

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;

Upvotes: 0

Views: 221

Answers (1)

Barmar
Barmar

Reputation: 781058

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.

Upvotes: 1

Related Questions