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

Question

Howdy,

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 individually.

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

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

I could create one Message table:

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

But this stops me from using constraints to enforce my referential integrity. I can also forsee it creating problems with the devlopment side using Linq to SQL later on.

Is there an elegant solution to this problem, or am I ultimately going to have to hack something together?

Burns

Answer 1

Create one Message table, containing a unique MessageId and the various properties you need to store for a message.

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

Create two link tables - QuoteMessage and JobMessage. These will just contain two fields each, foreign keys to the Quote/Job and the Message.

Table: QuoteMessage
Fields: QuoteId, MessageId

Table: JobMessage
Fields: JobId, MessageId

In this way you have defined the data properties of a Message in one place only (making it easy to extend, and to query across all messages), but you also have the referential integrity linking Quotes and Jobs to any number of messages. Indeed, both a Quote and Job could be linked to the same message (I'm not sure if that is appropriate to your business model, but at least the data model gives you the option).

@Guy - thanks, typo corrected.

Answer 2

About the only other way I can think of is to have a base Message table, with both an Id and a TypeId. Your subtables (QuoteMessage and JobMessage) then reference the base table on both MessageId and TypeId - but also have CHECK CONSTRAINTS on them to enforce only the appropiate 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

What does this buy you, as compared to just a JobMesssage and QuoteMessage table? It elevates a Message to a first class citizen, so that you can read all Messages from a single table. In exchange, your query path from a Message to it's relevant Quote or Job is 1 more join away. It kind of depends on your app flow whether that's a good tradeoff or not.

As for 2 identical tables violating DRY - I wouldn't get hung up on that. In DB design, it's less about DRY, and more about normalization. If the 2 things you're modeling have the same attributes (columns), but are actually different things (tables) - then it's reasonable to have multiple tables with similar schemas. Much better than the reverse of munging different things together.

Answer 3

Ian Nelson: I did think of that, but how can I implement an ON DELETE CASCADE from my Quote/Jobs tables to the Message?

Surely the delete will cascade to the link tables, but carry no further? Hence my ref integrity problem.

Burns

Answer 4

@burns

Ian's answer (+1) is correct [see note]. Using a many to many table QUOTEMESSAGE to join QUOTE to MESSAGE is the most correct model, but will leave orphaned MESSAGE records.

This is one of those rare cases where a trigger can be used. However, caution needs to be applied to ensure that the a single MESSAGE record cannot be associated with both a QUOTE and a 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

Note to Ian, I think there is a typo in the table definition for JobMessage, where the columns should be JobId, MessageId (?). I would edit your quote but it might take me a few years to gain that level of reputation!

Answer 5

Thanks Guy.

Answer 6

Why not just have both QuoteId and JobId fields in the message table? Or does a message have to be regarding either a quote or a job and not both?