SQL2005: связать таблицу с несколькими таблицами и сохранить целостность ссылки?

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

Table: Message
Fields: Id, TimeReceived, MessageDetails, WhateverElse...

Создайте две таблицы ссылок - QuoteMessage и JobMessage. Они будут содержать только два поля, внешние ключи для цитаты / задания и сообщения.

Table: QuoteMessage
Fields: QuoteId, MessageId

Table: JobMessage
Fields: JobId, MessageId

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

О единственном другом способе, о котором я могу подумать, - это иметь базовую таблицу сообщений, содержащую как Id, так и TypeId. Ваши подтаблицы (QuoteMessage и JobMessage) затем ссылаются на базовую таблицу в MessageId и TypeId, но также имеют CHECK CONSTRAINTS для принудительного применения только подходящего 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 одинаковых таблиц, нарушающих СУХОЙ - я бы не стал зацикливаться на этом. В дизайне БД речь идет не о СУХОЙ, а о нормализации. Если две вещи, которые вы моделируете, имеют одинаковые атрибуты (столбцы), но на самом деле разные вещи (таблицы) - тогда разумно иметь несколько таблиц с одинаковыми схемами. Гораздо лучше, чем перебирать разные вещи вместе.

@burns

Ответ Яна (+1) правильный [см. Примечание] . Используя многие ко многим таблицы , QUOTEMESSAGEчтобы присоединиться QUOTEк MESSAGEнаиболее правильная модель, но оставит сиротами MESSAGEзаписи.

Это один из тех редких случаев, когда можно использовать триггер. Однако необходимо соблюдать осторожность, чтобы гарантировать, что одна 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(?). Я бы отредактировал вашу цитату, но мне понадобится несколько лет, чтобы получить такой уровень репутации!

Почему бы просто не иметь поля QuoteId и JobId в таблице сообщений? Или сообщение должно касаться цитаты или работы, а не обоих?