Many to Many Relationship with extra field and 4th table foreign key with Mysql and Propel

Question

I am designing a database that will keep track of users and their relationship with different organizations. A user can belong to many organizations, and an organization can have many users. That part is simple to solve with a Many to Many relationship. However, where things get a little more fuzzy is that a user can also be an admin to one or more of the organizations, and a user needs to be able to log time spend with each organization.

It seems that there are many ways to solve this. Here is the table structure I have so far, I would like your opinion if you think there is a better way.

CREATE TABLE `organization` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    PRIMARY KEY (`id`)
);

CREATE TABLE `user` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `first_name` VARCHAR(50) NOT NULL COLLATE 'utf8_unicode_ci',
    `last_name` VARCHAR(50) NOT NULL COLLATE 'utf8_unicode_ci',
    `email` VARCHAR(50) NOT NULL COLLATE 'utf8_unicode_ci',
    `password` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `email` (`email`)
);

CREATE TABLE `time_log` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `user_organization_id` INT(11) NOT NULL,
    `date` DATE NOT NULL,
    `time` TINYINT(4) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `user_organization_id` (`user_organization_id`),
    CONSTRAINT `fk_time_log_user_organization` FOREIGN KEY (`user_organization_id`) REFERENCES `user_organization` (`id`)
);

CREATE TABLE `user_organization` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) NOT NULL,
    `organization_id` INT(11) NOT NULL,
    `admin` TINYINT(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`, `user_id`, `organization_id`, `admin`) USING BTREE,
    INDEX `user_id` (`user_id`),
    INDEX `organization_id` (`organization_id`),
    CONSTRAINT `fk_user_organization_organization` FOREIGN KEY (`organization_id`) REFERENCES `organization` (`id`),
    CONSTRAINT `fk_user_organization_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
);

I chose to go with an id field on the user_organization table because it made creating a foreign key to the time_log table easier. However, I could also just put the user_id, and organization_id in the time_log table as well.

score 0 · Answer 1 · answered Sep 06 '15 at 19:14

CREATE TABLE `user_organization` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,  -- remove
    `user_id` INT(11) NOT NULL,            -- don't you want INT UNSIGNED?
    `organization_id` INT(11) NOT NULL,
    `admin` TINYINT(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`, `user_id`, `organization_id`, `admin`) USING BTREE,  -- Bad!
    INDEX `user_id` (`user_id`),  -- see below
    INDEX `organization_id` (`organization_id`),
    CONSTRAINT `fk_user_organization_organization` FOREIGN KEY (`organization_id`) REFERENCES `organization` (`id`),
    CONSTRAINT `fk_user_organization_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
);

-->

CREATE TABLE `user_organization` (
    `user_id` INT(11) NOT NULL,
    `organization_id` INT(11) NOT NULL,
    `admin` TINYINT(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (`user_id`, `organization_id`)     -- PK, and lookup from user
    INDEX `organization_id` (`organization_id`, user_id),  -- lookup the other way
    CONSTRAINT `fk_user_organization_organization` FOREIGN KEY (`organization_id`) REFERENCES `organization` (`id`),
    CONSTRAINT `fk_user_organization_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB;        -- don't let it default to MyISAM

Thanks for the answer. If I drop the id on user_organization, what do I do with the foreign key on time_log? Is it better to add a user_id, and organization_id column in that table instead? — ryanmc, Sep 06 '15 at 23:18
That comes under the heading of "over-normalization". Simply have the user and org ids in the log. Also, do not split DATE and TIME unless you have a good reason. Use DATETIME. — Rick James, Sep 06 '15 at 23:33

score 0 · Answer 2 · answered Sep 08 '15 at 17:22

It's not a good idea to flag the admin in the intersection table. What happens if none of the users of a particular organization are flagged or if more than one is flagged for the same organization? One good way is to have a separate OrgAdmins table.

create table OrgAdmins(
    UserID   int  not null,
    OrgID    int  not null,
    Assigned date not null,
    constraint PK_OrgAdmins primary key( OrgID ),
    constraint FK_OrgAdmins_OrgUser foreign key( UserID, OrgID )
        references user_organization( user_id, organization_id )
);

Making OrgID the key field limits one entry for each organization. Making the UserID, OrgID reference the intersection table assures that the admin is properly defined as a user of the organization.

A similar layout can work for the time log table. But is that time the total time per user for an organization or is there an entry for each time period the user "spends time" at the organization? If the former, then (UserID, OrgID) pair would be the primary key as well as a foreign key. If the latter, this is an "event" table which generally does not have a primary key -- multiple entries could occur for each reference and are differentiated by the date and time of the event.

Thanks for the comment. I do want to make sure at least one admin is on each org, but multiple is also ok. As for the time_log table, this is just a log of time that a user has donated to that org. — ryanmc, Sep 08 '15 at 20:52
To have zero, one or more admins per organization, just add UserID to the PK of OrgAdmins table. So (UserID, OrgID) will be both PK and FK. — TommCatt, Sep 10 '15 at 03:34

Many to Many Relationship with extra field and 4th table foreign key with Mysql and Propel

2 Answers2