Database design issues
 

Hi, i'm designing a database and ive kinda run into a design problem and im not sure the best route to take to acheive what i want. I have a database with about 20 tables in it, about 15 of those tables need to have comments available for them, actually each row in the table must havemultiple comments. So say i have a table called hardware and each row stores information about a particular piece of hardware. I need the ability to add mulitple comments to a row(piece of hardware) but this needs to be done on 15 tables. So far my choices are

-create an extra table for every table that i need comments for(bad::to many tables)
-create a one table that stores all comments(bad::need to make a table name lookup table, bad design)

Anyone know of a better way to design a universal comment table which can reference a given row in a particular table?

I think I would go with the second option but not bother with a lookup table to help performance.

well then how do i know which row of which table it belongs to? if i have hardware, platform, software, and i need to store multiple comments for each row in those three tables how would i do that?

You can just store a uid that you know relates to a specific table ie:
hardware=1
software=2

There is no need to actually put this information into a linked table as it is only for a very small amount of choices that can be easily hardcoded.

Maybe I don't understand the issue for 100%, but what about simply adding a column "comments" to each table that need one?

I think the idea is that each row in the "hardware" table could have any number of comments. Adding an extra field is fine when their is one comment per row, but if a row has 1000 comments you wouldn't want 1000 extra fields.

Option 1. Create an extra table .. why is it bad? After all 15 tables become only 30. It would seem that it is the more logical thing unless all comments are equal (ie a comment in table 1 needs to be joined with a comment in table 2 ..etc).

If you never want to analyse comments across the 15 base tables, then the clear advantage of option 1 is that since there are 15 tables, there is a lesser chance of it becoming a hot spot with respect to accesses and hits.

The other advantage is that its neater.

End