Сложный вопрос о дизайне реляционной БД

Вариант № 1 гарантирует, что каждая страница принадлежит одному и только одному другому объекту, хотя на самом деле связь не может быть установлена, поскольку поле parentId может указывать более чем на одно место.

Верно. С точки зрения реляционной теории проблема заключается в том, что ваш столбец "parentId" нарушает третью нормальную форму, потому что его значение зависит от строки в зависимости от значения в parentType (не ключевой столбец).

У вас не будет правильно спроектированной базы данных, если один столбец может содержать чей-то номер телефона или его дату рождения в каждой строке, в зависимости от какого-либо другого флага. Это два разных факта о человеке, и каждый из них заслуживает отдельной колонки. Точно так же сохранение site_id или node_id в одном столбце будет иметь ту же проблему.

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

Вариант № 2 чище, но в основном он говорит, что сайт «принадлежит» двум страницам с ошибками и не найденным, и это, вероятно, плохая практика.

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

Я бы сказал, что логически верно, что страница с ошибкой и страница «Не найдено» принадлежат сайту, а не наоборот. И способ сделать их обязательными состоит в том, чтобы другой объект ссылался на эти страницы и применял ограничение NOT NULL к этим ссылкам. Это то, что вы описали.

CREATE TABLE site (
  . . .
  error_page_id     INT NOT NULL,
  notfound_page_id  INT NOT NULL,
  FOREIGN KEY (error_page_id)    REFERENCES pages (page_id),
  FOREIGN KEY (notfound_page_id) REFERENCES pages (page_id)
);

Это отвечает вашим неотложным потребностям, это применимо и находится в нормальной форме.

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

@Tony Andrews предлагает сохранять два столбца на каждой странице, site_id и site_node_id и добавление ограничения CHECK, чтобы гарантировать, что ровно одно из них не равно NULL. Это кажется лучше, чем вариант parent_id/parent_type, но он по-прежнему не обеспечивает какого-либо принудительного выполнения того, что на каждом сайте должны быть страницы с ошибками и страницами «Не найдено».

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

Другой вариант — иметь 2 столбца site_id и site_node_id следующим образом:

create table pages
 ( page_id ... primary key
 , site_id references sites
 , site_node_id references site_nodes
 , ...
 , constraint site_or_node check (  site_id is null and site_node_id is not null
                                 or site_id is not null and site_node_id is null
                                 )
 );

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

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

Модификация Варианта 1.

Включите два отдельных столбца, ParentNodeID и ParentSiteID. Оставьте один из этих двух столбцов NULL, в зависимости от случая. Теперь вы все еще можете объявить ограничение внешнего ключа (ссылки) для каждого внешнего ключа.

Я не очень понимаю случай SiteNotFound. Не могли бы вы оставить оба внешних ключа NULL в этом случае?

Ваши присоединения и поиски будут проще. Вы также будете придерживаться 1NF. Это не совпадение.

Ваш вариант 1 объединяет значения, взятые из разных доменов, в одном поле. Это плохой дизайн поля, и IIRC нарушает 1NF.