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

Вот упрощение моей базы данных:

Таблица: Недвижимость
Поля: ID, адрес

Таблица: Цитата
Поля: ID, PropertyID, BespokeQuoteFields ...

Таблица: Работа
Поля: ID, PropertyID, BespokeJobFields ...

Затем у нас есть другие таблицы, которые относятся к таблицам цен и вакансий в отдельности.

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

Я мог бы создать две идентичные таблицы ( QuoteMessage и JobMessage ), но это нарушает принцип DRY и кажется грязным.

Я мог бы создать одну таблицу сообщений :

Таблица: сообщение
Поля: ID, RelationID, RelationType, OtherFields ...

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

Есть ли элегантное решение этой проблемы, или мне в конечном итоге придется что-то взламывать?

Ожоги

21.08.2008 09:12:00
4 ОТВЕТА
РЕШЕНИЕ

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

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

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

Table: QuoteMessage
Fields: QuoteId, MessageId

Table: JobMessage
Fields: JobId, MessageId

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

4
28.02.2013 21:01:52

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

1
21.08.2008 09:48:49

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

1
21.08.2008 11:56:57

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

0
21.08.2008 19:44:31