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

Введение


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

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

Для начала, разберем создание базы данных в 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.

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

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

А где же семь формальных правил?

Вот они:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

  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.

Когда вы понимаете отношения между субъектами и между субъектами и объектами — уже упомянутые семь формальных правил — эту или схожую схему можно реализовать «на коленке»: на листке бумаги, мене чем за час. И это еще с учетом усталости после плодотворного рабочего дня.

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

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

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

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

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


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

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

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

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

Послесловие


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

Источники


Ads
AdBlock has stolen the banner, but banners are not teeth — they will be back

More

Comments 87

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


    Почему никто не предоставляет такие возможности? Да потому, очевидно, что ни у кого нет таких данных. И взять их в общем-то негде. Единственный их источник — кандидат. А его никто не может обязать давать всю эту информацию, а тем более — давать полную и точную.

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

    То есть, получается что и у кандидата этой информации тоже нет?

    Тоже самое и с навыками. Что это такое, навык? Откуда мы знаем, что у кандидата А навык Б сопоставим с тем же навыком у кандидата В? В чем мы этот навык измеряем? Откуда мы узнаем, что величина этого навыка вот такая?
      +2

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

        +2
        Ну, на самом деле с точки зрения описания дизайна БД текст вполне хорош. Я просто хотел отметить, что понимание того, откуда данные вообще берутся, насколько они точные и т.п. — это желательно иметь еще до проектирования схемы.
          +3

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

            +1
            Ну, я бы сказал так — если ошибиться на этапе оценки свойств данных, то схема потом может поплыть очень даже сильно. Простой пример — если вы считаете, что позиция это что-то точное, хорошо сопоставимое между компаниями, и разных позиций конечное множество — у вас одна схема, если же позиции сопоставляются нечетко — то уже другая. Если навык это просто признак да/нет — то опять же, схема одна, если же навык это что-то типа «5 лет владения» — то снова другая.
              0

              Согласен и ещё хуже в хранилище данных, которому многие года (если переделывать придется, а версионирование схемы никто не заложил ранее)

      +2

      Спасибо за статью. Поясните, пожалуйста, зачем в связи 1-1 в примере человек-паспорт нужны foreign key constraint в обе стороны?

        +1

        Это один из способов гарантировать отношение 1:1, когда сущность разделяют на две сущности. Например, человек и его документы, чтобы не делать очень большую таблицу.

          +4
          А потом в эту стройную картину мира врывается человек, у которого есть два гражданства (и два паспорта, соответственно), причем в обеих локациях айти довольно развито.
            +1

            И потому в конце сказано, что все типы отношений существуют в жизни между субъектами и между субъектами и объектами, и важно в системе зафиксировать нужные для работы формальные связи.
            В данном случае, имелось в виду гражданство РФ для поиска работы в России.
            Т е если подразумевается гражданство одно, то связь будет 1:1, а если гражданств может быть несколько, то многие ко многим, т к одно и тоже гражданство может быть у разных людей.

              +6

              У человека могут быть два и более российских паспорта, другое дело, что действующий должен быть только один.

                0

                Интересный кейс)
                Но в любом случае принимают на работу по одному паспорту, а не по нескольким.

                  +2

                  Принимать могут по одному, а Увольнять нужно будет по другому. При этом, желательно, не потеряв информации о том, по какому принимали.

                    –1

                    Сервис по найму, не по увольнению.
                    Если паспорт меняется, то меняются и соответствующие поля в таблице.
                    В данном случае историю изменений паспорта или его замены хранить не требовалось в отличии от истории работ (т е резюме).

                      0
                      «Сервис по найму, не по увольнению.» Это мощно. Так HR-м и отвечайте когда они будут жаловаться что трудовые не могут найти и паспорта не соответствуют.
                        +1

                        Сервис по поиску работы и внутренняя система организации по учёту работников-это две разные ИС.

            +2

            Вопрос скорее к тому, почему бы обе таблицы не повешать на один общий суррогатный ключ, например CitizenID?

              0

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

                +2

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


                Пример с общим ключом
                create table citizen (
                id integer primary key,
                ...)


                create table personal_data (
                citizen_id integer primary key,
                foreign key citizen_id references citizen(id),
                ...)

                  –1

                  Благодарю за пример. Однако, это пример 1:1 с необязательной связью, т к в таком случае нет гарантий, что для каждой записи из первой таблицы найдется соответствующая ей запись во второй таблице.

                    +2

                    Конечно можно в первую таблицу также добавить foreign key id references personal_data(citizen_id), но придётся один из fk сделать отложенным, чтобы проверялся в конце транзакции. Но и в исходном примере невозможно добавить данные сразу в обе таблицы, одну из них нужно будет ещё и update.

                      0

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

                        0

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

                        +2
                        Для гарантий в базах данных придуманы чеки и триггеры.
                          0

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

              +1

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

                0

                Да, для того есть позиции-т е кем работал специалист-фронт, бэк, девопс и т д

                  +1
                  А если был всеми сразу? :)
                    0

                    Внести несколько записей в историю работы (резюме)

                    +1

                    Ну я к тому, что если хочется нормализации, то можно ввести сущность «роль на проекте», и к ней уже прилинковать скиллы.
                    А если есть желание автоматически раскладывать из какого-нибудь hh.ru по табличкам, то там будет прямо набор скиллов у человека. Или набор скиллов у человека на конкретном проекте. То есть схема в обоих случаях будет другая.

                      0

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

                  +2

                  спасибо за попытку систематизировать.


                  2.1 отношение один ко многим с обязательной связью
                  2.2 отношение один ко многим с необязательной связью

                  я бы не стал делить, рассматривал только случай 2.2, потом отдельно написал про NULL/NOT NULL/FOREIGN KEY/CONSTRAINT (может быть даже отдельная статья).


                  1.1.2 отношение один к одному с обязательной связью в одной сущности (таблице)

                  не вижу ни одной причины в реальной жизни использовать это (и сходу не могу вспомнить когда встречал/использовал).


                  и иллюстрации с паспортами/жёнами/… очень удачные, только выводы из них не сделаны.
                  если БД хранит реальные данные, то нужно учитывать, что "к одному" может меняться со временем (человек может поменять паспорт, сотрудник может перевестись в другой отдел, etc), и такие отношения нужно хранить в привязке к моменту времени.

                    +1
                    На самом деле это было систематизировано уже достаточно давно и много раз. Причем на нескольких уровнях абстракции. Потому что скажем связи типа M:M могут быть разного типа на логическом уровне, допустим, сборочная единица «состоит из» болтов и гаек. При этом если сборочная единица удаляется, то болты и гайки никуда не денутся, их останется такое же число. Соответственно, на более низком уровне физической схемы не применяется on delete cascade. Если это связь типа «имеет вот такое свойство» — то при удалении сущности все ее свойства будут удалены тоже, потому что без сущности они смысла не имеют.
                      +1
                      На самом деле это было систематизировано уже достаточно давно и много раз

                      да кто же спорит. но иногда пытаться объяснить «на пальцах» стоит, такие объяснения не заменяют «академические», а дополняют их, облегчая новичкам погружение в тему.
                      насколько удачна именно эта попытка — сходу не скажу.

                        0

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

                          +1
                          Я совершенно не против попыток «на пальцах». Скорее имелось в виду, что есть другие уровни систематизации, в том числе чуть более высокие, и из принятых там решений уже обычно следуют решения описанного тут уровня.
                            0

                            Совершенно верно, здесь попытка объяснить доступно, просто и коротко.

                      +2
                      The question is, rather, why not hang both tables on one common surrogate key, for example CitizenID?
                        0

                        The answer was given above in the comments

                        +4

                        Ожидание: есть принципы проектирования БД, позволяющие изначально правильно выстроить ее архитектуру, чтобы и гибко было, и работало быстро, сейчас мне про них доступно и коротенечко расскажут.
                        Реальность: есть несколько способов размещать сущности в таблицах БД. Если вы вдруг облажались, решив, что номер паспорта это свойство, а не отдельная сущность, и через некоторое время выяснилось, что связь человек-паспорт это один-ко-многим, то вы можете переделать схему БД, чтобы стало правильно. А потом еще раз переделать, когда вскроется следующая проблема. И еще раз.


                        Спасибо, кэп, но так я и сам умею.

                          0

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

                            +1
                            Собственно, давным-давно существует (существовала) методология того, как из представлений информации, используемых разными компонентами информационной системы, синтезировать общую схему БД для этой системы. Называлась она IDEF1 (или «сущность-связь» — entity-relationship, ER). Описана она была ещё в древних книгах 30-летней давности, лет двадцать назад совершенно точно существовали программы (из числа средств CASE), которые автоматизировали построение и модификацию схемы БД (вплоть до написания скриптов генерации/модификации под конкретную промышленую БД). Одной такой программой — ERwin — я сам довольно активно в то время пользовался.
                            Есть ли подобные программы сейчас, модифицированы ли они под новомодные схемы хранения XML/JSON и прочего No-SQL (потому что тогда они строили схему БД в 3-й нормальной форме реляционной модели) — я даже не знаю.
                            Но это хорошо, что древнее знание не забывается (хотя, может быть — и переоткрывается).
                              0

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

                              +1
                              >Спасибо, кэп, но так я и сам умею.
                              Ну вообще по-другому особо и не бывает. Большинство систем изменяются и развиваются. Вы конечно можете все с первого раза угадать, но гарантировать что нигде потом ничего не изменится — не можете все равно. Потому что не обязательно вскроется проблема — появится изменение постановки задачи.

                              То есть, нет таких принципов. Если вдруг постановка изменилась, и некая связь стала из 1:M вдруг M:M, вам придется перепроектировать минимум эту часть схемы. И еще раз, и еще.
                                0

                                Соглашусь с Вами.
                                Увы, реальность рано или поздно вносит свои коррективы.

                              +3

                              Был рассмотрен сферический конь в вакууме и получено соответствующее решение.


                              Например, скилы:


                              1. Таблица должна быть расширяема. Кандидат должен иметь возможность вводить новые навыки. Потому, что всех навыков ты в системе не предусмотришь.
                              2. Навыки должны иметь связь на себя же, т.к. есть навыки — синонимы.
                              3. Навыки должны иметь иерархическую связь, т.е. есть навыки общего характера (программист), а есть частного (C++).
                              4. Навыки должны иметь связь совместимости, для отображения саджестов. В какой-то степени, может помочь иерархия, но не всегда. Иногда в саджестах программисту нужно отобразить, например, scrum.

                              И тут ключевой вопрос — поддержание консистентности данных. И вот… расширяемость таблицы становится нетривиальной задачей.


                              И это только поверхность айсберга.

                                0

                                П.1 предусмотрен-через добавление
                                П.2 необязателен
                                П.3- не нужно смешивать должности (позиции) и навыки
                                П.4 для этого навыки связываются с проектом, а проект с резюме

                                  +1

                                  П1 обозначен как требование, котрое в итоге выдрержать становится затруднительно. А в вашей схеме — невозможно. Приведите пример как ваша схема противодействует дублированию скилов.
                                  П2 С чего вы взяли, что не обязателен? Вам так видится? Он обязателен. В рельной системе. Более того, еще и релевантность такой связи нужно определить.
                                  П3 должности тут не при делах. Аналитик и бизнес-аналитик. Что тут должность а что навык? Еще? Юрист и юрисконсульт. Scrum-мастер и scrum. Людям свойственно указывать свой навык точно, а вот искать обобщенно.
                                  П4 это не решает проблему. Привидите пример, почему в новой анкете программисту Java будет в топе предложен скил scrum или jira, а не photoshop или 1С.


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

                                    0

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

                                      +1
                                      Ну я так и написал. Дело в том, что сама задача то интересная. Более чем. Но оторванность от реального запроса не позволяет рассмотреть эти примечательные детали.

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

                                      И тут такая штука как master key как int становится неприемлемой. Нужны GUID. Желательно с указанием класса сущности в нем.
                                        +1

                                        Благодарю за замечания, не хотелось перегружать материал.
                                        В реальной системе используются последовательные GUID по возможности (но увы не всегда, т е попадаются GUID непоследовательные).
                                        identity можно использовать, если знать как разделить диапазоны идентификаторов при РСУБД (это не всегда возможно).

                                +3

                                Строго говоря паспорт может быть не один, его может и не быть, мало того это даже может быть не паспорт, а «документ, удостоверяющий личность».

                                  0

                                  Да, так и есть.
                                  Для упрощения был взят паспорт, а не любой документ, удостоверяющий личность.
                                  В данном примере рассматривалась упрощённая модель реальной базы данных для сервиса по поиску соискателей на работу в России. Принимают на работу по одному паспорту, а не по нескольким паспортам.

                                  +1

                                  Такое ощущение, что Вы плохо понимаете нотацию crow foot. Есть куча мест, где по тексту связь обязательна, а на схеме — необязательна.
                                  Пример: п.2.1, цитирую: "У каждого родителя есть как минимум один ребенок.". При этом на схеме у таблицы Child значок "от 0 до N", т.е. детей у родителя может и не быть.
                                  В п.2.2, где наличие детей становится необязательным, Вы почему-то сделали необязательным наличие родителя — со стороны таблицы Person изображена мощность "0 или 1".
                                  Ещё пример — 1.1.2. По Вашей схеме у гражданина наличие паспорта необязательно, как и наличие у паспорта гражданина.
                                  И т.д.

                                    0

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

                                      +1

                                      Нет, не правильно.
                                      Если, как у Вас написано, "У каждого родителя есть как минимум один ребенок.", то связь должна выглядеть вот так:

                                        –1

                                        Вы только что повторили схемы 2.1.2 и 2.2.2

                                          +1

                                          В том-то и дело, что у Вас схема 2.1.2 выглядит так:

                                          Именно это я и пытаюсь до Вас донести.

                                            +1

                                            На всякий случай подытожу — вдруг в будущем эту статью будут читать студенты, надо их предостеречь :) — и больше уже возвращаться не буду.
                                            В нотации crow foot значки и имеют разный смысл, так же, как и и , и в статье много неверных схем в том смысле, что они не соответствуют тексту, например, в тексте речь идёт про обязательность, а на схеме этот конец связи с "ноликом".

                                              0

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

                                                +2

                                                Если Вы мне советуете это почитать, то зря — я как раз-таки понимаю разницу между концептуальной, логической и физической моделями базы данных.
                                                И не могут при переходе между уровнями модели внезапно изменяться свойства связи — мощность, обязательность и т.д.
                                                Поэтому если Вы на концептуальном уровне указали, что на данном конце связи должно быть "ноль или один" — в таком виде это у Вас и доедет до физической модели.
                                                Так что тут Вы опять ошиблись.
                                                И всё равно всё вышесказанное Вами не отменяет того факта, что у Вас в статье схемы не соответствуют тексту. Заканчивайте уже выискивать какие-то странные оправдания в виде размытых фраз.

                                                  –1

                                                  Не хочу задеть, но у меня сложилось впечатление, что Вы теоретик и оперируете академическими понятиями из ВУЗа, где так много уделялось видам стрелок с кружком и без.
                                                  Я практик, и представленные схемы есть мой опыт, а также наблюдаемый опыт коллег, с которыми я работал. В совокупности опыт составил более 10 лет.
                                                  При этом были созданы:
                                                  1) 20+ информационных систем с нуля (принимал участие в проектировании и в разработке)
                                                  2) 60+ информационных систем с нуля или оптимизированы/изменены (в качестве консультанта и иногда разработчика).
                                                  Важно не что Вы знаете, а как применяете и понимаете.
                                                  За весь свой опыт ни разу не увидел, чтобы база данных была хотя бы в первой нормальной форме. Ни разу в успешных системах!
                                                  А на многих курсах выдалбливают эти нормальные формы.
                                                  Важнее понять просто суть нормализации и денормализации, а также научиться формализовать сущности и их связи, поняв 7 формальных правил.
                                                  Все-и вперёд проектировать быстро и четко.

                                                    +2
                                                    Не хочу задеть, но у меня сложилось впечатление, что Вы теоретик и оперируете академическими понятиями из ВУЗа, где так много уделялось видам стрелок с кружком и без.

                                                    Ошибаетесь, я — практик. И если уж мы тут начали пиписками меряться, то моя нынешняя должность называется "Эксперт по разработке". (А вообще Ваша тирада выглядит странно — наличие большого опыта исключает возможность делать ошибки или заблуждаться?)
                                                    И как практик я знаю, что всевозможные схемы (в т.ч. различные схемы БД) служат, в первую очередь, для передачи знаний между членами команды. И если Вы в голове держите "1 или N", а на схеме нарисовали "0 или N", то другой разработчик при имплементации какой вариант реализует, как думаете?

                                                      0

                                                      Конечно все ошибаются.
                                                      Я про то, что Ваши вопросы задайте разработчикам этой диаграммы, что рисует именно так, когда спроектировано верно.
                                                      А также тогда Вам как практику должно быть известно, что Бобер и SSMS рисуют просто связи, не разделяя из кружком.
                                                      Тогда что? Тоже критиковать разработчиков бобра и SSMS?
                                                      Или просто понять, что на логическом уровне эти связи так не выделяют.

                                                    0

                                                    Ещё вопрос: в SSMS в диаграмме тоже верно с кружком и без рисует связи?

                                                      0

                                                      В SSMS диаграмма выглядит так:
                                                      .
                                                      Где кружки?
                                                      Создаётся впечатление, что Вы не увидели сути в публикации и критикуете инструмент по визуализации диаграмм баз данных.
                                                      Даже инструменты на логическом уровне не рисуют Ваших кружков. Тот же Бобер тоже-проверил только что.
                                                      Не путайте модели и тем более не путайте инструменты для визуализации конкретного типа модели базы данных.
                                                      Я не в PowerDesigner это делал, а в простых инструментах, доступных каждому.

                                                        0

                                                        Вау, Вы меня впечатлили! )))))
                                                        Я, вообще-то, говорил о нотации crow foot (которую Вы использовали в статье).
                                                        А то, что SSMS использует какую-то свою, совсем другую — это все знают. Ну, или не все… =D

                                                          0

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

                                                          +2
                                                          Создаётся впечатление, что Вы не увидели сути в публикации и критикуете инструмент по визуализации диаграмм баз данных

                                                          Я даже не знаю, каким Вы инструментом пользовались, и в данном контексте это вообще не важно.
                                                          Услыште меня, я уже устал повторять одно и тоже — Вы неправильно используете нотацию crow foot, в тексте у Вас связь обозначена как обязательная, а на схеме — как необязательная. И эта ошибка повторена неоднократно.
                                                          И это может сбить с толку неопытных читателей Вашей статьи (джунов, студентов и т.п.), только поэтому я за этот момент и зацепился. Детей жалко :)
                                                          А Вы можете сколько угодно оставаться в мире своих заблуждений. Я бы порекомендовал Вам поразбираться с этой нотацией, но что-то мне подсказывает, что Вы этого делать не захотите.
                                                          За сим всё-таки окончательно откланиваюсь, ничего конструктивного Вы так и не сказали (но за SSMS отдельное спасибо, хорошее настроение у меня на весь день обеспечено :)).

                                                            0

                                                            "Я даже не знаю, каким Вы инструментом пользовались, и в данном контексте это вообще не важно"-на этом и остановимся, спасибо.
                                                            Также советую не путать концептуальную и логическую модели базы данных.
                                                            А то по Вашим словам получается, что такие ПО как Бобер и SSMS неверно рисуют диаграммы, что конечно неправда.
                                                            Практик отличается от теоретика тем, что проектирует систему сразу в логической модели, т е в том инструменте, где сразу и будет создавать базу данных, если напр такие инструменты как PowerDesigner недоступны.
                                                            И в конце концов напишите свою публикацию, чем критиковать причем не созданные мною инструменты в адрес данной публикации.

                                          +1

                                          Задавать связь мощности "1:1" через две связи между таблицами нельзя. Тем самым Вы открываете дорогу для возникновения неконсистентного состояния системы.
                                          Пример:


                                          1. В таблице Citizen есть запись с CitizenID == 42 и PassportID == 999 (пусть это будет некто Иванов).
                                          2. В таблице PassportData есть запись с PassportID == 999 и CitizenID == 123 (это айдишник Петрова).

                                          Вопрос: кому принадлежит паспорт с айдишником 999 — Иванову или Петрову?

                                            –1

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

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

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


                                              сложно, но можно.

                                              Если что-то может пойти не так — оно обязательно пойдёт не так.
                                              На предыдущем проекте наелись вот этого самого. В структуре БД не был предусмотрен (нашими предками) контроль консистентности, в результате багов (и ещё неправильных подходов к разработке) накопилось такое огромное количество "мусорных" данных, что решение этой проблемы заняло сотни человеко-часов.

                                                –1

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

                                                  +1

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

                                                    –1

                                                    Структура БД является правильной в зависимости от потребностей.
                                                    В публикации были приведены несколько вариантов.

                                                      +1

                                                      В зависимости от потребностей можно выбирать один из правильных вариантов, например, при связи "1:1" хранить всё в одной таблице или в двух связанных.
                                                      Какие потребности могут заставить выбрать неправильный вариант, я не представляю.

                                                        –1

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

                                              –1

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

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


                                              Я так понимаю имелось ввиду отношение между родителем и детьми?

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


                                              Может я как-то не внимательно прочитал ваш пост, можете пожалуйста объяснить, почему изначально, когда в постановке у нас полная семья с несколькими детьми, то связь не была многие-ко-многим, а стала таковой только поле появления попечителей (родителей)? Ведь изначально оба ребенка имеют двух родителей, и оба родителя имеют двух детей
                                                0

                                                Благодарю за замечания.
                                                Имелось в виду-между родителем и детьми (поправил).
                                                И в последнем замечании тоже верно-изменил (спасибо еще раз).
                                                Вы помогаете публикации становиться лучше.

                                                0
                                                В заголовке стоит добавить, что эти правила справедливы только для реляционных баз данных. А если рассказывать про связи то начинать надо с понятия нормализации базы данных и для чего это, иначе совсем не понятно зачем эти все связи 1:1, 1: М, М: М, а те кто понимают тем уже эта информация не нужна.
                                                  0

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

                                                    0
                                                    1. тогда приведите пример примените эти правила к key/value базам данных или как это ложится на документоориентированные базы данных.
                                                    2. есть еще такое понятие как денормализация, применяется для оптимизации доступа к данным, соотвественно приведение к 1й нормальной форме не имеет смысла кроме как в академических целях. Но без этих основных понятий не понять зачем вводить связи и самое главное как правильно это делать
                                                      0
                                                      1. Не путайне неполно-структурировпнные данных со словарем, а ключ-значение, это по сути словарь, а не модель и не база данных.
                                                        Неполно-структурированные данные бывают документоориентированными (XML, JSON и т д) и графовыми, на документоориентиоованный тип примеры даны в формальных связях. Графовый также дан, где таблица ссылается на саму себя.
                                                      2. Связи существуют вне зависимости есть нормальная форма или нет.
                                                        Достаточно просто понимать смысл нормализации и денормализации.
                                                  +1
                                                  Связь 1.1.2, как было сказано выше — капкан.
                                                  Нужно использовать единую колонку для обоих таблиц и в обоих таблицах уникальный индекс по колонке.
                                                    –1

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

                                                  Only users with full accounts can post comments. Log in, please.