Looking for help with database structure for SAAS

[DeX]

I have a quoting tool being used by a few companies and would like to turn it into a Software As A Service (SAAS) for the new redesign. I'm wondering what is the best way to go about this so I have some questions:

 

1. Should I be using a completely separate database for each new customer? How would I create a new MySQL database and tables in PHP? Just run the full SQL script in one shot after they have paid?

 

2. Should I use table prefixes for each customer? I don't think this is a good idea because it bypasses any sort of caching my database would be doing for queries.

 

3. Should I use the same database for all and just have a company_id field to state which company the data is for?

 

For a little more information, right now I have 2 users and might gain 1 every month or two I'm guessing. Employees would be logging in to create the quotes and customers of that company would be logging in to view their quotes.

[Jacques1]

You should have one database.

[DeX]

You should have one database.

 

Separate table prefixes or a company ID for each row in every table?

[Jacques1]

One database, one table per relation, one column per attribute. So no duplication at all. And, yes, you would add a company ID to the rows.

 

What do you expect to gain from creating multiple databases or tables with the exact same structure? I definitely know what you would lose, namely the ability to properly manage your server. Whenever you want to update the database structure, you have to do it n times (in the worst case, you even end up with different versions for different companies). Whenever you want to back up your data, you again have to do it n times.

[DeX]

That makes sense, I will do it that way.

What about for a development environment? Should I keep a separate database for that or use the same database in the same manner? I find now when I add a new item for sale into the system, it has to be added to all environments.