Design pattern for logging changes in parent/child objects saved to database

Question

I’ve got a 2 database tables in parent/child relationship as one-many.

I’ve got three classes representing the data in these two tables:

Parent Class
{
    Public int ID {get; set;}
    .. other properties
}

Child Class
{
    Public int ID {get;set;}
    Public int ParentID {get; set;}
    .. other properties
}

TogetherClass
{
    Public Parent Parent;
    Public List<Child> ChildList;
}

Lastly I’ve got a client and server application – I’m in control of both ends so can make changes to both programs as I need to.

Client makes a request for ParentID and receives a Together Class for the matching parent, and all of the child records.

The client app may make changes to the children – add new children, remove or modify existing ones. Client app then sends the Together Class back to the server app.

Server app needs to update the parent and child records in the database.

In addition I would like to be able to log the changes – I’m doing this by having 2 separate tables one for Parent, one for child; each containing the same columns as the original plus date time modified, by whom and a list of the changes.

I’m unsure as to the best approach to detect the changes in records – new records, records to be deleted, records with no fields changed, records with some fields changed.

I figure I need to read the parent & children records and compare those to the ones in the Together Class.

Strategy A:

If Together class’s child record has an ID of say 0, that indicates a new record; insert. Any deleted child records are no longer in the Together Class; see if any of the comparison child records are not found in the Together class and delete if not found (Compare using ID).

Check each child record for changes and if changed log.

Strategy B: Make a new Updated TogetherClass

UpdatedClass
{
    Public Parent Parent {get; set}
    Public List<Child> ListNewChild {get;set;}
    Public List<Child> DeletedChild {get;set;}
    Public List<Child> ExistingChild {get;set;} // used for no changes and modified rows
}

And then process as per the list.

The reason why I’m asking for ideas is that both of these solutions don’t seem optimal to me and I suspect this problem has been solved already – some kind of design pattern ?

I am aware of one potential problem in this general approach – that where Client App A requests a record; App B requests same record; A then saves changes; B then saves changes which may overwrite changes A made. This is a separate locking issue which I’ll raise a separate question for if I’ve got trouble implementing.

The actual implementation is c#, SQL Server and WCF between client and server - sharing a library containing the class implementations.

Apologies if this is a duplicate post – I tried searching various terms without finding a match though.

---

Update based on comments from svick and TimG:

To track change would the base class be something like:

public class ChangedRowBase
    {
        public enum StatusState
        {
        New, Unchanged, Updated, Deleted
    };

    protected StatusState _Status;

    public StatusState Status
    {
        get
        {
            return _Status;
        }
    }

    public void SetUnchanged()
    {
        _Status = StatusState.Unchanged;
    }

    public void SetDeleted()
    {
        _Status = StatusState.Deleted;
    }

}

Then my Child class would have something like:

    public int SomeDumbProperty
    {
        get;
        set
        {
            base.Status = StatusState.Updated;
            // Missing line here to set the property itself
        }
    }

    private int _SomeIntelligentProperty;

    public int SomeIntelligentProperty
    {
        get { return _SomeIntelligentProperty; }

        set
        {
            if (value != _SomeIntelligentProperty)
            {
                _SomeIntelligentProperty = value;
                base.Status = StatusState.Updated;
            }
        }
    }

The idea being that the intelligent one detects if there has indeed been a change; otherwise using the dumb one the client app would need to either detect change and only update if there has been a change, or use the setter anyway flagging a change which actually didn't occur.

I also know that my dumbproperty doesn't work (or compile) right now. If I want code in the getter or setter, does this mean I need to define a separate private variable myself ? I know the short hand {get; set;} does this behind the scenes.

Answer 1

Here are common design patterns to handle these scenarios.

Set up a base class. Child and parent will inherit from base. Base class should do the following:

1. Have a .Status property (read-only) that tracks if an object is {unchanged, new, updated, deleted} The status is set during {constructor, .delete() method, each Property's setter}. You will need a factory method to generate an object instance with a status of "unchanged" or a method to mark itself as "unchanged".
   btw. This approach makes an object more "atomic" because it is self-contained and it keeps track of its own changes. This is also an example of why people use Properties (getters/setters) instead of public vars.

2. A commonly used pseudo locking mechanism is to use a combination of .LastModifiedDateTime (read only) and .LastModifier (read-only) columns on each DB table (non code-table/static-table). These are also set in the constructor/factory method. On Update/Save, compare the current LastModifiedDateTime & LastModifier to the DB. If they don't match then reject this change.

#1 is similar to the way many ORMs maintain state (handle changes). #2 is often referred-to as a "soft locking mechanism".

Answer 2

I think a good solution here would be for the client model to detect changes to itself and then send to the server only the changes.

This means that setters of your properties will have to at the very least record that something changed. And collections can't be simple List<T>s, but something that can have custom reaction to changes. (You can use Collection<T> with overridden methods like InsertItem() for this.)