[littlecharva]

Hi,

Say I have a few tables for collecting different kinds of records in my DB:

Customer
ID, Name, Address, Postcode

Client
ID, Name

Property
ID, Address, CreationDate

Now each of these tables have a web form to allow the user to edit records, and on each of those web forms, I want to provide the user the ability to log a message, so I have a table such as:

Message
ID, Table, Reference, Message, CreationDate

Where Table contains the Table that stores the related record, and Reference contains the relevant ID. So if I were displaying the Property with ID = 3, I could do a:

SELECT Message FROM Message WHERE Table = 'Property' AND ID = 3

To get all related Messages. Works fine.

The problem I have is with referential integrity. If I delete Property #3, I want the Messages to be deleted too. Likewise with Customer and Client. How can I acheive this?

Thanks,
Anthony Burns

[andrewst]

You can't use ON DELETE CASCADE because of the generic nature of your Messages table. Depending on your DBMS you could perhaps write a database trigger to do the job, or allow deletions only via a stored procedure (API) that deletes the messages as well. Alternatively you could just let the messages become "orphans" and write a periodic batch job to purge them.

[littlecharva]

I would imagine this to be a reasonably common (or certainly not rare) design problem. Is there not a design pattern about to deal with these sorts of things?

Am I designing my database the wrong way? Is there a better way to acheive the functionality I require?

Anthony

[andrewst]

Well, you could create a message table per entity type: customer_message, client_message, ... Then standard RI could implement the cascade delete. Apart from creating a few more tables, does this have any drawbacks for you?

I have seen both ways done, but nothing as grand as a "design pattern"!

[georgev]

This looks suspiciously like the impementation of a OTLT (One True Lookup Table)!

I'm afraid Andrewst is right - because of your structure you cannot simply use cascading.

One possible solution would be to have a NotesID in each of your tables which is the foreign key reference to the Messages table. However, this (in theory) makes your messages table the parent to the others... Hmmm

[aschk]

Going by some simple logic here I would say that the notes that are being added are specific to whichever section they're in.
i.e.
The program user is in the client section (editing client info), and thus ANY notes they should be adding ought to be related to the section they're in (clients).

Think about it, why would you write a note about a customer in the client section? They're separate things.

Thus it would seem logic that you have client_notes, customer_notes, and property_notes, that are SPECIFIC to their section only. Bring forth your referential key...

[littlecharva]

Surely un-necessary duplication like that is bad design? When I want to make a change to my table structure I have to make that change to multiple tables, and if I want to query all messages logged by an individual I have to join all tables together. And then if I add a new table that requires me to add another xxx_Message table, I then have to update all my other stuff to incorporate the new message table.

I guess I'll have to go without referential integrity and live with the orphans.

Thanks for your advice anyway though, at least I know my options.

Oh, the poor orphans.

Anthony

[andrewst]

Originally Posted by littlecharva

Surely un-necessary duplication like that is bad design? When I want to make a change to my table structure I have to make that change to multiple tables, and if I want to query all messages logged by an individual I have to join all tables together. And then if I add a new table that requires me to add another xxx_Message table, I then have to update all my other stuff to incorporate the new message table.

Well, it is necessary duplication (of structure, not of data) if you want to use regular SQL RI constraints to perform the cascade delete. Whether it is bad design depends on how many of these tables there will be, how much they really change, how well it performs, how hard it is to maintain the code etc. There are trade-offs involved, and you need to weigh these up.

[r937]

Originally Posted by littlecharva

Am I designing my database the wrong way?

yes

any time you store meta data -- such as a table name -- you are making a design error

when you do this --

Message
ID, Table, Reference, Message, CreationDate

Where Table contains the Table that stores the related record, and Reference contains the relevant ID.

it becomes impossible to define the Reference FK, therefore you are not assured of RI

[littlecharva]

georgev: Are you suggesting something like:

Client
ID, MessageID, Name
01, 56, Bill Jones

Message
ID, Message
56, Bill is Dead

? That would only allow me one message per record though, and I need more.

R937: That's exactly my point. Having only one Message table prohibits me from using RI, but having a Message table for each of the main tables (possibly 15+) becomes a maintenance nightmare.

aschk: I see your point, but the structure of the tables would be identical, there could be in excess of 15 message tables, and I would want to provide an inbox style page where a user could view all of their messages in one place, which would involve multiple joins and having to update that code each time I added a new table.

andrewst: It is a trade-off you're right, I was just hoping there was going to be an easy answer.

Anthony

[r937]

nobody said you have to have 15 message tables

i would agree that one message table is better

it is "linking" a message to one of 15 different tables that is the design challenge

here's a step in the right direction: suppose your message table had 15 foreign keys, each of them nullable (since on any given row, 14 of the 15 will actually be null)

then you could define proper FKs and actually have the database enforce them

[littlecharva]

I like your thinking, but feel like having so many null fields is a bad design too. How about having a linking table for each section such as:

Client
ID,Name
54,Bill

Message
ID,Message
98,Do stuff

Client_Message
ClientID,MessageID
54,98

That would enable me to retain RI yeah? And although I would essentially have a lookup table for each main table, I wouldn't be duplicating the message structure and I'd still be keeping my messages stored together. How does that strike people?

Also, back to the Meta data thing. I'd also still want to keep a record in the Message table as to what the message is related to for my application's benefit, so I'd still be storing stuff like:

ID,Relation,Message
98,Client,Do stuff

So when I listed the messages in an inbox, I could then provide a link the the Client page along with it. Is this still a no-no?

Anthony

[r937]

Originally Posted by littlecharva

How about having a linking table for each section

you prefer 15 linking tables to one table with 15 nullable Fks?

[littlecharva]

Not really, I dislike both to be honest. I just wish there was a more elegant solution.

Thanks for all the help anyway though.

[r937]

elegant?

look, in post #1 you mention 4 tables, and each one of them has a column called ID

isn't that also inelegant? how come you aren't also looking to find a way to combine them?