Hello,

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
PROS

• 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

CONS

• 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
PROS

• 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

CONS

• 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!

I found this thread which is very interesting, and seems to touch on exactly this topic:

http://discuss.joelonsoftware.com/de...gn.4.319460.16

If your company is going to be responsible for the administration of these databases, then one big one will be vastly easier to manage - I would not consider the alternative of 100s of similar databases. The major DBMSs should have facilities to help with the security side of things; for example, in Oracle there is "Virtual Private Database" (VPD) to ensure that each customer can only access their own data.

I would agree with andrew on this. Each client would probably be given access to named views of the data, and not direct access to the underlying tables.

One Big Database

Surely you just add the new feature into the feature set for the whole system. You get the client who wants the feature to pay for the development then once the client has used the feature for a while you release the feature as a optional feature to other clients that they have to pay for. Eventually it becomes a standard feature for all clients. If the idea doesn't suit a global deployment then you just say sorry we can't do it.

Why? you're just adding a client_id to the tables or you're using the views. I assume you must believe having having 100's of separate developments going on for different clients is going to be easier?

Lots of distinct, identical databases

The idea of having 1000's of nearlly identical databases where each one has something subtly different gives me the shivers. I assume you have 1000's of staff to work on these different databases.

Greg

The following link may be useful for you (it was for our situation)
http://msdn.microsoft.com/en-us/library/aa479086.aspx

Mike, sadly this is not always possible. For some industries (e-learning in our case) client requirements can vary greatly. The client still pays for the customisations, but these need to be forever isolated from all other customers.

I imagine Greg was considering the client customsations when making this comment.

Not all systems require that scale of databases, but we have the same concerns. I imagine this could work with carefully managed schema (for deployments and upgrades) and corresponding application metadata.

We are currently researching this issue. We have already decided to allocate a separate database for each client. Our current investigation is to determine how extensions will best suit our requirements. At this stage, extending the schema with client specific tables seems to be the approach we will take and our task is determine how best to manage this.

kevan, I have no idea about your requirements but I was posting in reply to Greg

Quote:

Originally Posted by gregrobbins The shops will be similar in nature (mostly identical) but each containing its own, unique data.

Again it was more in response to Gregs original request...

Quote:

Originally Posted by gregrobbins db model would mean updating potentially hundreds or thousands of distinct databases

Mike