Advise on tracking changes

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Advise on tracking changes

Jolle Carlestam
I'm interested in some advise on how to handle a need for tracking  
changes made to a record. Would you create a new record for every  
update with a link to the original record and flag the old as "old"?
Or would you have a "log" table for changed field content?
Or a field in the table containing changed info?

How does wikis handle this?

HDB
JC

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: Advise on tracking changes

Jussi Hirvi
Jolle Carlestam ([hidden email]) kirjoitteli (5.7.2007 11:03):
> I'm interested in some advise on how to handle a need for tracking
> changes made to a record. Would you create a new record for every
> update with a link to the original record and flag the old as "old"?
> Or would you have a "log" table for changed field content?
> Or a field in the table containing changed info?

That depends on the project. Once I built that last option of yours, "a
field in the table containing changed info" - plus time and login name and
*old* info (which was written over). Worked well, but gets clumsy if there
are very many updates.

A separate "changes" table might be neat, with id reference to the changed
record.

> How does wikis handle this?

I think wikis simply save each version with timestamp etc., and then use a
comparison algoritm to compare versions. BTW, that algoritm easily loses
track completely, in MediaWiki at least (I used it intensively a year ago),
but when it works, it's beautiful.

- Jussi

--
Jussi Hirvi * Green Spot
Topeliuksenkatu 15 C * 00250 Helsinki * Finland
Tel. & fax +358-9-493 981
Mobile +358-40-771 2098 (only text messages)
[hidden email] * http://www.greenspot.fi/


--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: Advise on tracking changes

Greg Willits-2
In reply to this post by Jolle Carlestam

On Jul 5, 2007, at 1:03 AM, Jolle Carlestam wrote:

> I'm interested in some advise on how to handle a need for tracking  
> changes made to a record. Would you create a new record for every  
> update with a link to the original record and flag the old as "old"?
> Or would you have a "log" table for changed field content?
> Or a field in the table containing changed info?

Choosing one of these depends on the end purpose IMO. One method is  
to write the updated record twice. Once in the main table, and once  
in what is essentially a log. A mirror of the original table plus  
some extra tracking fields. The other method is to write only the  
fields that changed in a one to many fashion where one record per  
changed field is written.

Either can construct a point in time, so it comes down to how you  
want to query the history in order to present answers to the  
questions that probing the history is all about.

If you need to present the entire record as it existed as of 4pm on  
June 17, then having a complete mirrored record handy makes that very  
easy and efficient. With such a table, showing what's different is  
harder (the field by field comparison has t be churned through).

However, if you specifically need to show what was _changed_ on June  
17, then logging only the changes makes the most sense. To show a  
complete record you'd have to build one up from the entire history.

Also where is the burden o performance most important? Minimize  
impact on the main application and allw history probing to do more  
work when needed, or make history queries as efficient as possible?

Of course, you could do both? Asyncronously. There's been some  
interest in having these abilities in one of my apps, so I'm thinking  
of building these into pb. Aside from some "logging" standards that  
write both versions of histories above, I'm thinking of a generic  
"mask" routine that takes two data sets and spits out what's  
different using some ideas from code oomparitors.

-- gw
-----------------------------------------------------------------------
www.araelium.com/aredit/ae_and_lasso :: A new, Lasso-friendly, OS X
specific project manager and code editing development application.
www.pageblocks.org :: A comprehensive application framework for Lasso.
-----------------------------------------------------------------------



--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: Advise on tracking changes

Jolle Carlestam
5 jul 2007 kl. 11.07 skrev Greg Willits:

> On Jul 5, 2007, at 1:03 AM, Jolle Carlestam wrote:
>
>> I'm interested in some advise on how to handle a need for tracking  
>> changes made to a record. Would you create a new record for every  
>> update with a link to the original record and flag the old as "old"?
>> Or would you have a "log" table for changed field content?
>> Or a field in the table containing changed info?
>
> Choosing one of these depends on the end purpose IMO. One method is  
> to write the updated record twice. Once in the main table, and once  
> in what is essentially a log. A mirror of the original table plus  
> some extra tracking fields. The other method is to write only the  
> fields that changed in a one to many fashion where one record per  
> changed field is written.

Interesting and well analysed as usual, Greg.

The first method I can see how I implement. But for the second  
method. Write changes to a table with one record for every changed  
field. What would be the easiest way to find out what fields that  
really changed?

