June 14, 2015 at 7:32 am
Hi all,
Not really an SQL Server 2008-specific question, but couldn't think of a better forum to place it in. I'm looking for some advice / suggestions on best practices for designing my database structure for the following application.
The database is intended for the purposes of a website that companies can use to manage their business. The format of the website is that each company will have access to their own portal which contains their company information.
As it stands right now, we have only one company as a client, but we intend to get many more going forward.
The format of the server is currently that we have one "master" database containing two tables, one of which stores unique user names (by email address), and another which stores companies that user is a member of.
Then, from there, we have another database which is specific to the company, and whose name is contained within a column of the second of the above two tables.
When the user logs in on the website, a cookie stores the database name corresponding to the company which the user selected to log in to. From there, the website will then connect to that database for all queries.
The challenge which I'm currently facing is coming up with an appropriate structure to allow easy creation of new companies. What I want to avoid doing is having to make changes to every database each time new schema changes are required.
The current solution I'm considering is to have a "template" database which would store a master copy of all structure for the databases - tables, stored procs, functions, DB users/roles, etc... and then, when a new company is created, a new database is created by just making a copy of this template database. This solution works well for the creation of new companies, but it doesn't really solve my problem of changes to the schemas - if I need a new column in a table for example, I'm still required to go through each of my databases to make the change. I know I can execute an sp_msforeachdb script to do that, but I'd like a more elegant solution.
Has anyone else encountered this type of requirement before? If so, can anyone give me any suggestions on what approach I should take?
June 15, 2015 at 2:08 am
have u thought of using sharepoint ?
June 15, 2015 at 7:02 am
I'm wondering here if the untrusted browser can then set the cookie to arbitrary values to try to get into someone elses database? I myself would probably use the cookie to establish secure sessions and keep session info on the server, which of course would also have the database name.
June 15, 2015 at 9:26 am
kramaswamy (6/14/2015)
Hi all,Not really an SQL Server 2008-specific question, but couldn't think of a better forum to place it in. I'm looking for some advice / suggestions on best practices for designing my database structure for the following application.
The database is intended for the purposes of a website that companies can use to manage their business. The format of the website is that each company will have access to their own portal which contains their company information.
As it stands right now, we have only one company as a client, but we intend to get many more going forward.
The format of the server is currently that we have one "master" database containing two tables, one of which stores unique user names (by email address), and another which stores companies that user is a member of.
Then, from there, we have another database which is specific to the company, and whose name is contained within a column of the second of the above two tables.
When the user logs in on the website, a cookie stores the database name corresponding to the company which the user selected to log in to. From there, the website will then connect to that database for all queries.
The challenge which I'm currently facing is coming up with an appropriate structure to allow easy creation of new companies. What I want to avoid doing is having to make changes to every database each time new schema changes are required.
The current solution I'm considering is to have a "template" database which would store a master copy of all structure for the databases - tables, stored procs, functions, DB users/roles, etc... and then, when a new company is created, a new database is created by just making a copy of this template database. This solution works well for the creation of new companies, but it doesn't really solve my problem of changes to the schemas - if I need a new column in a table for example, I'm still required to go through each of my databases to make the change. I know I can execute an sp_msforeachdb script to do that, but I'd like a more elegant solution.
Has anyone else encountered this type of requirement before? If so, can anyone give me any suggestions on what approach I should take?
The very LAST thing you want to do is rely on the user's computer to determine the database to touch. That kind of security hole is a mile wide... I'm not sure you can afford to have separate databases. You might be able to use PARTITIONs to segregate data on separate spindles, but that may not satisfy regulatiory requirements. I don't know that there's any easy way to have separate databases and not have to create some pretty sizable maintenance scripts to allow for updates, which might not be something you can just "do" to every customer at the same time. Good Luck with this...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 15, 2015 at 10:04 am
Our company has that exact situation. Just accept that you will have different "versions" of the product in effect for different clients at different times. It's just too difficult to try to force every client to the exact same structure. So, schema changes will not automatically be made for every client immediately, but will be phased.
Use a version/release/mod number, similar to what SQL Server itself, IE, Oracle, etc., use. Then just document what each version/r/m contains.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 15, 2015 at 2:35 pm
ScottPletcher (6/15/2015)
Our company has that exact situation. Just accept that you will have different "versions" of the product in effect for different clients at different times. It's just too difficult to try to force every client to the exact same structure. So, schema changes will not automatically be made for every client immediately, but will be phased.Use a version/release/mod number, similar to what SQL Server itself, IE, Oracle, etc., use. Then just document what each version/r/m contains.
That's what you have to do. Some schema changes, rather than being phased, will apply only to subsets of customers and never reach the others. All of it has to be very thoroughly doumented so that you know what is where.
We had this requirement at Neos Interactive when I joined them 13 years ago; and customers controlled upgrade dates; and each customer had a thoroughly customised GUI; support could be interesting when several customers needed different versions of several fixes. We didn't have security problems arising from everything running on one server, as each customer had its own server (more often its own servers, number depending on workload) but we did have serious security problems rising from access by third parties (customers of the customers) who couldn't be trusted not to do something crazy so we we generated some pretty heavyweight lockdown methods (after the first serious incident, unfortunately). You will need to have extremely strong security with multiple customers on one server, and must include things like no two customer databases using the same passwords for maintenance access and so on (we had that rule even with separate servers, and wrote it into the customer contracts - which was a useful sales point).
Tom
June 15, 2015 at 3:43 pm
Regarding the security implications of using cookies for database access, I'm aware of them, and plan on dealing with that in due time.
As for the comments on propagating changes, thanks for the feedback, seems like a sensible approach. Challenge if all clients are not on the same version though, is that the code might not work correctly otherwise - so I'll need to consider having different versions of the code as well, or alternatively enabling certain features based on the database version the client is running.
June 15, 2015 at 5:19 pm
That's a valid point, but, from what I've seen, it will just not be possible to force all clients to the current version. They delay for all sorts of reasons. What can you do, force them from being your customers? Nah, just not practical. You pretty have to accept multiple versions of the code.
How would you react if MS tried to force onto the latest version of SQL Server the month after it came out?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 15, 2015 at 5:38 pm
That's a valid point, though perhaps I should have mentioned that the product in question is a web-based portal. So for example if SQL Server Azure were to make some changes, they would just automatically propagate them to all their customers, after a preview window of course.
So using that same model, I suppose I could have two versions running - the preview version and the live version. Any changes to the structure would be applied to the preview version initially, and then after a certain window, propagated to the live version. Of course, bugs that require structure change would need to be introduced more rapidly.
June 16, 2015 at 2:02 pm
kramaswamy (6/15/2015)
Of course, bugs that require structure change would need to be introduced more rapidly.
:w00t: - I think you might want to rephrase that :exclamation:
Tom
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply