SQL2005: linking a table to multiple tables and maintaining Ref integrity?

Question

Here is a simplification of my database:

Table: Property
Fields: ID, Address

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

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

Then we have other tables that relate to the Quote and Job tables .

Now I need to add a Message table where users can record phone messages left by customers regarding Jobs and Quotes.

I could create two identical tables ( QuoteMessage and JobMessage ), but this violates the DRY principle and seems messy.

I could create one Message :

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

But this prevents me from using restrictions to ensure my referential integrity. I can also foresee that it creates problems with the devlopment side, using Linq to SQL later.

Is there an elegant solution to this problem, or will I eventually have to hack something?

Burns

Answer 1

Create one message table containing a unique MessageId and various properties that you need to save for the message.

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

Create two link tables - QuoteMessage and JobMessage. They will contain only two fields, foreign keys to Quote / Job and Message.

Table: QuoteMessage
Fields: QuoteId, MessageId

Table: JobMessage
Fields: JobId, MessageId

, Message ( ), , , , Quote Job ( , -, ).

Answer 2

, , , , 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? , . , , Quote Job, - . , .

2 , DRY - . , . , , (), () - . , , .

Answer 3

@burns

(+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

Why not just have QuoteId and JobId fields in the message table? Or should the message concern either a quote or a task, and not both?