ArcPy or FME for adding history to an object?

Question

I have a very specific problem and I'm looking for a solution for it. I need to add some sort of history to an object. The history will be stored like this:

• begin_date
• end_date
• previous_id

Suppose the object that I'm wanting to add the history to is X. We have a central database (at the moment all mdb files, we will switch soon to gdb though) that contains the object X with the history. Workers on the field have a local version of the database and they also fill an object X_i that has an attribute changed. X and X_i have an id to track them.

Now when we want to merge the local version of the database a script should run and fill the history of the central db like this:

if X_i.changed = true:
    search central_X.id, fill end_date with today
    search local_X.id, copy this attribute to central_X and:
        fill begin_date
        fill previous_id with central_X.id
        generate new unique id for the central_X

We also add new fields to the central db and update the unique ids:

for every i in local_X:
    if i not in central_X:
        copy i to central_X
        generate new unique id for i
        enter begin_date

For deleted items we do something similar but then we set the end_date.

At the end of the merge. We create a new X_i where all the ids are correctly filled and the changed attribute is empty everywhere.

My question about this is:

I was thinking about doing this in ArcPy or FME because I have some experience with both. What do you think would be best, ArcPy or FME (or something else).

Can you point me in the right direction; what are the toolboxes, functions you would use for this?

I have looked at alternatives like an ArcSDE with archiving, but that would mean we would need to invest in an ArcSDE, change a significant part of our workflow and not all items that we change will have X_i.changed, so the archiving would contain way too much information to be really useful.

Edit: added workflow to clarify.

1. I can use changedetector for this.
2. If X_i.changed is true
3. If X_i.id is not empty
4. set end_date for all processed data
5. no idea how to do this
6. no idea how to do this
7. no idea how to do this
8. find the biggest id so far and proceed from this id.

Answer 1

FME with the TimeStamper http://www.fmepedia.com/index.php/TimeStamper

Use your previous_id and define the begin date

any changes to the data can be recorded with previous_id changes and current_id where the timestamper can be assigned and end_date.

Python can be used to record historic changes http://docs.python.org/library/time.html

Update after comment

http://docs.safe.com/fme/2009/html/Transformers/content/transformers/changedetector.htm