Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ththomps
Starting Member
1 Post |
 Posted - 2005-07-06 : 10:41:48
|
| Hello,I will ask forgiveness on my lack of design skills upfront. I have developed an asp.net application to access a SQL 2000 server backend. We no longer have a dedicated database architect/designer and I have been tasked with building a new application and database that can be used for multiple clients.What it did: The application was an asp.net application with the sql 2000 database backend which housed 3.5 million prospects including (ID,CompanyName,Address,Phone,ContactName,ContactTitle,#Employees,SalesVolume,MinorityOwned,IndustryCode(Sic Code), etc.). The data was designed to pull this information and display it ordered by State,City,Zip,Zip4 thus allowing tight areas for a sales representative to walk or drive.New: The new application is going to be used by more than our current client. They are attempting to introduce 2 more clients(application users). So ABC company was the old user and now we have 123 company, and say acme company for the 3rd.Design Problem: There are potentially 50 different select criteria that each company could base a selection on. I Know that ABC company selects prospects based on #Employees and salesVolume. However, 123 company may select on that and Minority owned or SicCode) and Acme company may select their prospects on any one of the other 50 criteria.I want the users (Impression) of performance to be fast. (IE. the search criteria) IE. Select CompanyID,CompanyName,Phone from prospects WHERE #employees >10 and SicCode =123456 needs display rather quickly.I also need to be able to track Current customer, batches, and if the prospect is currently allocated(to a sales rep) or not so they are not re-selected by the client.The project worked great with ABC company but, now I am going to have to do the same thing for 123 company and Acme company.Question:Is it best to have a Master database containing all possible 50+ fields and then have a client database containing their specific search criteria? IE. ABC company would have ProspectID,CompanyName,#Employees, and SicCode123 Company would have ProspectID,CompanyName,#employees,MinorityOwned,SicCode)Acme company would have ProspectID,CompanyName,#Employees,Lattitude,Longitude)Each database housing a allocation tracking, customer tracking, etc.I know the prospects will be updated quarterly (Prospects removed as defunct and prospects added as new businesses).I can see this becoming a nightmare for consistency and time.The other option I had in mind was a single database with one Prospects table containing all of those fields (very wide table) and then independant tables for each company's (Batches, allocation tracking, and currently customer tracking)I suspect that the number of concurrent users could have some significant performance issues with regards to this. I know I would have to index the table like mad but, I am not to concerned with the space involved in this. I know it would make updates much simpler.Is there anyone who has experience with this type of design?I have searched the site and newsgroups but could not find anything remotely similar to a design question such as this.regards, Tom |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-07-06 : 11:48:00
|
| Definitely seperate database for each company.Customised scripts re adding optional indices for each company for better performance.If the expansion of this gets too difficult to manage in terms of new installations, then add every index to everybodies database...(and prepare to handle the political side of dealing with the drop in INSERT/UPDATE performance...and increased space requirements for the existing customers) |
 |
|
|
|
|
|
|
|
Design issue 1 or multiple databases?