Database for multiple clients

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

Database Design- Referencing multiple database

Hi All,
I am designing database where few of the master tables will reside in different database or in case different server. Scenario is
Server "A" with Database "A" may host the "Accounts" table.
Server "B" with Database "B" may host the "Product" table.
I am designing database "Project" which will hosted in Server "A".
My application requires this master tables [readonly access] as data inserted in my application refers this tables. Also there are reports to be generated which refer this tables.
How do i design my database and sql queries?
I am thinking of approach of having equivalent tables created in my database and writing service which keep tables in my database in sync. This will ensure good perfomance during transaction and reports as they will need to refer this table locally as opposed to different database or different server.

Any thoughts on above approach?? or any better/standard way for such scenarios ?

Thanks in Advance. Your inputs will be of great help.why multiple databases? It makes it harder to enforce referential integrity and it can cause other headaches. i just recently ran into an issue where i swapped out a database instead of doinng a restore and the application broke because database objects that reference another database actually store the database id instead of the name. it was easy to fix but I still had an hour of down time. thank god it was not a production server.|||Hi All,
Hi.
Hi All,
I am designing database where few of the master tables will reside in different database or in case different server.
Really...
Scenario is
Server "A" with Database "A" may host the "Accounts" table.
Server "B" with Database "B" may host the "Product" table.
I am designing database "Project" which will hosted in Server "A".
How do i design my database and sql queries?
In a word: "poorly".
I am thinking of approach of having equivalent tables created in my database and writing service which keep tables in my database in sync. This will ensure good perfomance during transaction and reports as they will need to refer this table locally as opposed to different database or different server.
Well, that plan certainly is......"creative"...
Any thoughts on above approach?? or any better/standard way for such scenarios ?
I'd say, practically anything else would be better.
Thanks in Advance. Your inputs will be of great help.
No problem.|||Ahh, the much-vaunted (but almost certainly mythical and definitely undocumented) 'unreplication' functionality of SQL Server! ;)

On a more serious note, if you really want to proceed down this route you will need to set up Linked Servers and enable the MSDTC service.

I would implore you to think again.

Lempster|||That's certainly a novel approach - doomed but novel. Out of curiosity what advantages were you expecting from spreading your system across different servers? and what was your database(s) supposed to hold?|||How versatile

but, um, how much experience do you have?

Oh, I get it, you're in upper manglement|||Ouch Brett...that'll likely leave a scar. ;)

I assumed from the description that between the lines there is a plain motivation. Seems to me that the distant (aka remote) databases are already in existance, and the O.P. has been given a task to pull some data in from other servers, and followed the logical path to the current planned implementation!

Sheesh. How silly is it to have databases spread all 'round when you can just copy them all to one place and just keep them in synch. ?|||Hi All,
I am designing database

I didn't read that all

I designing a housebuilding business

I plan to have all of my nails located in DC, all of my wood in Oregon, all the fixtures in NY, and all of my roofing supplies in N'orleans (There all over the place and free)|||sometimes i go to other sql boards after following a link and i am always surprised at the difference in the content from this place.|||how so?

too short|||i think this board has more of a tendency to slam the OP.|||Nothing like the real world, eh|||No soup for you, poster!|||There are scenarios where distributed databases are a good idea - but they don't come up very often (at least I've never met one). Must admit it would be quite interesting to work on one though. I think it's important to hear the reasoning behind his system and find out why he thinks it's so versatile.|||I agree there, Mike. Sometimes you need to know the rationale behind the current design before dashing said design upon the jagged and bloody rocks of despair. ;)

If nothing else, it also allows one to more pointedly help (and/or taunt) the original poster's assumptions and reasoning.

As I am sure you are aware, sometimes stuff just comes across initially so far off the wall it's hard to resist a good-natured jab in the ribs and perhaps a well-intentioned kick in the groin.|||Hi all,
Thanks for all kinds of replies.
Well first let me clear (which i shud have done in my first post.) , that database are hosted on diff machine coz they are mastered by diff groups of ppl or the department. Application which we will be designing depends on these data. We cannot certainly have all required data in one single database.
Option of creating creating identical tables and syncing regularly is as i feel the better than writing down the distributed queries.

Any thought on it ??
Thanks,|||Hi all,
Thanks for all kinds of replies.
Well first let me clear (which i shud have done in my first post.) , that database are hosted on diff machine coz they are mastered by diff groups of ppl or the department. Application which we will be designing depends on these data. We cannot certainly have all required data in one single database.
Option of creating creating identical tables and syncing regularly is as i feel the better than writing down the distributed queries.

Any thought on it ??
Thanks,|||What's wrong with just downloading the data from the other data sources via feeds - bcp each file into a transfer table - check the data - put the changed data into your database. How many feeds could you expect this way, what size (roughly) for each feed?

Your method may save you having to write any feeds but the people writing the app are going to hate you for the rest of their lives :)

Mike|||Well first let me clear (which i shud have done in my first post.) , that database are hosted on diff machine coz they are mastered by diff groups of ppl or the department.This sounds like a small data warehousing initiative to me. Perhaps that is the approach you should be taking.|||We do stuff like this on a daily basis. Consolidating data from different departments on a single machine for historical and modeling purposes. I use a combination of distributed queries and web services that allow us to yank data in flat file format from some machines, and yank data directly from others (via linked servers).

Seems like downloading and synching databases is alot more work than you need to be doing to do what (i understand to be...) you need to do. Data feeds and distributed queries are much better with respect to basic database design conventions, and synching databases just causes a spontaneous sphincter constriction whenever I hear it.

Duplication of data = bad. Direct/indirect (and on-demand) access to remote data = good.

