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?


SAS? Software as a Service?

Written by S.Lott

What you've stated is too vague. What does 'Company' represent in your application?

Written by Randy Minder

yes Software as a Service

Written by Josh Curren

@Randy - A company is a group of users who have access to the same data. So Trucking Company A may have 5 users who access their data. Trucking Company B may have 20 users accessing a completely different set of data. Does that help?

Written by Josh Curren

The more common abbreviation is "SaaS". Normally, SAS refers to SAS, inc. sas.com. It's not clear if you're talking about the company or the services offering idea. You might want to fix your question.

Written by S.Lott

ok. I will do that. Thanks.

Written by Josh Curren

Accepted Answer

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.


Written by Don Dickinson
This page was build to provide you fast access to the question and the direct accepted answer.
The content is written by members of the stackoverflow.com community.
It is licensed under cc-wiki