One database or many?
database-design, maintainability, multi-tenant, performance
Solution
Personally, I prefer separate databases, specifically a database for each entity. I like this approach for the following reasons:
- Smaller = faster regarding the queries.
- Queries are simpler.
- No risk of ever accidentally displaying one customer's data to another.
- 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.
- 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.
Problem
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.