I am setting up a database that will be used by multi-users using linked-tables. The problem I am having is there a couple of different users that can modify tables. However, there is one (user A) that is above other users. So, let's say user B changes the values in a table and logout, and then user A login and does not like the changes user B made, then user A can restore all values back to the original (before user B changes). I am not sure how to this. Should I just create 2 copies of the same table, so kind of mirroring ? Then, how do I keep track of changes made, do I need to create a log table ?
Sounds like you need to have them establish a standard that will be adhered to.
Too many changes/suggestions by individuals not familiar with database design will surely lead to your db not being normalized.
If a standard cannto be determined, you may want to develop a switchboard with a couple of forms (not allowing the users to view/modify the tables). From that point user A could have a custom form, and user B could have a custom form.
(I would strongly suggest the "A" over "B". Eventually "B" will become a nightmare of "I don't like this, I want this here, that there". Eventually it will drive you crazy.)
In my case, it's not so much about the database design that is changed, but the value of the fields itself. So, basically, the lower-level users can change the data. However, in the end, those changes must be approved by higher-level users. So, maybe, when higher-level users login, he/she will see the changes made and if he/she agrees, then those changes will be copied to the original tables. Otherwise, it will be disregarded. Any idea about how I should go with this ? I was thinking of creating a log-tables that record all changes made by lower-level users, but still not sure how.