首页 / 知识

关于数据库：SQL2005：将一个表链接到多个表并保留引用完整性？

2023-04-12 17:20:00

SQL2005: Linking a table to multiple tables and retaining Ref Integrity?

这是我的数据库的简化：

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字段？还是消息必须与报价或工作有关，而不是两者都有关？

引用完整性关于数据库数据库