Суррогатный ключ как внешний ключ над составными ключами

Question

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

Учитывая эту спецификацию,

Site
---------------------------
SiteID      int    identity
Name        varchar(50)

Series
---------------------
SiteID      int
SeriesCode  varchar(6)
...
--SeriesCode will be unique for every unique SiteID

Episode
----------------------
SiteID      int
SeriesCode  varchar(6)
EpisodeCode varchar(10)
...

мой предложенный дизайн / реализация

Site
----------------------------
SiteID      int     identity
Name        varchar(50)


Series
-------------------------------------------
SeriesID    int     identity, surrogate key
SiteID      int         natural key
SeriesCode  varchar(6)  natural key
UNIQUE(SiteID, SeriesCode)
...

Episode
-------------------------------------------
EpisodeID   int     identity, surrogate key
SeriesID    int     foreign key
EpisodeCode varchar(6)  natural key
...

Что-то не так с этим? Можно ли использовать здесь суррогат SeriesID в качестве внешнего * ключа? Я не уверен, что мне не хватает очевидных проблем, которые могут возникнуть. Или было бы лучше использовать составные натуральные ключи (SiteID+SeriesCode / SiteID+EpisodeCode)? По сути, это отделило бы таблицу Episode от таблицы Series, и это не подходит мне.

Стоит добавить, что SeriesCode выглядит как 'ABCD-1', а EpisodeCode как 'ABCD-1NMO9' в необработанных входных данных, которые будут заполнять эти таблицы, так что это еще одна вещь, которую можно изменить, я полагаю.

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

Answer 1

Да, все выглядит хорошо. Единственное (второстепенное) замечание, которое я могу сделать, заключается в том, что если у вас нет еще 4-й дочерней таблицы, свисающей с Episode, вам, вероятно, не понадобится EpisodeId, поскольку Episode.EpisodeCode - это естественный ключ с одним атрибутом, достаточный для идентификации и определения местоположения строк в Episode. Конечно, не вредно оставлять его там, но, как правило, я добавляю суррогатные ключи, чтобы действовать в качестве целей для FK в дочерних таблицах, и пытаюсь добавить естественный ключ к каждой таблице, чтобы идентифицировать и контролировать избыточные строки данных... Поэтому, если у таблицы нет другой таблицы, на которую ссылается FK, (и никогда не будет), я иногда не буду включать в нее суррогатный ключ.

Answer 2

Мое предложение:

Используйте естественный / бизнес как первичный ключ, когда это возможно, за исключением следующих 3 ситуаций:

1. Натуральный / бизнес-ключ неизвестен на момент вставки
2. Натуральный / бизнес-ключ не хорош (он не уникален, он может часто меняться)
3. Натуральный / бизнес-ключ состоит из более чем трех столбцов, и таблица будет иметь дочерние таблицы

В ситуациях 1 и 2 требуется суррогатный ключ.

В ситуации 3 настоятельно рекомендуется суррогатный ключ.

Answer 3

Что такое "виртуальный" внешний ключ? Решили ли руководители высшего звена не использовать ограничения внешнего ключа? В этом случае вы вообще не используете внешние ключи. Ты просто притворяешься.

И является ли Эпизод лучшим выбором для сущности? Разве это не означает "Шоу", "Подкаст" или что-то в этом роде, и просто сейчас всегда является частью серии? Если так, это изменится в будущем? Будет ли Эпизод со временем использоваться для показа за пределами Серии? В этом случае привязка Эпизода к Сайту через Серию может снова преследовать вас.

Учитывая все это, и предполагая, что вы как хрюканье, вероятно, не можете ничего изменить: если бы я был вами, я чувствовал бы себя более безопасным, используя естественные ключи везде, где это возможно. В отсутствие ограничений по внешнему ключу это облегчает распознавание неверных данных, и если позже вам придется прибегнуть к какой-то хитрости SeriesCode='EMPTY', то это проще сделать и с естественными ключами.