I have a web app that I built for a trucking company that I would like to offer as SaaS. What is the best way to design the database?
Should I create a new database for each company? Or should I use one database with tables that have a prefix of the company name? Or should I Use one database with one of each table and just add a company id field to the tables? Or is there some other way to do it?
faced with a similar situation about 10 years ago, we opted for a database per client. we have hundreds (not thousands) of clients. looking back it was one of the best decisions we made. backups are easy. copying a single client to our office for analysis is easy (just take the last backup). scaling is easy (moving a single big client to a different server can free up resources on a stressed sql server). joel & jeff had a discussion about this on a stack overflow podcast (not a recent one) and joel did the same thing i do ... each client gets their own database. database purists will often argue for lumping everyone into one db, but i would never do that.
-don
It depends, here, i work in a company that has many "Internal Business units" treated like other companies. So, some reports must include all companies, Client accounts must also be shared across companies. Here we have a CompanyId Field in the tables that requires it. The Prefix solution is surely one to be avoided.
https://stackoverflow.fogbugz.com/default.asp?W24218 (podcast transcript, discussion around 50 mins in)
We have some datbases here with shared clients and some where each client has it's own server and own database. The ones where the client is on it's own server are the easiest to manage and the least likely to cause a problem when some developer forgot to add the clientid and sent client a's data to client b by accident (an example NOT chosen at random).
Keeping each on it's own server or server instance allows us to keep the database structre the same with the same names and makes it easier to propagate changes to all the servers becasue we don't have to change the database name.
If you do use separate instances for each client, make sure you design and implement a good system for propagating all changes to all clients. If these datbases get out of synch, they can become horrible to maintain. You'll find that if you let them get out of synch, each client will ask for changes and you will have 27 ways to do the same thing. You have to generalize when they are on the same database, when they are separate you have to use self discipline to ensure new functionality is the same for each client.
We have designed a solution to get the Oracle DB to support multi-tenancy. Even your existing Oracle db based applications can be made to support multi-tenancy out of the box (instead of hosting a separate instance of the db and applications on a separate machine). A particular customer transactions will be stored in a separate set of table spaces and based on the userid, security will be restricted to that set of table spaces and the data in them. We achieve this using Oracle's native partitioning and row-level security. If anyone is interested in this please contact me.