Hello,
Over the past few months, I've learned that starting with a good database design can save hours of work (and hair) in the long run.
I'm looking for a way to track changes that are made to a database. Call it "flagging" or "adding to the queue" if you will. The requirement is that I can track which table fields were changed by a given user and be able to view the existing and new data side by side before the data is set to "live" or "approved" by the admin.
Before anyone tries to answer that, please note that I already figured out how to do it:
Users
=============
id,
fname
fname_shadow
fname_updt_chk
lname
lname_shadow
lname_updt_chk
As an example, when a user (Jennifer) updates her first name, the data is entered into the "fname_shadow" field, then compared with the "fname" field using SQL (IF fname <> fname_shadow ...), and if it is indeed different, I set "fname_updt_chk" to True (Flagging it as changed). I then have a notification that a change is made to Jennifer's first name field, and I have both the new and existing data to look at.
So, what's the problem? ...
Quite simply, how do I do this with a table of relationships. Say for example you have the following relationship table:
User Relationship
==============
id
user_id <------ This is the related part
department
college
Ok, let's say that Jennifer has 10 relationships (10 different rows) for each of her affiliated department & college. If Jennifer decides to delete for change one of these relationships, how in god's name will I be able to track it? The only solution I can come up with is keeping two identical tables and somehow comparing the two. That sounds tricky and I'm not sure how to do it so I thought I would ask here.
Please let me know if you need clarification.
Thanks!
Start Free Trial
