多对多关系与额外的字段和第4表外键与Mysql和Propel

Question

我正在设计一个数据库，它将跟踪用户及其与不同组织的关系。一个用户可以属于多个组织，一个组织可以有多个用户。这部分很容易用多对多关系来解决。然而，事情变得有点模糊的地方是，用户也可以是一个或多个组织的管理员，并且用户需要能够记录在每个组织中花费的时间。

似乎有很多方法可以解决这个问题。这是我目前得到的表格结构，如果你认为有更好的方法，我想听听你的意见。

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

我选择在user_organization表上使用id字段，因为它使创建time_log表的外键更容易。但是，我也可以把user_id和organization_id放在time_log table中。

Answer 1

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

Answer 2

在交集表中标记管理员不是一个好主意。如果一个特定组织的用户没有被标记，或者同一组织有多个用户被标记，会发生什么情况?一个好的方法是有一个单独的OrgAdmins表。

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

将OrgID作为关键字段限制了每个组织的一个条目。使UserID、OrgID引用交集表确保管理员被正确地定义为组织的用户。

类似的布局可以用于时间日志表。但是，这个时间是每个用户在组织中的总时间，还是用户在组织中"花费时间"的每个时间段都有一个条目?如果是前者，那么(UserID, OrgID)对将是主键和外键。如果是后者，则这是一个通常没有主键的"事件"表——每个引用可能出现多个条目，并根据事件的日期和时间进行区分。