MySQL – Isolating the Database in Multiple Instances

database-designMySQL

I read on a forum about the use of database isolated environments are neccessary for any large project that handles "big" data. For instance I will be using the following table for ALL blogs:

projectname_blog_blogposts

We all know that the table will be very big. Probably hard to maintain, maybe awful situation if the table is corrupt or something like that. I don't know, I don't have any experience to confirm such a statement.

So instead of doing that. Is this approach better:

projectname_blog_1_blogposts
projectname_blog_2_blogposts
projectname_blog_3_blogposts

And then split them up like this:

DATABASE01

projectname_blog_1_blogposts

DATABASE02

projectname_blog_2_blogposts

DATABASE03

projectname_blog_3_blogposts

Sorry if the question is fuzzy. But I do hope you guys understand me.

Where can I read more about this so I can make a good choice?

Best Answer

the problem you are facing can be likened to the problem of multitenancy as you are talking about multiple applications (multiple blog) and one or more tables, schemas, databases, instances. A good starting point can be the following article from MSDN: Multi-Tenant Data Architecture. The article is focused on Microsoft SQL Server but it can be easily generalized.

Regards Giovanni

Related Question