Database Design Best Practice for Generic Objects

  • I have general question regarding design practices for generic child objects which may belong to many different types of parent objects. In my case, I'm keeping a note history table that has the columns NOTE_ID (Int), OWNER_ID (GUID), NOTE (nvarchar), MODIFIED_BY (int), MODIFIED_DATE (datetime). The note has a many-to-one relationship with the owner. Instead of having a Note Table for each different type of object that has notes, I was trying to use a single table. This simplifies the design, but I seem to be compromising on integrity, since I can no longer use a foreign key on the owner_id column. If I were to write a sproc to delete a parent object, I'm going to have to remember that I need to delete the Notes for the parent as well. If I had a separate note table for each parent, I could use a foreign key and have a cascade delete.

    It seems to be a trade off. I was just wondering if there's anything I'm missing? Or if there is a best practice for this scenario?

    Thanks much

  • walshbd (5/8/2013)


    I have general question regarding design practices for generic child objects which may belong to many different types of parent objects. In my case, I'm keeping a note history table that has the columns NOTE_ID (Int), OWNER_ID (GUID), NOTE (nvarchar), MODIFIED_BY (int), MODIFIED_DATE (datetime). The note has a many-to-one relationship with the owner. Instead of having a Note Table for each different type of object that has notes, I was trying to use a single table. This simplifies the design, but I seem to be compromising on integrity, since I can no longer use a foreign key on the owner_id column. If I were to write a sproc to delete a parent object, I'm going to have to remember that I need to delete the Notes for the parent as well. If I had a separate note table for each parent, I could use a foreign key and have a cascade delete.

    It seems to be a trade off. I was just wondering if there's anything I'm missing? Or if there is a best practice for this scenario?

    Look at your ER Model, if those "Notes" are different entities most probably you should implement them as different tables.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • the above solution is correct if you want to go with and maintain data integrity with normal forms.

    but there are scenerios where in due to some constarints you are unable to mainatain FR's

    what suitably a solution can be is to maintain a XXX_history table.

    so when you delete a node a delete trigger can insert records in XXX_history table.

    this way you can have a job/automated process which compares the object in XXX_history table and XXX table and delete records based on that.

    hope that works.

  • kritika (5/9/2013)


    the above solution is correct if you want to go with and maintain data integrity with normal forms.

    but there are scenerios where in due to some constarints you are unable to mainatain FR's

    what suitably a solution can be is to maintain a XXX_history table.

    so when you delete a node a delete trigger can insert records in XXX_history table.

    this way you can have a job/automated process which compares the object in XXX_history table and XXX table and delete records based on that.

    hope that works.

    Can you explain what you mean here? I am having a hard time figuring out how this relates to the OP's question.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply