Design question

Hi,

Currently I am building an application that tries to satisfy the following user requirements amongst others. I have described my strategies to satisfy the requirements. I will appreciate your feedback for a better strategy.

Requirements

1. The application should be useable with or without a network.

2. The data in the database comprised of data of 13 agencies. Each agency owns their rows and is allowed to edit them. Other agencies can query/view the rows they dont own.

3. There is gatekeeper to the centralized database who certifies each row for its validity to be part of the database.

My Strategy

1. Install MSDE on individual desktops. Before using the application in an environment without a network connection, a replicate database is created on users MSDE instance using out-of-box SQL Server 2000 tool. Replication is used again to synchronize the disconnected database with the central database when there are new or edited records in the disconnected database.

2. Create a table (USERS) that maps each application user to one of the 13 agencies. Add a AGENCY column to each table. Write a stored-procedure that generates add/edit/delete trigger on each table of the database. This trigger tracks the logged-user and either allow/disallow them to modify/delete records based on their membership with an agency.

3. Create a table (MODIFICATIONS) that records table name, row key and action (add/edit/delete) in the database. Write a stored-procedure that generates add/edit/delete trigger on each table of the database. This trigger populates the MODIFICATIONS table based on user action.

Is this strategy sound? If not how will you satisfy the above requirements. I am not a DBA. Is it possible to satisfy part or all of the requirements using Audit or Transact logs?

Thank you,
Amar DasFor item 1: I'm not sure that you need to install MSDE on user's desktops if they don't need entire database. ADO and .Net has capability for disconnected recordsets and tables. Basically copies of tables stored on users box which then can be synchonized (using an update command) with server on network connection.

For item 2: you could create NT security groups and put users in them. Then grant db object permissions to the NT groups. Also, create a seperate stored proc to handle each action (one for update, one for delete, etc...) then grant users access to those stored procs accordingly.