System Design in Master/Slave office setting

[Firecat1970]

Hello everyone,

I have a design question to consult.

Our system will be used by 1 master office, and many regional office.
The requirement is that
1. Master office can view/change all data inputted by all offices.
2. Slave office can only view/change data they inputted.

My question is, at which point would you place the data filtering logic by region. The probable query users would place is "Show me all Purchase Order with PO Number beginning with A1", and I want the regional filtering mechanism to work behind the scene.

Is Stored Procedure the best place to handle this?

In terms of table design, what I can think of is to use "keys and lock" method. e.g. a B-user create a new record, and the record has a column to contain value 'B'.
Each users' profile has key(s), as a HUB office user, he may have keys 'A', 'B', 'C', so he can see all records with lock = A,B or C.
I am currently use where charindex(table.column, userkey) > 0 to check whether a record is readable by a user.... is there other better implementation?

Thank you very much.

 

[mrdenny]

How are you going to be moving the data from the satalite offices to the corporate office? Do the databases at the satalite offices need to have the other offices data, but they can only see there own data, or can they have only there own information?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Firecat1970]

Thanks for replying.

My setup is just 1 single database in the HUB office. The satellite offices are accessing the data via our WEB application.

 

[mrdenny]

In that case the stored proc is going to be where you handle that.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com