CODEWITHSUNDEEP
mysqldatabasedatabase-design
Damiano Barbati
Damiano Barbati

Reputation: 3496

Dedicated database for each user vs single database for every user

I'll be soon developing a big cms where users can configure their website managing news, products, services and much more about their company. Think about a shopify without the ecommerce part (at least for now). The rdbms is MySQL and the user base will be about 150 (maybe bigger).

I'm trying to figure out which one of these two approaches would fit better.

DEDICATED DATABASE FOR EACH USER

PROS:

SINGLE DATABASE FOR EVERY USER

PROS:

Which way would you go for? I don't think shopify created a dedicated database for any user registered... or maybe they did?

I'd like more experienced people than me to help me figuring out the best way and all the variables I can not guess right now because of my ignorance.

Upvotes: 0

Views: 412

Answers (1)

O. Jones
O. Jones

Reputation: 108641

It sounds like you're developing a software-as-a-service hosted system, rather than a software package to distribute to customers for them to run on their own servers. In that case, in general, you will have an easier time developing and administering your service if you design it for a single database handling multiple users.

You'll be able to add new users to your system with data manipulation language (DML) rather than data definition language (DDL). That is, you'll insert rows for new users rather than create tables. That will make your life a LOT easier when you go live.

You are absolutely right that stuff like backups and aggregate reporting will be far easier if you have a single shared database.

Don't worry too much about the user data export functions. You'll have to develop software for those functions anyway; it won't be that hard to filter by user when you do the export.

But there's a downside you should consider to the single-database approach: if part of your requirement is to conceal various users' existence or data from each other, you'll have to be very careful to do this in your development. Will your users be competitors with each other? That could be tricky. You'll need to trust your in-house admin and support teams to refrain from disclosing one user's data to another by mistake (or deliberately). With a separate database per user, you'll have a smaller risk in that area.

150 users aren't many. Don't worry about scalability until you have a workload of paying customers. When that happens you can add MySQL server RAM, partitions, solid-state disks, replication, memcached, sharding, and all that other expensive and high-workload stuff. If you add those things before you go live, you'll just take longer and blow more money before you go live. Not good.

Upvotes: 2

Related Questions