My<cod/>.net
SQL2005: связывание таблицы с несколькими таблицами и сохранение целостности ссылок?
Вот упрощение моей базы данных:
Table: Property Fields: ID, Address Table: Quote Fields: ID, PropertyID, BespokeQuoteFields... Table: Job Fields: ID, PropertyID, BespokeJobFields...
Затем у нас есть другие таблицы, которые относятся к таблицам котировок и заданий по отдельности.
Теперь мне нужно добавить таблицу сообщений , где пользователи могут записывать телефонные сообщения, оставленные клиентами относительно заданий и котировок.
Я мог бы создать две идентичные таблицы (QuoteMessage и JobMessage), но это нарушает принцип DRY и кажется беспорядочным.
Я мог бы создать одну таблицу сообщений :
Table: Message Fields: ID, RelationID, RelationType, OtherFields...
Но это останавливает меня от использования ограничений для обеспечения моей ссылочной целостности. Я также могу предвидеть, что это создает проблемы со стороной devlopment, используя Linq для SQL позже.
Есть ли элегантное решение этой проблемы, или мне в конечном итоге придется взломать что-то вместе?
Ожоги
Создайте одну таблицу сообщений, содержащую уникальный MessageId и различные свойства, которые необходимо сохранить для сообщения.
Table: Message
Fields: Id, TimeReceived, MessageDetails, WhateverElse...
Создайте две таблицы ссылок-QuoteMessage и JobMessage. Они будут содержать только два поля, внешние ключи к цитате / заданию и сообщению.
Table: QuoteMessage
Fields: QuoteId, MessageId
Table: JobMessage
Fields: JobId, MessageId
Таким образом, вы определили свойства данных сообщения только в одном месте (что упрощает расширение и запрос по всем сообщениям), но у вас также есть ссылочная целостность, связывающая кавычки и задания с любым количеством сообщений. Действительно, и Цитата, И задание могут быть связаны с одним и тем же сообщением (я не уверен, подходит ли это для вашей бизнес-модели, но, по крайней мере, модель данных дает вам возможность).
О единственном другом способе, который я могу придумать, - это иметь базовую таблицу сообщений с идентификатором и TypeId. Ваши подтаблицы (QuoteMessage и JobMessage) затем ссылаются на базовую таблицу как на MessageId, так и на TypeId, но также имеют ограничения CHECK для них, чтобы применять только соответствующий MessageTypeId.
Table: Message
Fields: Id, MessageTypeId, Text, ...
Primary Key: Id, MessageTypeId
Unique: Id
Table: MessageType
Fields: Id, Name
Values: 1, "Quote" : 2, "Job"
Table: QuoteMessage
Fields: Id, MessageId, MessageTypeId, QuoteId
Constraints: MessageTypeId = 1
References: (MessageId, MessageTypeId) = (Message.Id, Message.MessageTypeId)
QuoteId = Quote.QuoteId
Table: JobMessage
Fields: Id, MessageId, MessageTypeId, JobId
Constraints: MessageTypeId = 2
References: (MessageId, MessageTypeId) = (Message.Id, Message.MessageTypeId)
JobId = Job.QuoteId
Что это дает вам, по сравнению с просто JobMesssage и QuoteMessage таблицы? Это повышает сообщение до гражданина первого класса, так что вы можете прочитать все сообщения из одной таблицы. В обмен на это ваш путь запроса от сообщения до соответствующей цитаты или задания составляет еще 1 соединение. Это зависит от вашего потока приложений, является ли это хорошим компромиссом или нет.
Что касается 2 одинаковых таблиц, нарушающих DRY - я бы не зацикливался на этом. В дизайне DB речь идет не столько о DRY,сколько о нормализации. Если две вещи, которые вы моделируете, имеют одинаковые атрибуты (столбцы), но на самом деле разные вещи (таблицы) - тогда разумно иметь несколько таблиц с похожими схемами. Гораздо лучше, чем наоборот жевать разные вещи вместе.
@burns
Ответ Яна (+1) верен [см. Примечание] . Использование таблицы "многие ко многим" QUOTEMESSAGE для соединения QUOTE с MESSAGE является наиболее правильной моделью, но оставит потерянными записи MESSAGE .
Это один из тех редких случаев, когда триггер может быть использован. Однако необходимо соблюдать осторожность, чтобы гарантировать, что запись a single MESSAGE не может быть связана как с A QUOTE , так и с A JOB .
create trigger quotemessage_trg
on quotemessage
for delete
as
begin
delete
from [message]
where [message].[msg_id] in
(select [msg_id] from Deleted);
end
Обратите внимание на Яна, Я думаю, что есть опечатка в определении таблицы для JobMessage, где столбцы должны быть JobId, MessageId (?). Я бы отредактировал вашу цитату, но мне может потребоваться несколько лет, чтобы получить такой уровень репутации!