Вот упрощение моей базы данных:
Таблица: Недвижимость Поля: ID, адрес Таблица: Цитата Поля: ID, PropertyID, BespokeQuoteFields ... Таблица: Работа Поля: ID, PropertyID, BespokeJobFields ...
Затем у нас есть другие таблицы, которые относятся к таблицам цен и вакансий в отдельности.
Теперь мне нужно добавить таблицу сообщений, где пользователи могут записывать оставленные клиентами телефонные сообщения о заданиях и предложениях.
Я мог бы создать две идентичные таблицы ( QuoteMessage и JobMessage ), но это нарушает принцип DRY и кажется грязным.
Я мог бы создать одну таблицу сообщений :
Таблица: сообщение Поля: ID, RelationID, RelationType, OtherFields ...
Но это мешает мне использовать ограничения для обеспечения ссылочной целостности. Я также могу предвидеть, что позже возникнут проблемы со стороной разработки, использующей Linq to SQL.
Есть ли элегантное решение этой проблемы, или мне в конечном итоге придется что-то взламывать?
Ожоги
Создайте одну таблицу сообщений, содержащую уникальный идентификатор сообщения и различные свойства, которые необходимо сохранить для сообщения.
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 в таблице сообщений? Или сообщение должно касаться цитаты или работы, а не обоих?