这是我的数据库的简化：

Table: Property
Fields: ID, Address

Table: Quote
Fields: ID, PropertyID, BespokeQuoteFields...

Table: Job
Fields: ID, PropertyID, BespokeJobFields...

然后，我们还有其他分别与Quote和Job表相关的表。

现在，我需要添加一个"消息"表，用户可以在其中记录客户留下的有关"工作和报价"的电话消息。

我可以创建两个相同的表(QuoteMessage和JobMessage)，但这违反了DRY主体，并且看起来很杂乱。

我可以创建一个消息表：

Table: Message
Fields: ID, RelationID, RelationType, OtherFields...

但这阻止了我使用约束来强制我的参照完整性。我还可以预见，稍后会使用Linq to SQL在开发方面造成问题。

是否有解决此问题的简便方法，还是我最终将不得不一起破解某些东西？

烧伤

解决方案

回答

创建一个Message表，其中包含一个唯一的MessageId以及我们需要为一条消息存储的各种属性。

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

创建两个链接表QuoteMessage和JobMessage。这些将仅包含两个字段，分别是Quote / Job和Message的外键。

Table: QuoteMessage
Fields: QuoteId, MessageId

Table: JobMessage
Fields: JobId, MessageId

这样，我们仅在一个位置定义了消息的数据属性(使扩展和查询所有消息变得容易)，但是我们还具有将引用和作业链接到任意数量的消息的参照完整性。实际上，Quote和Job都可以链接到同一条消息(我不确定这是否适合业务模型，但至少数据模型为我们提供了选择)。

回答

我能想到的唯一另一种方法是拥有一个同时具有Id和TypeId的基本Message表。然后，子表(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条。这是否是一个好的折衷取决于应用程序流程。

至于违反DRY的2张相同的桌子，我不会挂在那上面。在数据库设计中，与DRY无关，而与标准化有关。如果我们要建模的两个事物具有相同的属性(列)，但实际上是不同的事物(表)，那么拥有多个具有相似模式的表是合理的。比将不同的事物组合在一起的逆转要好得多。

回答

@烧伤

伊恩的答案(+1)是正确的[请参见注释]。使用多对多表QUOTEMESSAGE将QUOTE加入MESSAGE是最正确的模型，但是会留下孤立的MESSAGE记录。

这是可以使用触发器的罕见情况之一。但是，需要注意确保单个" MESSAGE"记录不能同时与" QUOTE"和" 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字段？还是消息必须与报价或者工作有关，而不是两者兼而有之？