Unanswered: Creating New Database System Structure
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.