Using one or multiple tables to hold comment data?

[tomcatexodus]

I'm working on a simple CMS/Blog system for Bands, and I'm stuck on a question of table design...

For example, I initially create the tables article, album, and photo for a band to store that data. If I want to add the ability to comment on these and any other items (like facebook provides) what is the best practice for creating the table(s) necessary?

Is it reasonable (possible) to make a single comment table to hold all comment data for all other tables, and if so how should I go about keying this to the parent tables?

Otherwise, should I resort to making multiple tables, article_comment, album_comment, etc., etc.

I just want to maintain extensibility and manageability, so that down the line if I add an audio, or whatever table, it can easily be integrated.

[jggtz]

You didn't say what is your database (SQL, Access, etc.) in order to know the field size limit
but you could use memo or maxchar fields to store the comments

[techgnome]

1 table, 3 fields minimum.
tblComments
parentKeyID int
parentType int
commentText varchar

the parent key would be the ID key of the item it is attached to (articleID or albumID or PhotoID).... the parent type would be an fkey to another table that holds the types (1 - Article; 2 - Album; 3 - Photo).... and the comment text is the comment.

OK, so if you want to get all of the album comments:
SELECT * FROM tblComments where ParentType = 2

To do a join from articles....
SELECT *
FROM articles A
LEFT JOIN tblComments C
ON A.articleID = c.ParentID
AND c.ParentType = 1

And so on....

-tg



EDIT -note: this is the way I would do it... I've used this in a couple of systems, it works pretty well... also if you then add other areas for comments, then you don't need to add another table... just add a new entry into the ParentType table.... Can also make searching all comments for something a lot easier (with one query rather than individual queries for each table created.)