Best practices regarding database design for multiple comments areas

[eluser]Jonas G[/eluser]
Hi

I am designing a database for use with a new site. It is possible to comment on both 'news' and 'blog entries'. My question is regarding the best way to do this. I see 2 possible ways to do it:

1. Have a 'comments' table with a field named 'area' that can be either 'blog' or 'news'
2. Have 2 tables: 'blog_comments' and a 'news_comments'.

I should say that my site will probably grow to having comments on more than those two areas. Still, solution 1 doesn't appeal to me.

How do you clever folks do it?

[eluser]Wuushu[/eluser]
#1

That's how i've done in previous projects, or very close to that way.. works perfectly fine.

[eluser]jedd[/eluser]

Still, solution 1 doesn't appeal to me.

What aspect(s) of it do you not like?

You suggest you might have more areas on the site, later, that will take comments - would you anticipate putting new tables in for each of those?

My thinking (and I have not done something quite like this) is that you would keep the data abstract here, and go with option 1. This works so long as the nature of your blog and news comments are going to be the same (and my guess is that they will - they'll include common attributes such as created_by, created_when, etc). An enum or even just a smallint column would provide the key to whether it's a news or blog article it points to and obviously an ext_id that points to the news or blog item proper.

[eluser]Jonas G[/eluser]

An enum or even just a smallint column would provide the key to whether it's a news or blog article it points to and obviously an ext_id that points to the news or blog item proper.

I hadn't thought about using enum or small int (actually i haven't worked with enum before). I would probably have used a tinytext field but I like your solution better. I mean, the thing i didn't like about my #1 way of doing it was comparing strings when matching the comment area.

[eluser]jedd[/eluser]
Full disclosure - never used enums, and I feel about them much the same way as I do about stored procedures. (Not terribly fond, in case you were about to ask

They're nice here, as they force some integrity into your database, but I think you'd get the same by having a smallint as a required FK entry into your 'comment_type' table, consisting of two fields - ID and TYPE. The latter could refer to a database table, but not sure how ugly that might get.

I think MySQL 5 can force an FK to exist. I was reading something about this recently .. somewhere.

Enums mean you'd need to do some database modifications when adding new commentable stuff - but I suppose you're resolved to altering your DB at that point anyway.