Основы правил проектирования базы данных

Введение

Как это часто бывает, архитектору БД нужно разработать базу данных под конкретное решение.
Однажды в пятницу вечером, возвращаясь на электричке домой с работы, подумал, как бы я создал сервис по найму сотрудников в разные компании. Ведь ни один из существующих сервисов не позволяет быстро понять насколько подходит тебе кандидат, нельзя создать сложные фильтры именно по конкретным навыкам, проектам и позициям или исключающие определенные навыки, позиции и проекты. Максимум, что предлагают сервисы — это фильтры по компаниям и частично по навыкам.

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

В данном примере разберем создание базы данных в MS SQL Server для сервиса поиска соискателей на работу.

Также данный материал можно перенести и на другую СУБД такую как MySQL или PostgreSQL.

Основы правил проектирования

Для проектирования схемы базы данных, нужно вспомнить 7 формальных правил и саму концепцию нормализации и денормализации. Они и лежат в основе всех правил проектирования.

Опишем более детально 7 формальных правил:

1. отношение один к одному:
   1.1) с обязательной связью:
   примером может выступать гражданин и его паспорт (у любого гражданина должен быть паспорт, и паспорт один для каждого гражданина)
   
   Реализовать данную связь можно двумя способами:
   1.1.1) в одной сущности (таблице):
   
   
   Рис.1. Сущность Citizen
   
   Здесь таблица Citizen представляет из себя сущность гражданина, а атрибут (поле) PassportData содержит все паспортные данные гражданина и не может быть пустым (NOT NULL).
   
   1.1.2) в двух разных сущностях (таблицах):
   
   
   Рис.2. Отношение сущностей Citizen и PassportData
   
   Здесь таблица Citizen представляет из себя сущность гражданина, а таблица PassportData представляет из себя сущность паспортных данных гражданина (т е самого паспорта). Сущность гражданина содержит атрибут (поле) PassportID, который ссылается на первичный ключ таблицы PassportData. В свою очередь сущность паспортных данных содержит атрибут (поле) CitizenID, которое ссылается на первичный ключ CitizenID таблицы Citizen. Поле PassportID таблицы Citizen не может быть пустым (NOT NULL). Также здесь важно поддерживать целостность и поля CitizenID таблицы PassportData, чтобы обеспечить связь один к одному. Т е поле PassportID таблицы Citizen и поле CitizenID таблицы PassportData должны ссылаться на одни и те же записи как если бы это была одна сущность (таблица), которая была показана в пункте 1.1.1.
   
   1.2) с необязательной связью:
   примером может выступать человек, у которого может быть паспорт, а может и не быть конкретной страны. Т е в первом случае он будет являться гражданином рассматриваемой страны, а во втором-нет.
   
   Реализовать данную связь можно двумя способами:
   1.2.1) в одной сущности (таблице):
   
   Рис.3. Сущность Person
   
   Здесь таблица Person представляет из себя сущность человека, а атрибут (поле) PassportData содержит все паспортные данные человека и может быть пустым (NULL).
   
   1.2.2) в двух сущностях (таблицах):
   
   
   Рис.4. Отношение сущностей Person и PassportData
   
   Здесь таблица Person представляет из себя сущность человека, а таблица PassportData представляет из себя сущность паспортных данных человека (т е самого паспорта). Сущность человека содержит атрибут (поле) PassportID, который ссылается на первичный ключ таблицы PassportData. В свою очередь сущность паспортных данных содержит атрибут (поле) PersonID, которое ссылается на первичный ключ PersonID таблицы Person. Поле PassportID таблицы Person может быть пустым (NULL). Также здесь важно поддерживать целостность и поля PersonID таблицы PassportData, чтобы обеспечить связь один к одному. Т е поле PassportID таблицы Person и поле PersonID таблицы PassportData должны ссылаться на одни и те же записи как если бы это была одна сущность (таблица), которая была показана в пункте 1.2.1, или же данные поля должны быть неопределенными, т е содержать NULL.
   
