How to Design a SaaS Database

database database-design database-schema
Josh Curren picture Josh Curren · Feb 4, 2010 · Viewed 14.6k times · Source

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?

Answer

Don Dickinson picture Don Dickinson · Feb 4, 2010

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

Related questions

What are OLTP and OLAP. What is the difference between them?

Actually what do they mean? All articles I find about them don't give me an idea, or my knowledge is too insufficient to understand it. Will some one give me some resources with which I can learn this from scratch.

Read More
Is it possible to create a column with a UNIX_TIMESTAMP default in MySQL?

I'm trying to do this, but it seems like MySQL isn't allowing me. Is there a solution to this issue or am I expected to always include the function in my INSERT queries? CREATE TABLE foo( created INT NOT NULL …

Read More
2 relationships between 2 entities in ER diagram

I'm trying to draw an ER diagram describing the following: -"Department" employs "Employees" -Some "Employees" are "Special" and have more attributes -Some of the "Employees" ("Special" and non special) are "Managers" -"Managers" manage "Departments" So, to convey this …

Read More