php - 与 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 表 中。

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