2. отношение один ко многим:
   2.1) с обязательной связью:
   примером могут выступать родитель и его дети. У каждого родителя есть как минимум один ребенок.
   
   Реализовать данную связь можно двумя способами:
   2.1.1) в одной сущности (таблице):
   
   
   Рис.5. Сущность Parent
   
   Здесь таблица Parent представляет сущность родителя, а атрибут (поле) ChildList содержит в себе информацию о детях, т е самих детей. Данное поле не может быть пустым (NOT NULL). Обычно типом поля ChildList выступает неполно структурированные данные (NoSQL) такие как XML, JSON и т д.
   
   2.1.2) в двух сущностях (таблицах):
   
   
   Рис.6. Отношение сущностей Parent и Child
   
   Здесь таблица Parent представляет сущность родителя, а таблица Child представляет сущность ребенка. У таблицы Child есть поле ParentID, которое ссылается на первичный ключ ParentID таблицы Parent. Поле ParentID таблицы Child не может быть пустым (NOT NULL).
   
   2.2) с необязательной связью:
   примером может выступать человек, у которого могут как быть дети, так и полностью отсутствовать.
   
   Реализовать данную связь можно двумя способами:
   2.2.1) в одной сущности (таблице):
   
   
   Рис.7. Сущность Person
   
   Здесь таблица Parent представляет сущность родителя, а атрибут (поле) ChildList содержит в себе информацию о детях, т е самих детей. Данное поле может быть пустым (NULL). Обычно типом поля ChildList выступает неполно структурированные данные (NoSQL) такие как XML, JSON и т д.
   
   2.2.2) в двух сущностях (таблицах):
   
   
   Рис.8. Отношение сущностей Person и Child
   
   Здесь таблица Parent представляет сущность родителя, а таблица Child представляет сущность ребенка. У таблицы Child есть поле ParentID, которое ссылается на первичный ключ ParentID таблицы Parent. Поле ParentID таблицы Child может быть пустым (NULL).
   
   2.2.3) в одной сущности со ссылкой на саму себя при условии, что у сущностей (таблиц) родителя и ребенка будут одинаковые наборы атрибутов (полей) без учета ссылки на родителя:
   
   
   Рис.9. Сущность Person со связью на саму себя
   
   Здесь сущность (таблица) Person содержит атрибут (поле) ParentID, которое ссылается на первичный ключ PersonID этой же таблицы Person и может содержать пустое значение (NULL).
   
   Также данная реализация является примером реализации и отношения многие к одному с необязательной связью.
   
3. отношение многие к одному:
   Эту связь можно зеркально рассмотреть к рассмотренной выше связи один ко многим, т е отношение сущности дети к сущности родители, где обязательная связь будет если предполагается, что у ребенка есть хотя бы один родитель, а если в рассмотрение взять всех детей, в том числе и которые находятся в детских домах, то такое отношение будет с необязательной связью.
   
4. отношение многие ко многим:
   здесь примером может выступить недвижимость, которая может находиться в собственности как у одного человека, так и у нескольких. С другой стороны, у человека может быть во владении несколько домов или долей собственности нескольких домов.
   
   Реализовать данное отношение, привлекая NoSQL, можно подобными способами, описанными выше для предыдущих отношений. Однако, в рамках реляционной модели обычно такое отношение реализуют через 3 сущности (таблицы):
   
   
   Рис.10. Отношение сущностей Person и RealEstate
   
   Здесь таблицы Person и RealEstate представляют сущности человека и недвижимость соответственно. Связываются данные сущности (таблицы) через сущность (таблицы) PersonRealEstate через атрибуты (поля) PersonID и RealEstateID, которые ссылаются на первичные ключи PersonID таблицы Person и RealEstateID таблицы RealEstate соответственно. Обратите внимание, что для таблицы PersonRealEstate пара (PersonID; RealEstateID) является всегда уникальной и потому может выступать первичный ключем для самой связующей сущности PersonRealEstate.
   
   Также данное отношение можно реализовать и через более 3-х сущностей, добавляя нужные атрибуты, которые ссылаются на первичные ключи необходимых соответствущих сущностей. Такая реализация похожа на примеры, описанные выше в пунктах 1.1.2 и 1.2.2.
   

Также отношения один ко многим и многие к одному можно реализовать и более чем через 2 сущности, добавляя нужные атрибуты, которые ссылаются на первичные ключи необходимых соответствущих сущностей. Такая реализация похожа на примеры, описанные выше в пунктах 1.1.2 и 1.2.2.

А где же 7 формальных правил?
Вот они:

1. п.1 (п.1.1 и п.1.2)-это первое и второе формальных правила
2. п.2 (п.2.1 и п.2.2) — это третье и четвертое формальных правила
3. п.3 (аналогично п.2) — это пятое и шестое формальных правила
4. п.4 — это седьмое формальное правило

