Как канонично организовать структуру таблиц в базе данных MySQL?

Question

[Anton Dyshkant]

Добрый день!

Меня интересует вопрос того, как правильно организовать структуру таблиц в базе данных MySQL, если с её помощью надо описать следующую условную ситуацию:

Есть некоторое число работников. Работник может работать, а может не работать. Если работник работает, значит должен быть указан завод, на котором он работает, а также цех, в котором он работает.

Есть некоторое число заводов, однако не все из них подразделяются на цеха.

Вопрос: какие должны быть созданы таблицы и какие должны быть связи между ними.

Самое незамысловатое решение, конечно же, это создать таблицу работников:

CREATE TABLE `worker` (
  `id` INT UNSIGNED NOT NULL ,
  `plant_id` INT NOT NULL,
  `department_id` INT NOT NULL
) ENGINE = InnoDB;

таблицу заводов:

CREATE TABLE `plant` (
  `id` INT UNSIGNED NOT NULL
) ENGINE = InnoDB;

и таблицу цехов:

CREATE TABLE `department` (
  `id` INT UNSIGNED NOT NULL,
  `plant_id` INT NOT NULL,
) ENGINE = InnoDB;

Однако данное решение содержит ряд неприятных моментов:

• неработающий работник будет содержать в себе plant_id = NULL, department_id = NULL (хотя второе прямо вытекает из первого)
  
• работник, работающий на заводе, где нет цехов, будет содержать в себе department_id = NULL (хотя, по большому счету, это свойство целого завода, а не конкретного работника)
  
• работник, работающий на заводе, где есть цеха, буде содержать в себе plant_id = x, department_id = y, а в это же время в таблице department будет id = y, plant_id = x (т.е. будет некоторая избыточность)
  

Могли бы посоветовать каноничный, наиболее нормализованный и эталонный вариант решения данной задачи?

И если у цехов разных заводов будут разные свойства, их также не хотелось бы хранить в одной таблице. Как должны быть при этом организованы таблица(-ы) цехов, и как на них должен ссылаться завод и работник?

Спасибо.

Answer 1

[pi314]

Вот Вам пример сильно нормализованной модели под заявленные хотелки (насколько я их понял).

SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE department
(
	id INT NOT NULL,
	name VARCHAR(50) NOT NULL,
	PRIMARY KEY (id)
) 
;
CREATE TABLE department_property
(
	department_id INT NOT NULL,
	property_id INT NOT NULL,
	UNIQUE KEY UQ_department_property_department_id_property_id(department_id, property_id)
) 
;
CREATE TABLE employment
(
	id INT NOT NULL,
	worker_id INT NOT NULL,
	plant_id INT NOT NULL,
	PRIMARY KEY (id),
	UNIQUE KEY UQ_employment_worker_id_plant_id(worker_id, plant_id)
) 
;
CREATE TABLE employment_department
(
	employment_id INT NOT NULL,
	department_id INT NOT NULL,
	UNIQUE KEY UQ_employment_department_employment_id_department_id(employment_id, department_id)
) 
;
CREATE TABLE plant
(
	id INT NOT NULL,
	name VARCHAR(50) NOT NULL,
	PRIMARY KEY (id)
) 
;
CREATE TABLE property
(
	id INT NOT NULL,
	name VARCHAR(50) NOT NULL,
	value VARCHAR(50) NOT NULL,
	PRIMARY KEY (id)
) 
;
CREATE TABLE worker
(
	id INT NOT NULL,
	name VARCHAR(50) NOT NULL,
	PRIMARY KEY (id)
) 
;
SET FOREIGN_KEY_CHECKS=1;

Но, как уже сказали, нормализация не является самоцелью! С запросами к этой модели у программиста уже возникнет некоторая Камасутра, а будут ли они оптимальны по перформансу - вообще отдельная тема. Так что, не устану повторять: оптимизация структуры БД заключается не в достижении максимально возможной NF, а в том, чтоб на ней оптимально выполнялись именно те запросы, которые на ней должны выполняться ))

Comments

[Anton Dyshkant]

pi314 большое спасибо! Именно это и было предметом моих хотелок! Единственное, что хотелось бы уточнить: в данной схеме каждый department фактически обладает отдельным набором из property. Возможно, мое уточнение частично противоречит основному вопросу, но есть ли способ часть свойств всё-таки сделать общими? Будет ли "правильно" (читай нормализовано), если их просто внести в таблицу department?
И второй вопрос: что нужно изучить, чтобы смочь строить подобные нормализованные схемы? "Любой учебник по SQL", или что-то более специальное?
И даже третий вопрос: вы говорите, что в коде возникает камасутра, и в общем-то понятно, почему. Может быть вы знаете попытки (подходы, паттерны, или даже готовые решения), которые позволяли бы вынести эту камасутру в один "модуль-адаптер" или ORM, чтобы в коде было всё более менее гладко, а этот самый модуль распутывал клубки этих связей? Или практика всё-таки такова, что такие схемы просто не используются?
И совсем крохотный вопросик: в чем вы рисовали эту схему?)

[pi314]

Антон Дышкант: 1.

Будет ли "правильно" (читай нормализовано), если их просто внести в таблицу department?"

