Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2007
    Posts
    6

    Referential Integrity Problem

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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.

  3. #3
    Join Date
    Sep 2007
    Posts
    6
    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

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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"!

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    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
    George
    Home | Blog

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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...

  7. #7
    Join Date
    Sep 2007
    Posts
    6
    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

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote 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.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Sep 2007
    Posts
    6
    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

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Sep 2007
    Posts
    6
    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

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote 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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Sep 2007
    Posts
    6
    Not really, I dislike both to be honest. I just wish there was a more elegant solution.

    Thanks for all the help anyway though.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •