CODEWITHSUNDEEP
performancedatabase-designmulti-tenantmaintainability
dsims
dsims

Reputation: 1322

One database or many?

I am developing a website that will manage data for multiple entities. No data is shared between entities, but they may be owned by the same customer. A customer may want to manage all their entities from a single "dashboard". So should I have one database for everything, or keep the data seperated into individual databases? Is there a best-practice? What are the positives/negatives for having a:

Multiple databases seems like it would have better performance (fewer rows and joins) but may eventually become a maintenance nightmare.

Upvotes: 9

Views: 1856

Answers (11)

iDevlop
iDevlop

Reputation: 25252

The single database option would make the maintenance much easier.

Upvotes: 0

MathGladiator
MathGladiator

Reputation: 1211

Do you plan to have your code deployed to multiple environments?

If so, then try to keep it within one database and have all table references prefixed with a namespace from a configuration file.

Upvotes: 0

Ian Ringrose
Ian Ringrose

Reputation: 51897

It also depends on your RDBMS e.g.

With SQL server databases are cheep

With Oracle it is easy to partition tables by customer "customerID", so a single large database can run as fast as a small database for each customer.

However witch every you choose, try to hide it as a low level in your data access code

Upvotes: 0

Aaron Fischer
Aaron Fischer

Reputation: 21211

Check out this article on Microsoft's site. I think it does a nice job of laying out the different costs and benefits associated with Multi-Tenant designs. Also look at the Multi tenancy article on wikipedeia. There are many trade offs and your best match greatly depends on what type of product you are developing.

Upvotes: 1

Vinnie
Vinnie

Reputation: 12720

Personally, I prefer separate databases, specifically a database for each entity. I like this approach for the following reasons:

  1. Smaller = faster regarding the queries.
  2. Queries are simpler.
  3. No risk of ever accidentally displaying one customer's data to another.
  4. One database could pose a performance bottleneck as it gets large (# of entities increase). You get a sort of build in horizontal scalability with 1 per entity.
  5. Easy data clean up as customers or entities are removed.

Sure it'll take more time to upgrade the schema, but in my experience modifications are fairly uncommon once you deploy and additions are trivial.

Upvotes: 5

JasonS
JasonS

Reputation: 23868

I think this is hard to answer without more information.

I lean on the side of one database. Properly coded business objects should prevent you from forgetting clientId in your queries.

The type of database you are using and how it scales might help you make your decision.

For schema changes down the road, it seems one database would be easier from a maintenance perspective - you have one place to make them.

Upvotes: 2

Tundey
Tundey

Reputation: 2965

I think you have to go with the most realistic scenario and not necessarily what a customer "may" want to do in the future. If you are going to market that feature (i.e. seeing all your entities in one dashboard), then you have to either find a solution (maybe have the dashboard pull from multiple databases) or use a single database for the whole app.

IMHO, having the data for multiple clients in the same database just seems like a bad idea to me. You'll have to remember to always filter your queries by clientID.

Upvotes: 0

Chris Miller
Chris Miller

Reputation: 4899

If the client would ever need to restore only a single entity from a backup and leave the others in their current state, then the maintenance will be much easier if each entity is in a separate database. if they can be backed up and restored together, then it may be easier to maintain the entities as a single database.

Upvotes: 0

Vaibhav
Vaibhav

Reputation: 11436

One good argument for keeping them in separate databases is that its easier to scale (you can simply have multiple installations of the server, with the client databases distributed across the servers).

Another argument is that once you are logged in, you don't need to add an extra where check (for client ID) in each of your queries.

So, a master DB backed by multiple DBs for each client may be a better approach,

Upvotes: 0

Vaibhav
Vaibhav

Reputation: 11436

This is a fairly normal scenario in multi-tenant SAAS applications. Both approaches have their pros and cons. Search on best practices for multi-tenant SAAS (software as a service) and you will find tons of stuff to ponder upon.

Upvotes: 1

Lasse V. Karlsen
Lasse V. Karlsen

Reputation: 391276

What about backup and restore? Could you experience a customer wanting to restore a backup for one of their entities?

Upvotes: 1

Related Questions