Если так сделать, то да - все равно будет "нормализовано", просто, в меньшей степени, чем если бы их вынести в отдельные таблицы. А вот правильно это будет или нет, зависит только от самих свойств. Самая общая рекомендация: если свойства имманентны (т.е. присущи всем и каждому department-у), смело пихайте их в эту таблицу - это не создаст дополнительной избыточности, но позволит избежать в запросах одного бесполезного JOIN; а те, которые специфичны (или могут быть специфичны), пихайте в отдельную. Но, опять же, как в каждом конкретном случае "правильно", зависит не только от моделируемых сущностей, но и от того, что конкретно предполагается делать с этой моделью. Например (пофантазируем): где-то в UI захочется поиметь редактируемый список всех (общих и специфических) свойств одного конкретного department-а в виде key-value, в котором будут редактироваться только значения. Тогда программисту потребуется для каждого свойства еще его признак (общее/специфичное). Для такого случая гораздо удобнее вынести и общие свойства в отдельную таблицу с таким же точно набором полей, как и в "специфичных", т.е. тогда эта общая рекомендация уже будет неверной ))
2. "Любой учебник по SQL", по теории реляционных БД и т.д. - это хорошо, но только начало. Изучение теории позволит, грубо говоря, уверенно отвечать на вопрос, в большей или меньшей степени нормализована та или иная структура, но мало что даст в смысле ответа на вопрос "как правильно" )) По-настоящему понять все закономерности можно только спроектировав большое количество разных систем, совершив при этом все возможные ошибки и прочувствовав на собственной шкуре их последствия. (Это, разумеется, мое личное IMHO... я повидал кучу теоретиков, проектировавших офигительно нормализованные БД, приводившие в результате к полной заднице в проектах. Возможно, мне просто не везло в жизни и где-то есть правильные книжки, которые им следовало бы изучить вместо тех, которые они изучали...)
3.а На практике есть, в принципе, два подхода к проектированию: DB first или code first. То, что Вы, видимо, пытаетесь делать, это DB first, а настоящий Дзен, как обычно, где-то посередине ))
3.б В смысле архитектуры принято говорить об уровне абстракции доступа к данным (Data Access Layer). Это как раз и есть тот код, который распутывает/запутывает клубки связей между сущностями, чтоб программист на уровне бизнес-логики мог сосредоточится именно на ней, а не на заморачиваться на тему конкретных JOINов или диалекта SQL. Но его тоже кто-то пишет. Готового решения из коробки нет, но есть куча паттернов (от наиболее распространенных, вроде DAO, DTO и до экзотики типа QBE). Но паттерны - это просто общие рекомендации. А по сути все так или иначе сведется либо к использованию ORM, либо к собственному "велосипеду".
4. Эту конкретную схему я набросал в Enterprise Architect и из него же сгенерировал DDL (просто был под рукой на момент написания ответа), но должен предупредить, что EA - далеко не лучший выбор для таких дел. Есть много подобных тулзов попроще, но для конкретно MySQL, сам бог велел воспользоваться MySQL Workbench.

[Anton Dyshkant]

pi314: спасибо! Остался еще один вопрос по данной конкретной реализации: получается, что все property будут храниться в varchar. Есть ли какой-то способ избежать это? Заводить отдельные таблицы под отдельные типа property (и, соответственно, еще по таблице-связке)?

[pi314]

Антон Дышкант: Да, конечно... если нужна еще и типизация свойств на уровне типов данных, обычно заводятся отдельные таблицы под каждый тип данных, и далее, если совсем "нормализовано", то связь с каждой таблицей для типа данных делается через отдельную таблицу связей, а если чуть менее, то ключ типа можно поместить прямо в таблицу свойств. Оба способа имеют, как обычно, свои преимущества и недостатки. Впервом случае любой запрос можно будет сформулировать только средствами SQL (ценой его сложности!), во втором потребуется дополнительная логика в коде.

[Anton Dyshkant]

pi314 хотелось бы ещё кое-что уточнить: на картинке у вас нарисованы связи между таблицами (в частности, между department и plant), а в приведенном коде этих связей нет. Правильно ли я думаю, что в таблице department просто есть поле plant_id?

Answer 2

[Иван]

Учитесь селектить Join'ы. База самая обычная один-ко-многим, ничего в ней нет. Bull вполне допустим, если вписывается в вашу логику.

Answer 3

[sim3x]

МестоРаботы:
  тип = (завод, цех, )
  вышестоящее_место_работы = FK(МестоРаботы)

Работник:
  место_работы = FK(МестоРаботы)

но в коде придется правильно сохранять, чтоб цех не содержал заводов
Также будут проблемы если у заводов и цехов сильно различаются поля

Answer 4

[Smithson]

Вопрос про нормализацию - вы пытаетесь байты экономить или хотите сделать всё по фен-шую?
Учтите, что с полностью нормализированными базами трудно работать человеку, обычно логика так не поворачивается, как там надо.
Так что отталкивайтесь от разумной оптимизации, когда ни у программиста, ни у DBA, ни у пользователя при работе с такой базой не идет кровь из глаз.

Ваш вариант, кстати, вполне нормален, повесить триггеры, чтобы отслеживать события удаления заводов, цехов, переезда цехов из завода в завод и перевода работников - и всё будет вполне терпимо.