7

So I have this application that would have multiple modules, from project management to accounting modules. The question is should I have one database per client (company) or one database that holds everything ?

1) which one would be better performance wise?
2) is is going to be a lot harder to manage multiple databases or is these manageable.
3) We are going to have the same application for all users, meaning that the same schema is going to be used no matter the number of databases.
4) some clients are going to have a lot of that (accountants for example might have up to 2 million row added per year in one table) while others are going to use much much fewer data.

what do you think I should use?

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
redmoon7777
  • 4,498
  • 1
  • 24
  • 26
  • possible duplicate of [Multiple schemas versus enormous tables](http://stackoverflow.com/questions/8340721/multiple-schemas-versus-enormous-tables) [This answer](http://stackoverflow.com/a/8343142/562459) provides a good overview of the issues. – Mike Sherrill 'Cat Recall' Jan 02 '12 at 20:06

3 Answers3

6

1) Having separate databases allows for easier distribution of load on several hosts, it lifts the roof in many ways; disk, memory, locking, cpu, backup-time and so on. If you are serious about putting millions of rows in mysql, it is certainly a good idea with separate databases (not only schemas), and even separate instances, so that the resource-consuming customers won't impose downtime on less resource consuming ones.

2) It is going to be exactly N times harder to manage where N is the number of databases :o) This extra cost you must compare to the cost of using just one db/schema and instead manage separation of customer in code. It's also inherently much harder to manage if you have to call customer support at your hosting company, or even your local grumpy dba, instead of just running a neat script from your console each time you need to update schema or create a new database.

Some databases and persistence frameworks have support for multi-tenancy, Oracle has this and support is beginning to emerge in Hibernate 4.

Even though many arguments point in the direction of separate databases, it is generally possible to use just one database as well.

Jon Martin Solaas
  • 737
  • 1
  • 10
  • 19
  • 1) yes we already have some users on an older application who are creating about 2m rows a year (accountants especially). 2) the problem with this is how do I ensure that all databases were updated when I run the script ? – redmoon7777 Jan 02 '12 at 20:12
  • @redmoon7777 well, that is entirely up to you, just as it would be up to you to guarantee data separation in one multitenancy databse. – Jon Martin Solaas Jan 02 '12 at 20:22
  • you are right. I've been looking around for days I still can't find the "right" answer. Right now I'm leaning toward multiple databases. – redmoon7777 Jan 02 '12 at 20:24
  • @redmoon7777 as long as you do not depend on others to manage the databases, at least it won't be wrong doing it that way. but it might be a little overkill, your mileage may vary ... – Jon Martin Solaas Jan 02 '12 at 20:38
3

In theory, multiple databases will be better for performance. That is, if you could put them on separate disk controllers. But in actuality they will most probably all be on the same disk, so there will probably be no performance gain. Plus, additional disks are better utilized as additional members of RAID arrays than as additional separate logical disks to which you can offload data.

From the point of view of maintenance, multiple databases are going to be a nightmare. Every ALTERation to the database will need to be made N times, where N is the number of clients. Of course you will never do that by hand, so you will always have to do it programmatically, and pretty soon you will start appreciating how easy things would be if you could just do the same ALTERations with a few clicks on the management console instead of having to write code to do them for you every time.

Mike Nakis
  • 56,297
  • 11
  • 110
  • 142
  • you are right, but is the databases management the only issue with using multiple databases ? – redmoon7777 Jan 02 '12 at 19:40
  • Well, it is not very convenient to have multiple databases if you are planning to be executing any cross-database queries, but I think that if it really has to be done, it can be done, so it is not a show-stopper. – Mike Nakis Jan 02 '12 at 19:47
  • true, that would affect the script to gather data on all clients at once. – redmoon7777 Jan 02 '12 at 20:04
  • Right, but that's probably the least of your concerns. – Mike Nakis Jan 02 '12 at 20:11
  • generally you *want* to manage alterations programmatically (sql scripts), even if there is only one schema. version control is one reason, better control is another. – Jon Martin Solaas Jan 02 '12 at 20:13
0

security will be a real bear if you don't use separate databases, regardless of any other concerns. you would have to be extremely cautious with coding to make sure one company doesn't see (or modify or delete) the others' data.

jcomeau_ictx
  • 37,688
  • 6
  • 92
  • 107
  • As long as the data base sits behind a set of scripts and is not exposed directly to clients, it's not all that hard to ensure that data is secure. – Ted Hopp Jan 02 '12 at 19:37
  • I'd rather have some other mechanism enforcing access to data besides trusting in code, especially when using hired coders of dubious proficiency. – jcomeau_ictx Jan 02 '12 at 19:41
  • redmoon7777, I don't have data on relative performance with the two approaches. I'm just saying that this one consideration could trump any other. – jcomeau_ictx Jan 02 '12 at 19:44
  • I agree, so you think (added) security wins over multiple databases maintenance ? – redmoon7777 Jan 02 '12 at 19:57
  • yes, as MikeNakis pointed out, you can script modification to the database. nothing will save you if a programming error compromises your customers' data. – jcomeau_ictx Jan 02 '12 at 20:02
  • @jcomeau_ictx that is correct, but you already have billions of ways to compromise data, putting all customers in one database just adds up to a billion and one ways. It is more complex, but it's not an entirely different ballpark. A wisely structured database layer will minimize the problem. – Jon Martin Solaas Jan 02 '12 at 20:17
  • @jcomeau_ictx if applications access the db thru a data layer, which has test cases covering the access layer methods to make sure they are working as intended, would alleviate that worry. But again, if developers are not writing simple test cases such as this, then that is a recipe for disaster. – Abhi Sep 11 '20 at 17:24