0

So I'm working on this blog creation website as a side project. I don't actually expect it to become anything, but I want to make it as efficient as possible, even if just for practice.

The idea is that people can make accounts and then make as many blogs as they want, and post whatever they want on those blogs. My question is that should all of those blog posts be stored on the same table, or should I be creating separate tables for each new blog created? I don't know if there are limits or downsides to having hundreds or thousands of different tables, but I know that having everything on one table will slow down the selecting of posts for one specific blog, for example. This issue also applies to comments; are all comments on the site stored in one place, with an ID of what post they're about, or are they stored on separate tables depending on what blog they're on or something else. Or is there some other, better way that I should be using(without having to program a new database structure from scratch)?

I'm using PHP/MySQL for the backend. Any ideas would be much appreciated!

Improve this question
5
  • You should probably have a database per blog. Then you don't have to dynamically specify the table name (what are your tables going to be called, blogposts00001, blogposts00002, etc?), and you don't have to worry about the scalability of blowing up a single table. The nice thing about a database per blog is when one blog posts a ton of content it's easier to move a single database to a different server or storage than to move a singe table or some subset of a table.
    – Aaron Bertrand
    Commented May 23, 2015 at 18:26
  • Hmm. It sounds a bit complicated to be honest. I'll look into it though. Thanks!
    – Kurt
    Commented May 23, 2015 at 18:32
  • What's complicated? You have the same code running in the context of each database, or the code has to know the context of which table to use. Something in there is going to be "complicated" as soon as you go from one blog to two.
    – Aaron Bertrand
    Commented May 23, 2015 at 18:41
  • I meant the creation of database for every blog. I'll end up with thousands of different databases, won't I?
    – Kurt
    Commented May 23, 2015 at 19:15
  • Or thousands of different tables, why is the latter less complicated than the former?
    – Aaron Bertrand
    Commented May 23, 2015 at 19:44

1 Answer 1

Reset to default
1

More than a few thousand tables or databases is a bad idea in MySQL. This because of MySQL's dependence on the filesystem, and most OS's can get bogged down when a directory has "too many" entries.

Hence, I vote for a single Blogs table. You will probably need few other tables to round out the app. Probably they will all be in a single database, but that does not matter much.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.