Sounds like you've already decided though, so g'luck to you :)|||Well the things getting more complex. We will be having four external database to depends on for our application.
Reason of staying away from distributed query was that our application requires this data (external database table serves as master table for our application) in the transactions. And of course in reports as well.

I liked the suggestion of getting feed from external database into temporary staging area and then updating only updating the changesets in our tables.|||Originally posted by versatile_me
I liked the suggestion of getting feed from external database into temporary staging area and then updating only updating the changesets in our tables.I'd like to claim first dibs on the concept but I suspect it's been around for 40 years.

I know it's fun comming up with new systems but after you spend 6 months sweating blood trying to get it to work there are only a few possible outcomes :
it fails and you have to call in a professional who is then faced with working with a flawed design or loosing the client by throwing everything away and starting afresh.
it works, but only just, and after 3 months limping along the system gets abandonded.
it works and is a fantastic success - I'm afraid this is the least likely outcome :)
If the application is at all important to your company then it may well be worth getting an experienced profesional in to do the work and you provide the business requirements. This would be a good way to learn about database/system design and also might ensure any good ideas you may have will appear in the finished product.

Mike|||We do stuff like this on a daily basis. Consolidating data from different departments on a single machine for historical and modeling purposes. I use a combination of distributed queries and web services that allow us to yank data in flat file format from some machines, and yank data directly from others (via linked servers).

Seems like downloading and synching databases is alot more work than you need to be doing to do what (i understand to be...) you need to do. Data feeds and distributed queries are much better with respect to basic database design conventions, and synching databases just causes a spontaneous sphincter constriction whenever I hear it.

Duplication of data = bad. Direct/indirect (and on-demand) access to remote data = good.

Sounds like you've already decided though, so g'luck to you :)

...
I am thinking of approach of having equivalent tables created in my database and writing service which keep tables in my database in sync. This will ensure good perfomance during transaction and reports as they will need to refer this table locally as opposed to different database or different server.

I'm struggling to see much difference between TallCowboy's "daily consolidation" and the OP's idea of equiv. Master tables that are regularly refreshed.

Although duplicate data = bad, isn't it common practice to have read-only replicated tables on a different server for reporting (or other read-only) purposes? Obviously; the data is as old as the last replication so the designer has to consider the ramification of slightly old data, but this localizes remote-connection / design breakages into the DTS / Copy programs, not the report.

Not so much "versitile" as "stable". (at least - until the source system changes and you have to fix the DTS - or retest it each and every time it changes ... so I'm not saying it's a great idea, just maybe unavoidable).|||I'm struggling to see much difference between TallCowboy's "daily consolidation" and the OP's idea of equiv. Master tables that are regularly refreshed.The difference is that in my system the data is not imported on a daily basis "as-is", but rather the existing tables are updated with NEW data that we did not previously have. However, I see your point in that the difference may be perception (and/or semantics) and my understanding of what the O.P. is contemplating as an implementation.

In my world, the data we import (via web service or via remote access) is not a "synching" of databases. It's the importation of data from external providers, and placed into our own "consolidated" and normalized database tables (and used for our own devious purposes completely unrelated to the original source(s) of the data). At least in my eyes, a completely different process than importing actual tables from a remote database and keeping them in synch.

Although duplicate data = bad, isn't it common practice to have read-only replicated tables on a different server for reporting (or other read-only) purposes? Obviously; the data is as old as the last replication so the designer has to consider the ramification of slightly old data, but this localizes remote-connection / design breakages into the DTS / Copy programs, not the report. I can't speak for this one, because we don't do it. We either provide access to our data via controlled (read-only) linked servers and associated userid's, or via a web service that is a front-end to our database.

We absolutely don't allow uncontrolled access to our tables, even remote access via linked servers/userid's is typically controlled with a view. If there is time and/or the longevity of the projects dictates, we write a web service wrapper to the external world and make them keep their nasty little grubbies off our databases.

We expect (and generally require) external/remote databases to have (or support) the same indirect access. We don't want our applications failing because they changed a table on the external system.

Not so much "versatile" as "stable". (at least - until the source system changes and you have to fix the DTS - or retest it each and every time it changes ... so I'm not saying it's a great idea, just maybe unavoidable).ed-zachary :)|||i think this board has more of a tendency to slam the OP.

Really?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67782&whichpage=8|||ROTFLMAO ... like Mastercard ... that was priceless :D

database design question

Hi!

We are designing a big database with multiple parts (like Human Resources, Sales, Production) etc. in Sql server 2005. Should we devide the main parts into separate databases, or should we put all tables in a single database and use schemas to group the parts (like the AdventureWorks sample database?). Is there a general recommendation here?

We are considering multiple databases so one more easily could move one database to a diffrent server if needed.

Hi,

I would suggest you put all the tables in a database and devide them with schemas, just as AdventureWroks shows. Putting the tables across will result in many problems, and performance hits.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

If we devide into multiple databases, we will try to minimize the references between them. After all they represent different services within the enterprise solution.

I realize that the administrative tasks increase as the database count increase, but why would it be related with performance hits? If the traffic to one of the databases becomes an issue, we can easily move it to a new fresh server and thus gain performance.

Could you also be more specific on what other problems you refere to.

Thanks!

database design issue

Here's a situation I'm trying to replicate in a database. A CASE can be tied to multiple EVENTS and an EVENT can have multiple CONTACTS. Each CASE or a CONTACT can have a SURVEY.
I am unable to figure a way to accomplish the last task.

Any help is greatly appreciated!
-Jay

Note: Words in capitals represent tables.Create a table of surveys and include the primary key as a foreign key in the CASE and CONTACTS tables.
Not sure what is so difficult about this...|||Not sure what is so difficult about this...Losing a table made it easier I guess :)|||Thank you Blindman...I guess i was thinking out loud :D|||Glad to help. Welcome to the forums.