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 |
|
yofdo
Starting Member
1 Post |
 Posted - 2009-08-10 : 11:50:18
|
| Hi All,I work in a marketing consultancy and at the moment we use MS access to hold our data. We recently decided to move into SQL server to increase the efficiency and speed up the systems. We have quite a few clients and each of them come up with different projects throughout the year, these projects are most of the time independent on its own, but some are repeated annually, or with a set time period basis...At the moment what we do is, we create 1 database per client per project and set up a quick system and carry out the research work. The repeating databases I use the same structure and upload new data onto it. Some of the recent databases I created have a project table, and I have grouped that entire project's data referencing to that project. So at anytime the database would have all previous year's data, but a user would be only working on that current project, unless they want to check what happen last year.Most of the projects are similar in nature, either Lead Generation projects, Subscription management projects, fulfilment projects or simple telephone research project. They have a company table, contacts table and a questionnaire table. For 99% of the projects Company and Contact tables are same. Only the questionnaire table differs. Some projects have specific systems such as the way a lead generation system work is different to the way a subscription management system work.So my question is, How do I decide on to structure my database (company database system within the SQL Server). Is it 1 database per customer per project? Or is it 1 database for the whole company and have all clients’ data within that. Is there a way of grouping each client and then have another grouping mechanism to group their projects around the main client object. (I guess use schema???). Also remember that each client is different and we treat each of their data individually.I'm very much confused with this and without an answer for this I simply can’t progress my development work. Please let me know of your opinion.Please help me!!!!Thanksyo----- yo ----- |
|
|
ScottWhigham
Starting Member
49 Posts |
Posted - 2009-09-03 : 09:14:29
|
| I imagine that, within the past three weeks since you posted this, that you've found the answer but just in case you still need help I'll try!You need to answer one question before you can decide: "Will the client ever need to store a *physical* copy of the database?"If the answer is, "No - we maintain the database" then it's an easy, "You should have a single database with tables defining relationships, etc."If the answer is, "Yes - the client always has the physical copy" (which it doesn't seem like it), then the answer is, "You probably want separate databases for each client or project"If the answer is, "Maybe - some clients want the database and others don't" (or some variation), then the answer is "You should have a single database with tables defining relationships, etc. Whenever a client wants a copy of the data, execute an SSIS package that builds the database for them."========================================================I have about 1,000 video tutorials on SQL Server 2008, 2005, and 2000 over at http://www.learnitfirst.com/Database-Professionals.aspx |
 |
|
|
|
|
|
|
|
Database Structure Design