Design pattern for logging changes in parent/child objects saved to database
- by andrew
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.
