June 22, 2007 at 10:48 am
Hi,
I am building a .net app that will store companies information that use my site. I am thinking about storing all membship data (UserNames,pass etc) in one main database. Then I will give each company their own database which will store all other types of data. For example when a user logs in I authenticate them against the 1 memberhip database and grab the company ID they belong to. With that companyid I will grab a the appropriate connection string for that companies db(probably stored in web.config) and save the connection string in a session variable. When ever I read/write to the db I use the connection string in the session var. Does this sound like a good plan?
Or should I store everything in one db. If I do that. I am afraid over time that the db will get to overloaded.
Thanks,
tom
June 22, 2007 at 11:18 am
There are a lot of variables that you didn't touch on in making a decision of this nature, but based on what was provided I recommend a single database. My main reason would be maintenance, if all the database will look the same then you have to remember to make the same change in all databases (even a small change for one customers problem). If you start letting them diverge from each other then it would be hard to determine what impact a "global" change might have on each individual database.
Additionally if you will be using a number of reference/code/lookup tables then you would likely end up "duplicating" that data multiple times (it is posibile to link database, but I don't recommend doing so for referential integrity).
If you get to the point where SQL server can't handle the traffic on a single database you'd be looking at moving to multiple servers rather than multiple databases. There are solutions for that (Clustering, partitioning, etc) that can be explored when the need arises.
James.
June 22, 2007 at 12:13 pm
Thanks James for your feedback. I will have to investigate clustering and partitioning options that you mentioned.
-tom
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply