这是我的数据库的简化：
1
2
3
4
5
6
7
8
| Table: Property
Fields: ID, Address
Table: Quote
Fields: ID, PropertyID, BespokeQuoteFields...
Table: Job
Fields: ID, PropertyID, BespokeJobFields... |
然后，我们还有其他分别与Quote和Job表相关的表。
我现在需要添加一个消息表，用户可以在其中记录客户留下的有关工作和报价的电话消息。
我可以创建两个相同的表(QuoteMessage和JobMessage)，但这违反了DRY主体并且看起来很杂乱。
我可以创建一个消息表：
1
2
| Table: Message
Fields: ID, RelationID, RelationType, OtherFields... |
但是，这阻止了我使用约束来强制我的参照完整性。我还可以预见它稍后会使用Linq to SQL在开发方面造成问题。
这个问题是否有一个优雅的解决方案，还是我最终将不得不一起破解一些东西？
烧伤
创建一个消息表，其中包含唯一的MessageId以及需要为消息存储的各种属性。
1
2
| Table: Message
Fields: Id, TimeReceived, MessageDetails, WhateverElse... |
创建两个链接表-QuoteMessage和JobMessage。这些将仅包含两个字段，分别是Quote / Job和Message的外键。
1
2
3
4
5
| Table: QuoteMessage
Fields: QuoteId, MessageId
Table: JobMessage
Fields: JobId, MessageId |
通过这种方式，您仅在一个位置定义了消息的数据属性(使扩展和查询所有消息变得容易)，但是您还具有引用完整性，可以将Quotes和Jobs链接到任意数量的消息。实际上，Quote和Job都可以链接到同一条消息(我不确定这是否适合您的业务模型，但至少数据模型可以为您提供选择)。
@burns
Ian的答案(1)是正确的[请参见注释]。使用多对多表QUOTEMESSAGE将QUOTE连接到MESSAGE是最正确的模型，但是会留下孤立的MESSAGE记录。
这是可以使用触发器的罕见情况之一。但是，需要注意确保单个MESSAGE记录不能同时与QUOTE和JOB关联。
1
2
3
4
5
6
7
8
9
10
11
12
| 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(？)。我会编辑您的报价，但是要获得这样的声誉可能要花几年时间！
关于我能想到的另一种方法是拥有一个基本的Message表，同时包含一个Id和一个TypeId。然后，您的子表(QuoteMessage和JobMessage)同时在MessageId和TypeId上引用基表-但它们也具有CHECK CONSTRAINTS来仅强制执行适当的MessageTypeId。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| 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个相同的表违反了DRY-我不会挂断电话。在数据库设计中，与DRY无关，而与标准化有关。如果您要建模的2个事物具有相同的属性(列)，但实际上是不同的事物(表)，那么拥有多个具有相似模式的表是合理的。比将不同的事物聚在一起的相反方法要好得多。
为什么在消息表中不仅同时具有QuoteId和JobId字段？还是消息必须与报价或工作有关，而不是两者都有关？