HDB
JC

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: Advise on tracking changes

Johan Solve
On 7/5/07, Jolle Carlestam <[hidden email]> wrote:
> The first method I can see how I implement. But for the second
> method. Write changes to a table with one record for every changed
> field. What would be the easiest way to find out what fields that
> really changed?

A simple way is to simply store the sql statement that was used for
each update in a log table. It can be captured using action_statement.
That way it's simple to roll back to any point in time, no matter what
field was changed, but of course you will not get the details of
exactly what was changed.
It can also be used to create a temporary mirror record, by modifying
the sql statement slightly so the roll back is applied to a new blank
record instead of the original record, and this new mirror record can
be used to generate a detailed comparsion against the "real" record.

Maybe a nice feature of Knop could be to have it generate a list of
fields that have been modified after an update, along with the "old"
and "new" values. Ooops, did I say Knop? ;-)
--
     Johan Sölve    [FSA Partner, Lasso Partner]
     Web Application/Lasso/FileMaker Developer
     MONTANIA SOFTWARE & SOLUTIONS
http://www.montania.se   mailto:[hidden email]
 (spam-safe email address, replace '-' with 'a')

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: Advise on tracking changes

Jolle Carlestam
5 jul 2007 kl. 12.19 skrev Johan Solve:

> Ooops, did I say Knop? ;-)

You said A. The crowd is hoping to also hear B... :-)

HDB
JC

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: Advise on tracking changes

Greg Willits-2
In reply to this post by Jolle Carlestam

On Jul 5, 2007, at 2:31 AM, Jolle Carlestam wrote:

> 5 jul 2007 kl. 11.07 skrev Greg Willits:
>
>> On Jul 5, 2007, at 1:03 AM, Jolle Carlestam wrote:
>>
>>> I'm interested in some advise on how to handle a need for  
>>> tracking changes made to a record. Would you create a new record  
>>> for every update with a link to the original record and flag the  
>>> old as "old"?
>>> Or would you have a "log" table for changed field content?
>>> Or a field in the table containing changed info?
>>
>> Choosing one of these depends on the end purpose IMO. One method  
>> is to write the updated record twice. Once in the main table, and  
>> once in what is essentially a log. A mirror of the original table  
>> plus some extra tracking fields. The other method is to write only  
>> the fields that changed in a one to many fashion where one record  
>> per changed field is written.
>
> Interesting and well analysed as usual, Greg.
>
> The first method I can see how I implement. But for the second  
> method. Write changes to a table with one record for every changed  
> field. What would be the easiest way to find out what fields that  
> really changed?

At some point you just have to brute force compare each field to know  
if it was changed. The difference in the two methods is that you  
either do that before the log is written (in order to create it) or  
after the log is written (in order to display a report).

Given the way pb's query system works, it'd be quite easy to add  
these capabilities for table updates. Assuming we're talking about  
forms, I would pass the original query result which loaded the form  
(always in at least a named inline in pb) along with the POST data to  
a tag that ran asyncronosouly to write the log.

When I have have high write logs like this (pb already automatically  
tracks logged in users, and logs page visits and database actions), I  
keep the logs in at least a separate database, preferably on a  
seperate disk, and even a separate server so that it can be tuned or  
high write volume w/o interfering with the availability of the  
primary database.

If we're talking about tracking updates that are driven by domain  
logic where there's no initial query to capture the original data  
state, then I think you're pretty much stuck inferring changes  
through differentials between log entries.

So maybe the lowest common denominator is simply to keep mirrored  
records so that the deltas can be inferred through comparison on  
demand. That way the same tools can be used to find those differences  
whether the data was updated via user/form or business logic. If you  
did that along with a log that generated statements as to why data  
was being updated (pb's log tools could do this), you'd have a pretty  
good history system. And if the logs wrote the current user ID, then  
they could be linked to a user breadcrumb trail (like pb's authlog),  
and you'd have a total clickable system to track who was in and  
exactly what they did. Hmm....


-- gw
-----------------------------------------------------------------------
www.araelium.com/aredit/ae_and_lasso :: A new, Lasso-friendly, OS X
specific project manager and code editing development application.
www.pageblocks.org :: A comprehensive application framework for Lasso.
-----------------------------------------------------------------------



--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/