One database or many?

Question

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:

• database for the entire site (entity has a "customerID", data has "entityID")
• database for each customer (data has "entityID")
• database for each entity (relation of database to customer is outside of database)

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

Answer 1

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.

Answer 2

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.