Jump to content
Sign in to follow this  
DeX

Looking for help with database structure for SAAS

Recommended Posts

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.

Share this post


Link to post
Share on other sites

You should have one database.

 

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.