...

View Full Version : One table or seperate tables



mrdemin
06-22-2011, 02:56 AM
A little unsure here.

I'm adding the ability to post user comments on images, blogs, articles, profiles and am wondering if its best to create ONE comment table and somehow tag what the comment belongs to, or if its just easier to create one table for EACH type of comment (art_comments, blog_comments, etc).

My gut tells me having a seperate one for each type is the way to go! Of course this rookie gut might be wrong as usual :o

PS currently I have set up seperate tables, and it works just fine, but I was thinking of maybe using an addComment class in PHP vs having addImgComment/addBlogComment/etc functions, and just pass a parameter to it specifying what the comment is meant for.

Double PS, this is on an offline site, just a personal task to learn some... And when done make it live :rolleyes:

Old Pedant
06-22-2011, 06:02 AM
Recent response to same question:
http://www.codingforums.com/showthread.php?t=229114

mrdemin
06-22-2011, 06:19 AM
Hmm I should've went through an extra page of topics I guess haha.

You mention there should be no bottleneck with the single table design, looking ahead I have to ask (being that its ratings in my case, and will get pretty heavy), are you suggesting there will be no real slowdown even if I will be counting, and averaging rows with a WHERE clause (to specify what I'm counting/averaging a rating for)?

Old Pedant
06-22-2011, 08:13 PM
It's hard to say there will be *NO* slowdown, but if you add an index onto the field(s) that your WHERE clause is based on, the slowdown will be minimal.

There's no way to give a definitive answer for all situations, so treat what was written as general guidelines. When in doubt, learn to use MySQLs EXPLAIN feature which will help find bottlenecks, if they exist.

kunz
08-18-2011, 11:51 AM
Hmm I should've went through an extra page of topics I guess haha.

You mention there should be no bottleneck with the single table design, looking ahead I have to ask (being that its ratings in my case, and will get pretty heavy), are you suggesting there will be no real slowdown even if I will be counting, and averaging rows with a WHERE clause (to specify what I'm counting/averaging a rating for)?

There definitely will be a slowdown if you are counting and averaging rows. As suggested earlier, indexing definitely helps a fair bit.

I recently worked on a really large database (several thousands of queries / minute). The database had no indexes set up (or very messy indexes) and after creating "smart" indexes, we managed to reduce their server load on SQL by up to 30%. Query times also decreased.

Just goes to show that indexing really helps significantly.

Old Pedant
08-18-2011, 08:27 PM
I'm surprised it was only 30%. I would have expected more like 90% to 95% in typical situations. Yours must have been really complicated.

kunz
08-20-2011, 01:55 AM
I'm surprised it was only 30%. I would have expected more like 90% to 95% in typical situations. Yours must have been really complicated.

They already had indexes setup so their database wasn't absolutely horrible - it was just extremely messy.

30% was a great improvement over the previous 'optimization' they paid for. We are going to restructure their entire db in the coming months. It's basically a company that grew too quickly and whoever made their website and did their database structures didn't care about scalability.

Oh well :) should be fun.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum