0

I have a few blogs/websites hosted on my vps. Recently I've decided to move a db on a single central server/vps because all the blogs have the same structure in terms of code and structure of the db.

The problem is, how can I effortlessly start using "application_id" in my db? It will denote a website data belongs to.

Say, I have these tables:

   -- users
   -- articles
   -- categories
   -- tags

and others. Now I'll have to insert one additional column in each table: "application_id"

Not a big deal. I think. But perhaps there's a simple and more smart way to archieve the same goal?

And in general, is it a good idea to move it to a central, single vps/server/db?

  • Your database engine may have support for this (I'm thinking of PostreSQL's namespaces). Your blogging engine may also need some tweaking and/or configuration. Anyway what you are contemplating is multi-tenancy. It has pros and cons, and there exist multiple schemes, so you should evaluate them wisely. – user44761 Nov 1 '17 at 9:51
  • What you are looking for is named multitenancy. Have you read about this subject before? There are several ways to implement multitenancy. Don't reinvent the wheel. I would suggest to read before about this subject in order to choose the approach that best meet your needs. – Laiv Dec 3 '17 at 21:55
  • Is there anything unique about each blog’s categories, tags, articles and or users? For example, does each blog only handle certain categories? – tale852150 Dec 31 '17 at 15:50
1

I wouldn't mix up the databases of different websites. There are several reasons:

  • You probably don't want the blogs to be able to access the content of another blog.
  • Even if you are the author of all of the blogs, so the above reason isn't important for you, it could still happen that an error occurs and there are articles, categories or even users mixed up more easily.
  • If you have lots of data in your database it could be less performant storing all your data in one single database.
  • Imagine you want to move one blog to another server in a few years. Then it would be much easier to do so if the data is separated from the data of the other blogs.

If you need to set up the same database structure many times, I would suggest writing a setup script or something else that simplifies the database creation rather than putting them all together.

| improve this answer | |
  • Imagine you want to move one blog to another server in a few years. Then it would be much easier to do so if the data is separated from the data of the other blogs. -- why? it's just a matter of adding "where app_id = N" – Radiz Nov 1 '17 at 12:38
  • "If you have lots of data in your database it could be less performant storing all your data in one single database." Rubbish. proper indexing and/or partitioning will cope with the vast majority of scenarios. "...move one blog to another server ... much easier to do so if the data is separated ..." Not so. In either case, you're just moving data from one set of tables to a new set of tables. – Phill W. Nov 1 '17 at 16:41
0

But perhaps there's a simple and more smart way to achieve the same goal?

Unless you can find a unique way to define each blogs data within their existing database structure -- for example, each blog exclusively contains a set of categories -- then the best approach would be to add an application_id (or blog_id) to that single database you want to load/create. That is, if can find a unique "key", then use that "key" to distinguish which data in your single database belongs to which blog.

You could build views on top of your single database with each set of views associated with each blog. Of course, you would still need some "key" so that your views represent data only for that blog.

While (probably) as (or more) painful than adding an application_id, you could also have a set of tables for each blog with the table names defining which blog they are associated with, such as blog1_categories, blog2_categories, blog1_users, blog2_users ... I don't recommend this but it is an option if you want to keep the blog data separated but in one database.

And in general, is it a good idea to move it to a central, single vps/server/db?

The pros and cons -- performance, security, etc. -- are fairly addressed by @Gregor , @Phill W and yourself in one of the answers and comments already mentioned.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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