SQL2005：将一个表链接到多个表并保留引用完整性？

Question

这是我的数据库的简化：

Table: Property
Fields: ID, Address

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

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

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

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

我可以创建两个相同的表（QuoteMessage 和 JobMessage），但这违反了 DRY 原则并且看起来很混乱。

我可以创建一个 Message 表：

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

但这会阻止我使用约束来强制引用完整性。 我还可以预见它会在稍后使用 Linq to SQL 的开发端产生问题。

这个问题有一个优雅的解决方案吗，还是我最终不得不将一些东西组合在一起？

伯恩斯

Answer 1

为什么不在消息表中同时包含 QuoteId 和 JobId 字段？ 或者消息是否必须涉及报价或工作，而不是两者？

Answer 2

我能想到的唯一其他方法是拥有一个基本消息表，其中包含 Id 和 TypeId。 然后，您的子表（QuoteMessage 和 JobMessage）引用 MessageId 和 TypeId 上的基表 - 但也对它们进行检查约束，以仅强制执行适当的 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 - 我不会对此耿耿于怀。 在数据库设计中，更多的是规范化，而不是 DRY。 如果您正在建模的两个事物具有相同的属性（列），但实际上是不同的事物（表） - 那么拥有多个具有相似模式的表是合理的。 比把不同的东西混在一起要好得多。

Answer 3

@burns

Ian 的回答 (+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

Ian 请注意，我认为 JobMessage 的表定义中存在拼写错误，其中的列应为 JobId、MessageId（？）。 我会编辑你的引言，但我可能需要几年的时间才能获得这样的声誉！

Answer 4

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

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

创建两个链接表 - QuoteMessage 和 JobMessage。 这些将只包含两个字段，即报价/作业和消息的外键。

Table: QuoteMessage
Fields: QuoteId, MessageId

Table: JobMessage
Fields: JobId, MessageId

通过这种方式，您仅在一处定义了消息的数据属性（使其易于扩展和跨所有消息查询），但您还具有将报价和工作链接到任意数量的消息的引用完整性。 事实上，报价和工作都可以链接到相同消息（我不确定这是否适合您的业务模型，但至少数据模型为您提供了选择）。