Use multiple database

5

I'm not very knowledgeable about database and have never worked with large systems ... I'm creating an application with CakePHP that will serve as a webservice for two types of distinct clients, a client is simple and will only have a table relationship, the another client is more complex and will have more data volume and table relationships (tables that do not relate to client 1 and vice versa) and the two clients will use different android applications.

I've always used 1 database for an application, and I noticed that systems like wordpress and joomla use, but I figured, since I have 2 different applications but with the same goal, would it be better to use two databases? (one for client1 and one for client2). By logic it would be better ... But what if I need to have a related table between the two types of clients?

I'm using MariaDB

    
asked by anonymous 21.04.2015 / 04:33

1 answer

6

When you have a single application and several different clients, it may be advantageous to have a single database serving everyone (as you claim to be the case with Wordpress and Joomla). This does not come without challenges, however - see my answer to this related question for a real example of how this might be done, and the care that needs to be taken for one client's data to not mingle with those of another. This article about multi-tenancy quotes other possible strategies, how to use the same application but different banks, or even separate instances of the application for each client (strategy that I used in a recent project myself).

Whether or not it is advantageous to maintain a single bank for all customers, from a performance or maintenance standpoint, is debatable. Personally, I believe that a single bank brings more agility to register and unsubscribe clients (just insert / delete records from a table, instead of creating / destroying entire banks), while multiple banks makes it simpler to do backups (you do not have to worry about data from one customer going on backups from another) and perhaps simplify scaling up (you can scale single-seat systems too - through "vertical" or " horizontal "- but I do not have enough practical experience to comment).

In their case, however, the fact that customers - and the application that will serve them - are substantially different from one another is almost the decisive argument for choosing to have separate banks; a complicating factor in multi-tenant architecture is just how to customize a customer's experience without breaking the system for everyone else (if the customer wants an extra field in the X-table, you can not simply put, because otherwise all the others would also have to receive that field). And in that case the factor is present from the beginning, because you know that will need different data structures (and code) ...

As for a client needing to reference the other in their database, I would say not to worry too much about this: firstly because this case is not foreseen, it is just a speculation "what if?", and how far I know this is not a common scenario; second, because even if it ends up needing it, the lack of referential integrity (ie not being able to create foreign keys from one bank to another, with a guarantee of integrity) should not be such a problem that it can not be addressed at its application layer .

    
21.04.2015 / 22:55