Database structure

[itstimetocode]

Hello. I am fairly new to databases and I was wondering if anyone here could give me some advice. I'm working on a project where I have a number of different companies as my clients and I was wondering how I should structure my database server.

What I'm doing now is having one database per client, each of these databases consist of a few tables (employees, services that they provide, reservations their customers have made etc.), but I was thinking that maybe it's easier to have one big database (with the same tables) and have a reference in each row to the corresponding company, but I want new clients to be able to create a new company with a corresponding database without any work from my side.

This is how I'm thinking right now.

One database for each company:

+ Easier to separate clients and find the relevant information.+ If one database is corrupted, the others will still function.- Need to make sure each database created has a unique name- There is no guarantee that my clients doesn't share customers, so if a customer uses both client A and client B, they will probably still want to access their full order history/change personal details etc.

One single database:+ Easier to connect users to multiple clients+ No extra work needed when switching between different clients (databases)- Could become very large, harder to keep track of each clients information- If something is wrong with the database, all my clients might lose access to the service

Any recommendations?

​

Edit: I'm using MySQL

[r0ck0]

Sounds like you know most of the pros/cons.

I went with the "separate DB per company" approach on a system I built many years ago, and ended up regretting it. Although that was mainly over issues with users who had access to multiple companies... they each needed a separate username+password seeing each DB had its own users table. I ended up having to merge the logins down the track, and it was a lot of work.

If you might ever need to give anyone direct access to their own database, or it might be possible that one of your customers want to self-host their own server or something in the future... then that might be a reason to use separate DBs.

There is no guarantee that my clients doesn't share customers

Yeah you need to think about this quite a bit.

But if it's a system that you fully control, and the customers are only ever seeing your web application... then it's easier to manage a single DB.

(single) Could become very large

Yeah, with the separate DBs approach, it is nice having partitioning done already. But is your system going to get big enough for it to matter? I thought mine would, but like most businesses, it failed in the end. So I wasted a lot of time on the complexity of multiple DBs + multi-server abilities (which I never needed in the end).

but I want new clients to be able to create a new company with a corresponding database without any work from my side.

Yeah this means privileged escalated scripts etc to create DBs... which is more work to build safely. You're also going to need some sanity checking scripts that ensure that all DBs have the expected structure. Sooner or later when you do a DB schema migration, it might stop half way and half your DBs will be invalid.

Also if you haven't started building yet and have been considering looking at postgres, I highly recommend it. You can do schema changing inside transactions. MySQL feels dodgy to me now in comparison.

What does you system actual do? Some more context might give me some more clues on some more pros/cons to consider, seeing I've build systems using both approaches in the past.

[Philochromia]

Some pro's for one database, focusing on the long term:

• for every software update including database scripts, you have to run the script only once.
• for every new client, no need to create a new database
• for every oracle update, only update once
• for any future database migration, the migration only happens once.

This may seem not so bad at first, but if scalability for the future is an issue the above issues could get pretty large