Hi guys
I’m developing a commercial SaaS site with a shared DB. However, I am participating problems with a shared DB and consider moving towards a separate DB for each tenant.
Reasons:
-
Commercial clients never want exactly the same system, simply because their business-processes are unique. Separate DB could make individual tweaks easier.
-
Commercial programs are not simple. They normally have many things that the user could do wrong. Restoring backups to a shared DB could influence innocent tenants. Trying to correct user mistakes in only certain records could be dangerous for other tenants if you are not careful - and normally you don’t have much time for testing, because the client’s business must go on.
-
Most tenants want their invoice numbers to be sequential. How do you do that in a shared DB? Auto-increment will not work, because tenants share the invoice table, meaning tenant-A will have invoices 1, 5, 13, 87, That will not work.
-
Separate DB’s could be much faster and scaling is a dream.
-
Some clients insists on their data being in a separate DB.
Negatives:
- Separate DB’s are more expensive. Really? Is this still a factor to mention? I mean hosting at world-class institutions now cost as low as US$2 per month per database.
Question:
So I want a main DB which contains all the tenants. Then I want to use the tenant_id as the name for the tenant’s own database. The connection string must therefore use the tenant_id (stored in session) to connect with the tenant’s own DB.
Any thoughts? Any hiccups?