We are working on a project in which there will be multiple online shops, each "owned" and managed by different clients. The shops will be similar in nature (mostly identical) but each containing its own, unique data. Data would not, in principle, be shared by different clients.
At this point we are at the database / application design phase. We are debating the following essential question: Should we design one huge comprehensive database to store everyone's data or should we set up the application to create a new instance of the database model for each individual client?
Some pro's and con's to each...
One Big Database
- Only one database to backup
- simplifies application design in that queries to the database are always the same, using "ShopID" to differentiate between clients
- modifications need only be made once
- Security: we'll try to be secure, of course, but what if we get hacked? ALL data for ALL shops is at risk.
- Customization: What if a client needs some sort of customization? It could become very complex trying to add features for a specific client.
- Inital Design will be far more complex and bug-prone
Lots of distinct, identical databases
- Easy to modify each database to fit specific client needs
- compartmentalized data for each client
- Security: if one db gets hacked, it's only ONE, a lot more manageable than the entire system being compromised
- Making a global change to the application / db model would mean updating potentially hundreds or thousands of distinct databases, which sounds horrible in itself, but could also give way to naming or other conflicts arising in some cases
- Would have to backup and manage hundreds of databases
- DB user management becomes much more complex?
- Might be heavier server load?
We have had a fair bit of DB design experience for individual shops but have never had to do something this large. We'd appreciate your comments and discussion. Thanks in advance!