I have a SQL Server 2005 Express database that was designed to be used by one client. What is the best way to change the design so it can contain multiple clients that can only see data entered by users of each client organization?
Also I'm using the asp.net membership database to handle login and profiles. Can this be used with my multi client database?
Let's say you have TableA that contains data for one client. You intend to change the design to include data for multiple clients, where each client sees their own data.
Here are classic database design steps to follow in this situation
1. Create a reference table that holds the organization name and a unique ID for each organization - let's say that field is called organization_id
2. Add a record per organization to this table.
3. Add the field organization_id to TableA and set the value to 1 for all existing records
4. All future records added into TableA must have the correct organization_id value.
5. Create a View that restricts data in TableA to a specific organization_id.
6. Change your application to ensure that all data access is done via views that are restricted on organization_id
That's a kind of summary of what you're looking for. I repeat that using Views are the standard way of ensuring that clients only see their relevant data.
For your second question - certainly you may use login and profiles. I have implemented this kind of design by using Roles (aspnet_roles) as the reference table I mention in step 1 and 2. So you create a role per organization. Then add RoleID to your other tables as in step 3.
|||Thank you very much for this information.
Is it possible to use my current aspnet_database as you mentioned, even if I already use roles for limiting user access to pages in the application? Also the application uses stored procedures to insert,update, and delete date.
Thanks again for your good thoughts,
|||Yes, it's possible to use a current security database even if you already use roles. Users can be in more than one role. So keep your existing roles, and create additional roles for the organizations. Presumably all your current users are from one organization (your first), so add them into the correct new role - that won't effect the existing user access limits.
Great that the application uses stored procedures - that is already a sign of good design. There are several ways to incorporate multiple clients here. You could add a parameter to your stored procedures which accepts the correct RoleID and ensures that the new column is maintained correctly. Note that if your procedures already accept a UserID, then you don't have to change the parameter list - within the stored proc you can do a lookup of the correct RoleID.
Note that changing the stored procedure interface will need corresponding changes to all your client code - and regression testing. Another option depends on how your application connects to the database - if you are using a common login to the database for all users this is not an option, but if you are using user logins (individual logins and passwords for your users), then you can programmatically determine within the stored procedure who is logged in (using a system function) and then get the RoleID.
Good luck! By the way, if you extending the application for somebody else (your manager or a client), make sure they understand that changing from single to multiple clients is a significant change to an application - don't sell yourself short!
sql