Globally filtering database tables

Question

Globally filtering database tables

fosjunk

SSC Enthusiast

Points: 138
More actions
January 23, 2009 at 4:47 pm

#97092

I am working with multiple databases which which have the same structures to them. I.e., the way it was set up is that each database corresponds to an individual organization. We are looking into combining these and are looking at the most efficient way to be able to filter the data to allow an organization to see only their data. I am not aware of anything in SQL Server that would assist in this but wanted to see if anyone has done this and what you would recommend.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

jacob sebastian SSChampion Points: 11822 More actions · Answer 1

I am not clear about what exactly you expect from SQL Server to do here. If you are combining the data of different organizations into a single database, your tables might have an extra column that stores the organization ID. Stored procedures should filter data based on the organization ID being passed as an argument

We do have applications where we store the data of multiple customers in the same database. In those cases, the customer ID is stored and all stored procedures have a UserID parameter and from the UserID we identify the customer and returns the correct rows.

I am not sure whether it will be helpful in your case, but one option is to store the data into a single table and create views for each organization. But that would need application changes as each organization needs to query from a different view.

could you elaborate a little bit more on what sort of assistance you expect from SQL Server to solve this?

.

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 2

I agree with Jacob... well, except for combining everything into a single table and using views. Instead, look into the advantages of using a "partitioned table" and views. Most of the advantages are in the areas of archiving old rows and some advantages in maintaining indexes while still have SA access to all of the data all at once when needed without having a single monster table to work with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 3

If I get you requirements correctly, wouldn't database schemas work here, have each client have a schema in one database. Search this site for schemas, or check out BOL?

Andrew

fosjunk SSC Enthusiast Points: 138 More actions · Answer 4

Thank you for the replies. I have been away from SQL server development for a couple years and wanted to make sure I had not overlooked anything easy. The application was originally built without any organization information and primarily without stored procedures. It did not use stored procedures to retrieve data. I had started down the path of modifying tables and utilizing stored procedures, but because there is a fair amount of rewrite needed thought I would look around a little to be sure. I do not want to head down the individual views per organization path as that would be a nightmare. If I am correct I do not think SQL Server can take parameters in views either. It sounds like passing the parameters to stored procedures is still the best approach.