Отношение многие ко многим с дополнительным полем и внешним ключом 4-й таблицы с Mysql и Propel
Я разрабатываю базу данных, которая будет отслеживать пользователей и их отношения с различными организациями. Пользователь может принадлежать многим организациям, а организация может иметь много пользователей. Эту часть легко решить с помощью отношения «многие ко многим». Тем не менее, когда дела обстоят немного более размыто, пользователь также может быть администратором одной или нескольких организаций, и пользователь должен иметь возможность регистрировать время, проведенное с каждой организацией.
Кажется, есть много способов решить это. Вот структура таблицы, которая у меня есть, я хотел бы узнать ваше мнение, если вы считаете, что есть лучший способ.
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`)
);
Я решил пойти с id поле на user_organization таблица, потому что это сделало создание внешнего ключа к time_log стол проще. Тем не менее, я мог бы просто поставить user_id, а также organization_id в time_log table также.
Решение
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
Другие решения
Не стоит помечать администратора в таблице пересечений. Что произойдет, если ни один из пользователей конкретной организации не будет помечен или если для одной организации будет помечено более одного человека? Один хороший способ — это иметь отдельную таблицу 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) будет первичным ключом, а также внешним ключом. Если последнее, то это таблица «событий», которая, как правило, не имеет первичного ключа — для каждой ссылки может происходить несколько записей, которые различаются по дате и времени события.