MYSQl Optimize Table Of Blog Posts With Comments

Question

Hi,

Im making a mysql table which will be holding my blog posts and some of their information. I want to be able to add comments the the blog posts and have them saved in mysql. My question is weather I should make a table called comments and have all the comments there have the blog post id, so I would only select the corresponding comments like this:

select from comments where id = blogpostid

Or my other idea was to put all the comments in an array, and save them in a longtext field in my blog posts table, so each row(blog post) will contain its comments.

I will accept any other ideas, but for me speed comes first.

Answer 1

Having a nicely normalized, separate comments table is the right thing.

With the proper indexes, it will work fine. I doubt whether there'll ever be the kind of traffic that would justify de-normalizing the structure for speed.

Answer 2

Please read about database normalization, for example (http://www.devshed.com/c/a/MySQL/An-Introduction-to-Database-Normalization/)

It's true that denormalization (adding redundant data or group it) can improve the performance of your database, but it's also increases the size of your database.
Data integrity should be your primary concern, when it's comes to database acting as a blog back-end.

You should keep your blog comments table separate with a blog post unique-id as a primary key, unless you need something more complicated (like Polymorphic Associations, etc)