DeX Posted January 9, 2017 Share Posted January 9, 2017 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. Quote Link to comment https://forums.phpfreaks.com/topic/302900-looking-for-help-with-database-structure-for-saas/ Share on other sites More sharing options...
Jacques1 Posted January 9, 2017 Share Posted January 9, 2017 You should have one database. Quote Link to comment https://forums.phpfreaks.com/topic/302900-looking-for-help-with-database-structure-for-saas/#findComment-1541232 Share on other sites More sharing options...
DeX Posted January 9, 2017 Author Share Posted January 9, 2017 You should have one database. Separate table prefixes or a company ID for each row in every table? Quote Link to comment https://forums.phpfreaks.com/topic/302900-looking-for-help-with-database-structure-for-saas/#findComment-1541235 Share on other sites More sharing options...
Jacques1 Posted January 10, 2017 Share Posted January 10, 2017 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. Quote Link to comment https://forums.phpfreaks.com/topic/302900-looking-for-help-with-database-structure-for-saas/#findComment-1541238 Share on other sites More sharing options...
DeX Posted January 10, 2017 Author Share Posted January 10, 2017 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. Quote Link to comment https://forums.phpfreaks.com/topic/302900-looking-for-help-with-database-structure-for-saas/#findComment-1541268 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.