Просто в тексте выше эти 7 формальных правил объединены в 4 блока по функционалу.

Про нормализацию стоит понимать ее суть, т е что она ведет к уменьшению повторяемости хранения информации, а следовательно и к уменьшению возможности появления аномалий в данных. Однако, нормализация при дроблении сущностей ведет к более сложным построениям запросов для манипуляций с данными (вставки, модификации, выборки и удаления).

Обратным процессом нормализации называется денормализация, т е упрощает построение запросов для доступа к данным за счет укрупнения и вложенности сущностей (например, как это было показано выше в пунктах 2.1.1 и 2.2.1 с помощью неполно-структурированных данных (NoSQL)).

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

А вы уверены что поняли отношения в 7 формальных правил? Именно поняли, а не узнали. Ведь знать и понять-это совершенно два разных понятия.

Объясню более детально. Спросите себя-можете ли вы за пару часов набросать пусть и укрупненный по сущностям, но модель базы данных для любой предметной области и для любой информационной системы? (тонкости и детали можно достроить, поспрашивав аналитиков и представителей заказчиков). Если вопрос вас удивил и вы думаете что это невозможно, то значит вы знаете 7 формальных правил, но не понимаете их.

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

Также эти отношения могут меняться, переходя из один к одному к одному ко многим или многие к одному, или многие ко многим, меняя обязательность этой связи или оставляя ее без изменений.

Позволю себе рассказать небольшой отрывок из своей истории, где от знания семи формальных правил я пришел к пониманию этих отношений.

В свое время меня смущало то, что в ВУЗе я знал эти 7 отношений, но очень долго строил модели базы данных для разных предметных областей в производственных практиках (когда ВУЗ отправляет студентов в компании для приобретения опыта по профилю). И я задумавшись понял, что не понимаю этих отношений.

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

Сон был про семью, в которой есть и отец, и мать, а также дети. Со временем отец погибает в автомобильной катастрофе, а мать начинает пить. Потому в итоге детей забирают в детский дом от матери и долгое время дети остаются без родителей. Затем появляются попечители для некоторых из этих детей. Этих попечителей тоже несколько.

Давайте посмотрим внимательно. Вы проследили какие отношения были между субъектами и как менялись эти отношения?

Когда была полная семья и было несколько детей, то отношение между родителем имело вид один ко многим с обязательной связью. Однако, если рассматривать любую семью, где может и не быть детей, то отношение будет такое же, но уже с необязательной связью.
А вот со стороны детей отношение к родителю было как многие к одному с обязательной связью пока последние были живы и здоровы и с необязательной связью, когда эти дети оказались в детском доме.

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

Отношение между мужем и женой было как один к одному с обязательной связью при регистрации этих отношений, например официально через регистрацию брака, и с необязательной связью до этой регистрации, т к жена может быть только одна, как и муж может быть только один в России. Однако, в другой стране может быть несколько жен и тогда связь между мужем и женами будет как один ко многим, а между женой и мужем как многие к одному.

Надеюсь, теперь вы значительно приблизились к пониманию этих 7 формальных правил.

Рекомендую постоянно практиковаться-наблюдать за людьми и выявлять существующие отношения как между субъектами, так и между субъектами и объектами (вспомните-выше описывался гражданин и паспорт как отношение один к одному с обязательной связью, а также человек и паспорт как отношение один к одному с необязательной связью).

После понимания 7 формальных правил, вы без труда сможете спроектировать любой сложности модель базы данных для любой информационной системы.

Также придет понимание, что реализовать отношение можно разными способами и что сами отношения могут меняться. Потому модель (схема) базы данных-это снимок отношений между сущностями в определенный момент времени. Потому важно определить как сами сущности, являющиеся образами объектов из реального мира или предметной области, так и их отношения между собой с учетом изменений в будущем.

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

Особенно это важно для хранилищ данных, где изменения влекут пересохранение больших объемов данных от нескольких гигабайт до многих терабайт данных.

Замечание. Вообще в реляционной модели таблицы-это отношения сущностей, а строки (кортежи)-это экземпляры этих отношений. Но для простоты понимания часто под таблицами понимают сущности, а под строками таблицы-экземпляры сущностей, а их отношения через связи в форме внешних ключей.

Проектирование схемы базы данных для поиска соискателей на работу

После того, как мы описали основы правил проектирования БД в первой части статьи, создадим схему базы данных для поиска соискателей на работу.

