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

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

  • Запрос назначения
  • Запрос поддержки
  • Запрос на обмен
  • Запрос депозита

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

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

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


sql sql-server foreign-keys database-design ddl
person Brendon Dugan    schedule 21.05.2012    source источник
comment
Есть ли у таблиц какие-либо общие атрибуты, кроме идентификатора и комментариев?   -  person keyser    schedule 21.05.2012
comment
В общем, хранить метаданные вместе с реальными данными — плохая идея. Хранение справочной таблицы вместе с справочным идентификатором не будет работать так же хорошо, как наличие нескольких таблиц.   -  person JNK    schedule 21.05.2012
comment
@Keyser: у каждого из них есть внешний ключ, указывающий на таблицу User, и у каждого из них есть RequestDate и RequestStatus (и я полагаю, что у всех у них есть RequestID), но кроме этого они хранят совершенно фантастически разные данные.   -  person Brendon Dugan    schedule 21.05.2012
comment
@JNK: Я согласен по большей части. Мне просто было интересно, есть ли другое решение, которое обеспечило бы достоверную целостность данных и сэкономило бы мне 3 или 4 таблицы для управления.   -  person Brendon Dugan    schedule 21.05.2012
comment
@BrendonDugan, лучше иметь больше столов. Учтите возможность того, что ваши исходные таблицы изменятся. Допустим, вы добавляете еще одно поле в ключ для Destination по необходимости - вам нужно очень сложным образом перестроить всю таблицу комментариев.   -  person JNK    schedule 21.05.2012
comment
@JNK: я полностью согласен. Однако я не упомянул, что добавляю функцию в приложение, срок действия которого истекает в ближайшие 90 дней. Новая версия приложения разрабатывается гораздо менее тупоголовым, и это просто пластырь, чтобы задержать нас, пока новая версия не будет готова. Такого рода проблемы гораздо проще решить на этапе планирования, чем в конце жизненного цикла приложения.   -  person Brendon Dugan    schedule 22.05.2012

Ответы (2)


arrow_upward
3
arrow_downward

В качестве пластыря, вы можете сделать что-то вроде этого...

введите здесь описание изображения

... со следующим ограничением на Note:

CHECK (
    (
        “Destination Request Id” IS NOT NULL
        AND “Support Request Id” IS NULL
        AND “Exchange Request Id” IS NULL
        AND “Deposit Request Id” IS NULL
    )
    OR (
        “Destination Request Id” IS NULL
        AND “Support Request Id” IS NOT NULL
        AND “Exchange Request Id” IS NULL
        AND “Deposit Request Id” IS NULL
    )
    OR (
        “Destination Request Id” IS NULL
        AND “Support Request Id” IS NULL
        AND “Exchange Request Id” IS NOT NULL
        AND “Deposit Request Id” IS NULL
    )
    OR (
        “Destination Request Id” IS NULL
        AND “Support Request Id” IS NULL
        AND “Exchange Request Id” IS NULL
        AND “Deposit Request Id” IS NOT NULL
    )
)

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

person Branko Dimitrijevic    schedule 21.05.2012
comment
Это кажется лучшим решением. - person Brendon Dugan; 22.05.2012

arrow_upward
1
arrow_downward

У вас может быть таблица Note(object_id, note), где object_id ссылается на любую из таблиц запросов. На самом деле вам не нужно иметь ограничение внешнего ключа в базе данных, или, если вы действительно хотите сохранить ограничение, вы можете использовать предложение ограничения (может быть, зависит от поставщика?).

Чтобы определить, с какой таблицей запросов связана заметка, у вас может быть значение типа перечисления в Note. У некоторых поставщиков баз данных есть тип Enum, у других вы можете просто использовать тип Int. Это будет просто какой-то столбец, например перечисление request_type (DESTINATION, SUPPORT и т. д.).

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

С некоторыми поставщиками (postgres) вы можете использовать наследование таблиц. С наследованием таблиц вы должны сделать что-то вроде create table destination_request(....) inherits(note); Это сделало бы любые поля в таблице заметок доступными для запросов в таблице destination_request. В каком-то смысле это работает и работает за вас за кулисами. Концептуально запрос не является подтипом примечания, поэтому он не идеален с точки зрения объектно-ориентированного проектирования, а наследование таблиц, вероятно, не может быть перенесено на другие поставщики баз данных. Вам решать, насколько вы заботитесь о чистоте здесь.

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

person nairbv    schedule 21.05.2012