Best design for edit tracking?

[Roy]

Hey all,
This is a general question. Here's my scenario: We have a legacy
database. The core table within contains almost 4 million records in
SQL Server. Currently we have a web front end which allows uses to
search through the database for the information they need.

What the client wants is a web front end which allows some users to
edit the core table above. No problem. What the client also wants is
for the non-editable web front end (used for research) to display (via
colored text) which records have been edited. Example:

1.) Jimmy edits records x, y, and z in coretable1 using the editing
web app.
2.) Jane comes along and uses the research web app to hunt down some
records. She views records s through z. In her view she notices that
certain cells in rows x, y, and z are colored red. This tells her that
Jimmy has edited those specific fields in those specific rows.

My question is: what is the most efficient way to track these column
specific edits so that my web app can display them? This may seem like
a SQL Server question, but the reality is that my web apps have to
interact with SQL Server so if anyone has any input, I'd love to hear
it! Thanks.

 

[Galin Iliev [MCSD.NET]]

well it depends on requirements. definetely you will need to store data
when row is edited/inserted

once you have this you could place different colors depeding on time
since they were last updated

other approach is ... hmmm... see e-mails - you should keep read rows
(and check if rows are changed since last read) per each users but this
will increase your database a lot. the pro for ths is that it is much
precise

Hope this helps
Galin Iliev[MCSD.NET]
www.galcho.com

 

[Paul]

Sounds like the full user requirement hasn't been identified just yet. If
you made two edits to a field, one to change it, the 2nd to put it back to
what it was originally - should this 2nd edit be flagged as red too? Also
once the user has this functionality, do you think they're going to want to
see the history of any changed field i.e. from what / to what and by who?

Answers to the above would dramatically change the solution. Either a bit
column next to each field to indicate a change to that field, or two copies
of each record, one containing the original record, the other containing the
edited verison (which would work with the two edit example above). If you
wanted full change history, you'd either need a table per table or field
recording the change or a full record per change in the main table.

Regards,

- Paul.