[ACCEPTED]-Building a web application with multiple database instances or just a single instance-web-applications

Accepted answer
Score: 29

You have to weigh up your options, as some 72 of this will be a matter of opinion and 71 might not be feasible for your implementation.

That 70 being said, I'd consider the single database 69 approach, for these reasons:

  1. Maintenance: when running 68 a database per registered 'client', you 67 will very easily reach a situation where 66 any changes or upgrades you make to your 65 app's schema have to be applied to every 64 single database instance. This will get 63 ridiculous, fast.

  2. Convenience: You might want analytics 62 and usage stats, or some way to administrate 61 all these databases. Querying a single database 60 is comparatively trivial to trying to aggregate 59 the same query for all your databases. This 58 isn't going to scale.

  3. Scalability *: As mentioned in 57 2, you're going to require a special sort 56 of aggregation to query things about your 55 clients, and your app as a whole. The bigger 54 your app gets, the more complex your querying. The 53 other issue is, if one client uses the app 52 a lot more than another, what will you be 51 encouraged to optimise? Your app, the bigger 50 client's database, or the smaller client's? Not 49 forgetting anything you do change has to 48 be copied to all databases.

  4. Backups: You can backup 47 one database easily, just by creating a 46 dump and stashing it somewhere. Get a thousand 45 clients and now you have to run 1000 database 44 dumps, and name them well enough to be able 43 to identify them if one single database 42 corrupts. How will you even know if this 41 happens? Database errors will be localised 40 to that specific one, as opposed to your 39 entire app.

  5. UI: A user signs up or is invited 38 to use your app, and belongs to one particular 37 client. Are you going to save that user 36 account to the client's database? If so, see 35 scalability for the issue of working with 34 that data when the user wants to change 33 their password, or you want to email them. So, do 32 you tell the user to let you know which 31 database they're in so you can find them?

  6. Simplification: You 30 have a database per client and want to just 29 use a single one. How do you merge them 28 all together without significantly breaking 27 things? There'll be primary key conflicts 26 if you use auto incremented IDs; bookmarked 25 URLs will break if you decide to just regenerate 24 the keys; foreign keys across tables will 23 no longer point to the right records. Your 22 data integrity will go down the pan.

You 21 mention 'white label' services that offer 20 their product through custom subdomains. I'm 19 not privy to how these work, but the subdomain 18 is only a basic CNAME or A record in their 17 DNS zonefile. The process of adding these 16 can be automated, and the design of the 15 application and a bit of server configuration 14 can deal with linking these subdomains to 13 the correct accounts and data. They're just 12 URLs, so maybe on the backend, the app doesn't 11 differentiate between:

http://client.example.com
http://example.com/client

Overall though, you 10 may decide that all these problems are things 9 you can and would prefer to deal with. Be 8 warned, however, that by doing so you may 7 be shooting yourself in the foot, and you 6 can gain a lot more from crafting a well-designed 5 single database schema and a well-abstracted 4 front-end.

*@xQbert mentions the very real 3 benefit of scalability with multiple databases. I've 2 amended this answer to clarify that I was 1 more concerned with other aspects.

More Related questions