Блог

Нужен совет по дизайну 2 DB

Предположим, у нас есть 2 таблицы Artefact и User . У артефакта есть ссылка на пользователя, который его создал. Но в течение довольно долгого времени в нашей устаревшей системе эта ссылка сохранялась как строковое имя, поэтому теперь невозможно восстановить creator для всех артефактов (некоторые соответствующие записи из User таблицы исчезли). Итак, возможны 2 варианта:

1. Создайте столбец идентификатора пользователя с нулевым значением для Artefact таблицы и заполните его null для артефактов, создатель которых уже удален.
2. Создайте специальную запись по умолчанию «Несуществующий пользователь» в User таблице и ссылайтесь на нее из Artefact таблицы для всех записей с неизвестным создателем.

На данный момент мне интересно, какой из вариантов лучше?

Плюсы второго подхода

1. Более простые предикаты sql (без бесконечных isnull() и left joins )
2. Более согласованное решение — у Artefact объекта всегда есть родительский User , и вы можете положиться на это

Недостатки второго подхода

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

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

Ваши плюсы второго подхода:

1. Более простые предикаты sql (без бесконечных isnull() и left joins )

К сожалению, у вас все еще есть другая бесконечная идиома: user <> ‘Несуществующий пользователь’. (Единственная экономия — это действительно слово, оставленное при объединении между артефактом и пользователем.)

2. Более согласованное решение — у Artefact объекта всегда есть родительский User , и вы можете положиться на это

Но SQL позволяет проверять внешний ключ от артефакта (пользователя) к пользователю, если он не равен нулю.

Простой реляционный дизайн заключается в создании отдельной таблицы:

 ArtefactUser(artefact, user)  
    pk (artefact, user)  
    fk artefact references Artifact not null
    fk (user) references User not null
  

Вы можете быть довольны этим. Тогда простая версия SQL для сохранения при объединениях заключается в том, чтобы пользователь был столбцом с нулевым значением в Artefact. Это ваш вариант 1.

Вам просто нужно знать идиомы при использовании нулей (из-за сложности SQL 3VL), чтобы удалить их как можно ближе к концам выражений запроса.

Повторите его недостатки:

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

Например: мы бы не хотели, чтобы «Несуществующий пользователь» когда-либо использовался в качестве имени пользователя. Поэтому было бы неплохо, чтобы пользователь user был varchar (n), а пользователь Artefact был varchar (n) U {non_existing_user}, где non_existing_user — это некоторое значение / токен / перечисление, отличное от любой строки. И соответствующее ограничение для FK было бы (select user from Artefact where user<>non_existing_user) <= (select user from User) . Самое близкое, что можно получить в SQL, — это использование нулей. Но NULL не обрабатывается в запросах как обычное значение. (И не похоже ни на что, что означает «неизвестно» или «неприменимо».)

Например: в вашем сообщении и комментарии вы постоянно пишете с ошибками «Несуществующий пользователь». (Также «артефакт».) Нет проверки типов.

Два задействованных столбца действительно принимают разные наборы значений. Кроме этого, если бы NULL не включал 3VL, это было бы просто значение, которого не было ни в одном типе, но которое также мог содержать столбец. По сути, это ваш вариант 2. Так что вариант 2 не страшен. (Пользователь должен быть ограничен, чтобы иметь user <> ‘Несуществующий пользователь’.)

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

(Не забудьте рассмотреть возможность выбора отдельной таблицы.)