Для: архитекторов программного обеспечения, администраторов баз данных и внутренних разработчиков
Базам данных уделяется много внимания, и они должны. Во взаимоотношениях код становится все более и более зависимым от базы данных, а база данных растет за счет потребности в новой функциональности и коде.
Чтобы быть максимально устойчивым к будущим изменениям, в RDBMS-land нужно стремиться к максимальному нормальному состоянию для своих данных. Если вы уже решили, что вам необходима РСУБД, чтобы выполнить задание, и совет "никогда не проектируйте на будущее"на самом деле больше не актуален. Потому что будущее вашего приложения будет выглядеть очень мрачным, если вы не продумаете тщательно свою схему данных (здесь я предполагаю значительную долговечность и расширение функциональности приложения).
Итак... О чем этот пост в блоге?
Между структурой базы данных и приложением существует тесная связь. Обойти это невозможно. Но что я считаю нехорошим, так это устанавливать правила в отношениях более одного раза. Это чревато расхождениями в логике между базой данных и кодовой базой. Может быть бесчисленное множество триггеров, ограничений внешнего ключа, которые блокируют, каскадируют или устанавливают на ноль, и вы не узнаете о них, если не выполните свое действие и не проанализируете результат, или вам придется имитировать логику базы данных в вашем коде. (который со временем сломается из-за расхождений).
Сделать проблему видимой
Давайте сначала запустим базу данных
╭─tim@The-Incredible-Machine ~ ╰─➤ sudo apt-get install mysql-server-5.7
И заполните его некоторыми данными. Я нашел этот пример базы данных на github. Если вы также используете его, чтобы учиться у или вместе с ним, пожалуйста, поставьте репозиторию звезду, чтобы люди знали, что они не выкладывают материал напрасно.
╭─tim@The-Incredible-Machine ~/Git ╰─➤ git clone [email protected]:datacharmer/test_db.git Cloning into 'test_db'... remote: Counting objects: 94, done. remote: Total 94 (delta 0), reused 0 (delta 0), pack-reused 94 Receiving objects: 100% (94/94), 68.80 MiB | 1.71 MiB/s, done. Resolving deltas: 100% (50/50), done. Checking connectivity... done. ╭─tim@The-Incredible-Machine ~/Git/test_db ‹master› ╰─➤ mysql -u root -p < employees.sql Enter password: INFO CREATING DATABASE STRUCTURE INFO storage engine: InnoDB INFO LOADING departments INFO LOADING employees INFO LOADING dept_emp INFO LOADING dept_manager INFO LOADING titles INFO LOADING salaries data_load_time_diff 00:01:02
Чтобы увидеть, что у нас есть, я реконструировал диаграмму базы данных из базы данных. Это звучит сложнее, чем есть на самом деле. Откройте MySQL Workbench, убедитесь, что вы установили соединение с работающей службой MySQL, перейдите на вкладку инструментов «база данных» и используйте функцию «обратного проектирования». Workbench создаст для вас схему, так называемую EER (Enhanced Entity Relationship Diagram).
Диаграмма EER примера базы данных
Мы можем в основном видеть, что все отношения каскадируются, когда происходит удаление, и ограничиваются, когда происходит обновление. Поэтому, когда сотрудника удаляют, он или она будет удален из отдела, больше не будет менеджером, и вся история зарплат и должностей будет удалена.
Итак, теперь давайте найдем одного менеджера отдела.
mysql> select * from dept_manager limit 1; +--------+---------+------------+------------+ | emp_no | dept_no | from_date | to_date | +--------+---------+------------+------------+ | 110022 | d001 | 1985-01-01 | 1991-10-01 | +--------+---------+------------+------------+ 1 row in set (0,00 sec)
И попросите базу данных объяснить свой план по удалению сотрудника, который, как мы знаем, является менеджером.
mysql> explain delete from employees where emp_no = 110022; +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | DELETE | employees | NULL | range | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using where | +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 1 row in set (0,00 sec)
Так чего мне не хватает?
Мне не хватает функции, подобной объяснению, которая не дает мне метаинформацию об оптимизаторе запросов, но фактическую информацию о том, что произойдет - с точки зрения отношений - если я удалю объект из моей базы данных с учетом определенного запрос.
Я ожидаю, что он вернет такие данные, как:
- какой стол
- сколько записей будет затронуто
- какая будет операция (restrict, cascade, null)
И точно так же, как объяснение может быть расширено, запуск этого в расширенном режиме также может дать объединенный в группу список первичных ключей, разделенных запятой и причудливостью csv, чтобы не разбивать строку (используя разделители строк, escape-символы, все, что вы считаете нужным ). Представьте себе, какую фантазию вы могли бы реализовать, фактически информируя пользователя о том, какая запись является виновником.
Назовите мне несколько примеров, где мне это нужно!
Давайте представим, что мы создали CRM-систему. А пока представим, что у нас есть
- организации
- адреса
- счета-фактуры
- контакты
- Примечания
Некоторые вопросы, которые могут возникнуть:
- Могу ли я удалить организацию, или к ней все еще прикреплен необработанный счет (блокирующее ограничение)? Лучше не показывать пользователю кнопку удаления и не указывать причину, почему это нельзя сделать, чем закрыть этот вопрос и откатить действие.
- Когда я удаляю организацию, что с ней будет?
- Будут ли также удалены обработанные счета-фактуры
(надеюсь, что нет! В этом случае лучше установить значение null, счет-фактура должен (помимо org.id) также хранить копию всех соответствующих данных организации)? - Будут ли удалены необработанные счета?
(Возможно, лучше всего заблокировать в этой ситуации, так как все еще есть потенциал денег или есть основания полагать, что пользователь этого не хочет) - будут ли удалены заметки?
(Вы можете это сделать, но только если будете очень многословны) - будут ли удалены контакты?
(Часто нет, но, поскольку такие отношения часто связаны со многими, эти связующие записи следует удалить) - Что я на самом деле знаю об этом отношении? Что именно связано? Очень приятно динамически создавать графики того, как данные связаны и насколько актуальны эти данные на самом деле.
Зачем решать это в базе данных?
Я работал над двумя приложениями, каждое из которых насчитывало более 150 таблиц, и все они были взаимосвязаны, и я действительно могу сказать, что вложение значительных усилий в философствование над вашей схемой базы данных — это не роскошь, а необходимость.
База данных содержит атомарные значения. Он закладывает основу, с которой начинает работать ваш код. Это по своей сути означает, что все, что мы можем сделать на этом уровне для его защиты, мы должны сделать.
Пара преимуществ решения как можно большего в базе данных
- Если к базе данных подключаются несколько инструментов или приложений, все они должны следовать одним и тем же соглашениям.
- все, что уже находится в базе данных, не нужно передавать в базу данных, что экономит пропускную способность.
- Ограничения внешнего ключа используют индексированные столбцы по своей природе. Ваши запросы будут выполняться быстрее, поскольку данные, в которых ваши таблицы обычно связаны друг с другом, уже проиндексированы.
- Вам не нужно полагаться на сверхпрозрачное именование (кстати, вы должны сделать это в любом случае), но каждый, кто заглянет в вашу базу данных, поймет, как таблицы связаны друг с другом.
Это копия из моего блога Ministry Of Dev.
