Allowing specific users to accept new and updated records

[rebeccaatemc]

Here's the deal. I want any user to be able to fill out a form with information for a new project. I do not, however, want that new information to be incorporated as a record into my project table until it is accepted by one master user. Similarly, I don't want all users to be able to modify records without the notification of the same master user. Ideally I would have a two forms, one for new records and one for record updates, which could be submitted to the master user for accenptance. Upon acceptance, the changes would automatically become a part of the projects table. Does that make sense?

Any suggestions on how to go about doing such a thing? I would really appreciate the help. Thanks.

[mr_vodka]

Try it with having a table that is very similar, if not duplicate to the project table; called staging.

Set up the normal users with having no modification or record creating access to the Projects table. However, the users should be able to edit the staging information. Once they submit the data in the staging table, you can flag the record or notify the master user that it is ready to be added or updated into 'Projects'. The master user would go in with his access and perform an acceptance script that would basically copy the data from the Staging to the Projects table

[rebeccaatemc]

Thanks. That sounds great. I'll give that a try and then probably come back at you with more questions. ;)

[Steven H. Blackwell]

This is really not a security question; it's a data modeling question.

Consider this: proposed projects have a number of attributes, pieces of information or data that need to be tracked. Approved projects have all those pieces of information, plus additional ones. Yet you may wish to track all proposed projects, whether they are approved or not.

So, you create two different tables. In the Proposed Projects table you put the various attributes that need to be tracked about all projects. In the Approved Projects tableyou put only those attributes that need to be tracked once a project is approved. Plus you also create a primary key field in that table that you leave blank initially. Assign whatever access privileges to both of these tables that are appropriate.

Create a relationship between the two tables where the primary key in the Approved projects table = the primary key in the Proposed Projects table. When the supervisor has approved the project, then by script, set the primary key in the Approved Projects to be the same as that in the Proposed Projects. This will create the record in the Approved Projects table.

Then, the relationship references the information from Proposed in Approved, and you display it where needed. There is no copying of data from one table to the other, running the risk of contradictory or confusing data, You can also apply whatever additional rules are needed based on whether the project has been approved.

From a data modeling standpoint, this construct is called a subclass of the entity Projects.

HTH

Steven