RopeADope Posted April 27, 2011 Share Posted April 27, 2011 Hi all. I'm working on a business application that stores data for several clients. I'm trying to figure out what the best practice is for this sort of application. Should each client have their own database or is it safe to store all the clients data in one database? Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted April 27, 2011 Share Posted April 27, 2011 Depends. A single database will be fine, as long as you properly define your tables and set appropriate security. However, if there are going to be a lot of tables for a lot of clients then regonalising the content into multiple database would make life easier all round. Quote Link to comment Share on other sites More sharing options...
RopeADope Posted April 27, 2011 Author Share Posted April 27, 2011 The application database is ~55 tables so if I did separate databases for each client, that's what would be created every time. In my head, it seems like a lot to be remaking for every client but my assumption is that security would be tremendously easier because I could set user permissions per database, correct? Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted April 27, 2011 Share Posted April 27, 2011 you can set user permissions per table, you can prefix each table for each client, no need for a new databases. The issue comes when you want to administer the databases, if you have 100 clients with 55 tables each all in the same database, that's 5500 tables you need to sift through to get the ones you want. Having a master table that can pull the client tables by prefix makes this nice and easy, as does segregating the tables over multiple databases ( normaly by region - such as europe, america, asia all get a database each) that way you can filter down where you look by the information you have on your client. Quote Link to comment Share on other sites More sharing options...
RopeADope Posted April 27, 2011 Author Share Posted April 27, 2011 If I prefixed the tables, I'd still have 5500 tables (assuming 100 clients). I guess that's why it seems like separate databases would make more sense because then they could authenticate to the database and I could reuse the same code for every client. If I prefixed tables, wouldn't I have to adjust queries to use the proper prefix? Quote Link to comment Share on other sites More sharing options...
fenway Posted April 28, 2011 Share Posted April 28, 2011 If you ever need to cross clients, then different databases is a royal pain. Quote Link to comment Share on other sites More sharing options...
RopeADope Posted April 28, 2011 Author Share Posted April 28, 2011 1) Cross clients? 2) So I guess I'm still kind of unsure as to what route I should take... It seems like I have three options: different databases, prefixed tables, or same tables with record identifiers(if that's even feasible). Thoughts? Quote Link to comment Share on other sites More sharing options...
fenway Posted April 28, 2011 Share Posted April 28, 2011 If you never need to access data from multiple clients, then sure, why not, different DBs is just fine. Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted April 28, 2011 Share Posted April 28, 2011 Your third option isn't a commercialy viable solution. No company is going to accept that their information is held in the same table as several other companies all with the same access level. I am not sure how many users you can acctualy assign to a database (I think it's noth of 320,000 - but I just pulled that from thin air) but you can still use database authentication to dictate which table prefix they can and can't access. I think Fenway's first post was in refference to If you ever need to cross refference the information between multiple companies over multiple databases it's a pain in the ass (such as generating reports on time allocation for a support service). Quote Link to comment Share on other sites More sharing options...
RopeADope Posted April 28, 2011 Author Share Posted April 28, 2011 Ah, okay. Thank you both very much. I have a much better understanding now. I think the route I'll need to take is the table prefix method. Eventually, there will be some cross-client data mining for internal purposes so I'd like to be prepared for that. Thanks again! Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted April 28, 2011 Share Posted April 28, 2011 you're welcome Quote Link to comment 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.