How to canonically organize the structure of tables in a MySQL database?

Question

MySQL

Good afternoon!
I am interested in the question of how to properly organize the table structure in the MySQL database, if it is necessary to describe the following conditional situation with its help:
There are a certain number of employees. An employee may or may not work. If an employee works, then the plant where he works, as well as the shop where he works, must be indicated.
There are a number of factories, but not all of them are divided into workshops.
Question: what tables should be created and what should be the relationships between them.
The simplest solution, of course, is to create a worker table:

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

factories table:

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

and the shop table:

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

However, this solution contains a number of unpleasant moments:

• a non-working employee will contain plant_id = NULL, department_id = NULL (although the second directly follows from the first)
  
• an employee working in a plant with no shops will contain department_id = NULL (although, by and large, this is a property of the whole plant, not a specific employee)
  
• an employee working at a plant with workshops will contain plant_id = x, department_id = y, and at the same time, the department table will have id = y, plant_id = x (i.e. there will be some redundancy)
  

Could you advise the canonical, most normalized and reference version of solving this problem?
And if the workshops of different factories have different properties, they also would not want to be stored in the same table. How should the shop floor table(s) be organized, and how should the plant and worker refer to them?
Thank you.

Answer 1

Here is an example of a highly normalized model for the declared Wishlist (as far as I understood them).

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;

But, as already said, normalization is not an end in itself! With requests to this model, the programmer will already have some Kama Sutra, and whether they will be optimal in terms of performance is a separate issue altogether. So, I will not get tired of repeating: the optimization of the database structure is not to achieve the maximum possible NF, but to ensure that exactly those queries that should be executed on it are optimally executed on it))

Answer 2

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

Answer 3

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

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

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

Answer 4

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