Hi,

I'm extremely thankful to dBforums for being such a great resource. First post, here we go!

My question has to do with how to store "notes" for various objects (tables) in a database. This notes table is similar to what you might see in the Journal in Microsoft Outlook: it allows timestamped comments with additional details and with relationships to other "objects" in the database.

There are several "parent" objects that need to have this journaling functionality. It would be time-consuming (but very possible, as this was the original design) to add a separate "notes" table (and associated tables) for each "object" that requires this functionality.

However, what is the trade-off of having one master notes table that has an extra field to note the "parent" object and its key, and letting some extra business rules/programming handle keeping things in order (instead of using database rules such as cascades)? I'd like this notes table to be standardized across the entire database.

Or, any other ideas that might be useful to attack this situation?

Thanks!
Mark

FWIW, I am VERY much in favor of a single "notes" table with a column to indicate which object the notes apply to. In fact, I'd suggest that you have a separate row for each comment, since that will probalby make things a boat load easier if you ever need to report on this thing (and that almost always comes sooner than you planned)!

Just to make Rudy happy, I'd be perfectly willing to settle for table names instead of object ids for the foreign key in this case!

-PatP

Hello,

Some things I have done in the past.

Create your "notes" table. What your "notes" really are is probably something to do with "history", or that might be how you think about it.

Some ideas.

Create an identity column. This will be your primary key. Trust me on this.
Have a "table origin". This represents the table a given note "supports."
You will also have a "parent key". This represents the unique key into the parent table.
Have an "original date". Have a "submitter" or user that made the note. Status sometimes.

Sometimes (usually) you need a "last updated date" and "last user update". When inserting, the original date and user are the same as "last updated date and user."

You will find business reasons to break types of "notes" out. For instance, "Phone calls", "Tech Support", "sales calls", etc. Sometimes the notes might be from the same table, yet have different business purposes. It gets a little confusing sometimes where to put these different business tracking attributes.

Sometimes you need a history or log of when "notes" were updated. That adds complexity and can be done a variety of ways.

When retrieving, you probably want to retrieve by Parent Table and Parent key.

I would submit that this would be a poor table to have a clustered index on, but that is probably a different discussion for the theory folks.