I am trying to find out why a programmer has decided to set up our mssql server this way:
He claims that we need to have a separate database for each client rather one database for all clients. We have a web based product and we allow customization for each client but the structure stays intact. I think it would make more sense and be less of a management nightmare to use 1 database rather than several and make adjustments to the existing tables to accomodate our needs -again i have been shot down, probably due to my lack of knowledge in this area.
He claims that that there is a possibility that the information would get mixed up...? (it is medical information).
The users login with a name, password, and location (which is the database) - they have permissions for that specific locale/database along with the customization. He claims this is only possible b/c we have separate databases for each client.
Isn't there a way to add additional structure the the tables to make up for the current limitations that would not allow for this in 1 database?
Does anyone else agree that this is a unique way of structuring our system -probably not the best....?
I welcome any feedback -thanks
From what you say your developer doesnt seem to know what he is doing. Unless the data structures vary greatly between customers there should be no reason to use seperate databases. He will have to come up with better reasons than he's given. Its also easier to reconsile and analyse data from 1 database. If you need any more help mail me.
If your programmer says that, I think its best to get the database designed from someone who knows this stuff and give your programmer the schema to work on...don't get the database designed by him.
Surely having a separate database for each user is the simplest example of programmers who claim to know good database design practices, but don't. Maybe the person is trying to avoid some coding work on his part, but this schema would surely get messy once the number of user grows or you have to make some changes to the database schema (imagine changing the databases and adding tables for each and every user).
The separate databases per client is necessary where either the database product or the front-end to access the database don't offer sufficient security - but is all too often done when programmers/DBAs don't understand how to set-up security correctly, or the people who set-up user-ids are looking for an easy life.