Для начала определим что важно для сотрудников из компании, которые осуществляют поиск соискателя на работу:

1. для HR:
   1.1) компании, где работал соискатель
   1.2) позиции, которые занимал соискатель в данных компаниях
   1.3) навыки и умения, которыми пользовался соискатель в работе
   а также продолжительность работы соискателя в каждой компании и на каждой позиции, длительность использования каждого навыка и умения
   
2. для технического специалиста:
   2.1) позиции, которые занимал соискатель в данных компаниях
   2.2) навыки и умения, которыми пользовался соискатель в работе
   2.3) проекты, в которых участвовал соискатель
   а также продолжительность работы соискателя на каждой позиции и в каждом проекте, длительность использования каждого навыка и умения
   

Для начала выявим нужные сущности:

1. сотрудники (Employee)
2. компании (Company)
3. позиции (должности) (Position)
4. проекты (Project)
5. навыки (Skill)

Компании и сотрудники имеют отношение многие ко многим, т к сотрудник мог работать в нескольких компаниях, а в компании работают несколько сотрудников.

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

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

Аналогично и по проектам, т е проекты относятся ко всем выше рассмотренным сущностям как многие ко многим.

Для простоты будем считать, что в проекте сотрудник использует один набор навыков.
Тогда между проектами и навыками будет отношение многие ко многим.

С учетом важности фиксации длительности пребывания сотрудника в той или иной компании и на той или иной позиции (должности), а также на том или ином проекте, то схема нашей базы данных может быть следующей:


Рис.11. Схема базы данных для поиска соискателей на работу

Здесь таблица JobHistory выступает в виде сущности истории работы каждого соискателя, т е самого резюме, которое и реализует отношения многие ко многим между сущностями сотрудник, компания, позиция и проект.

Проекты и навыки относятся друг другу как многие ко многим и потому связываются между собой через сущность (таблицу) ProjectSkill.

При понимании отношений между субъектами и между субъектами и объектами, т е семи формальных правил, данную или подобную схему можно реализовать на листке бумаги на коленке менее чем за 60 минут с учетом того, что вы возвращаетесь уставшим после плодотворного рабочего дня.

Здесь можно было упростить схему и добавления данных, если навыки вложить в сущность проекты через неполно структурированные данные (NoSQL) в виде XML, JSON или просто перечисляя названия навыков через точку с запятой. Но это бы усложнило выборку с группировкой по навыкам и фильтрацию по отдельным навыкам.

Подобная полученная модель лежит в основе базы данных проекта Geecko.

Как видите ничего сложного в проектировании информационных систем (в части проектирования базы данных) нет. Это всего лишь отражение объектов и субъектов из реального времени, перенесенного в сущности схемы базы данных, где фиксируются отношения между этими сущностями на определенный момент времени с учетом будущих изменений.

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

Немного лирики

И после того как вы внедрите модель в работу, остановитесь на миг и подумайте о том, что только что был создан новый маленький мир со своими сущностями из реального мира и со своими отношениями. Да, это цифровой мир, но он будет теперь развиваться по своему пути. Он будет общаться (интегрироваться) с другими системами (мирами), которые тоже были созданы по своим правилам, а данные будут течь в этих системах как кровь в живом организме.

А перед сном подумайте о том, что 7 формальных правил были всегда и что они окружают нас во всем. Ни больше и не меньше, всегда 7. Все отношения в реальной жизни можно разложить на эти 7 формальных правил. А когда вы думаете или когда видите сны как там сущности относятся друг к другу-не по тем же 7 формальным правилам?

Вообще я уверен, что эти отношения (семь формальных правил) выявил очень хороший психотерапевт и даже возможно женщина причем очень давно и задолго до появления самого понятия информационные технологии. И самое интересное, что эти отношения живут вне базы данных и ИТ, а последние лишь используют эти естественные правила для моделирования информационных систем.

Но мы немного отошли от темы. Я лишь призываю в момент создания новой системы подойти к этому процессу с душой и тогда поверьте-это будет как момент творения, и спроектированная таким образом система будет живее всех живых в цифровом мире.

Послесловие

Диаграммы для примеров были реализованы с помощью инструмента Database Diagram Tool for SQL Server. Однако, подобный функционал есть и в DBeaver.

Источники

• SQL Database Design Basics with Example
• Geecko
• Microsoft SQL documentation
• SSMS
• Database Diagram Tool for SQL Server