I have a CRUD app for <DomainObject>s. They are persisted in a database.

There is a new requirement: keep track of the <DomainObject>s that have been created since the app was opened, so that the user can see what he/she has done.

I see three ways to implement this:

  1. every time the app is opened, create a new sessionid (for lack of a better name) and store it in the database. Store the sessionid as a foreign key for each <DomainObject>. Use a custom query to access the data (i.e. select * from DomainObject where sessionid = "currentSessionId")
  2. same as 1., except perform the filtering in the business layer
  3. don't keep track of this data in the database. Instead, have the model keep track of the new <DomainObject>s

The problem: I don't have enough experience to understand the consequences of each approach. For example, it seems like 1. puts business logic in the database. But 3. doesn't persist some of the data.

What are the pros and cons of each approach?


3 Answers 3


Maintaining an Audit table of denormalized DomainObject records with various user actions is the best bet.

By flattening the object tree of DomainObject and storing all the data in an Audit table with user information and timestamps, then you have a complete history of all changes to DomainObject's regardless of Create, Update or Delete operations.

By flattening the data in the Audit table you ensure that if children of DomainObject are deleted or modified then you will also have a history of what those children were as of the given timestamp and all of that without violating any foreign key constraints.


There is a new requirement: keep track of the s that have been created since the app was opened, so that the user can see what he/she has done.

I Don't fully understand you three ways But the correct way to do this is as follows. Every table in your system (The you want yo track) should have the following four fields on them:


These fields should be updated by your business layer based on various events (OnInsert, OnUpdate ect..)

If you need more logging create an Audit table with the Structure similar to the following:


Data should be inserted into this table from your business layer (you should have some mechanism for marking the fields that you would like to have changes logged for, so you are not needlessly logging everything)

EDIT : I reread you question and better understand it now. Keep with the model I am showing above. When the user logged in Store the UserID and CurrentTimeStamp in session.

Then you can :

Select x from Table 
where Userid=[SessionUserID] and ModifyDateTime>=[SessionTimestamp]

Note: you don't need to check CreateDate because you ModifyDate should be populated on create.


All the projects I work on have audit data like you describe and its all handled through audit tables/composite keys with version numbers that are managed through SPs. It may seem like you are putting business logic in the database, but rules for maintaining history are more data management issues than true business logic and databases exist to manage data.

  • Should be in a trigger - an sp won't always get called
    – HLGEM
    Commented Mar 16, 2012 at 19:25
  • @HLGEM it will if sps are the only way to access db
    – Ryathal
    Commented Mar 16, 2012 at 20:11
  • No you can't depend that they are. Auditing espcially can't depend on that because part of its purpose is to catch changes that aren't made through the official process.
    – HLGEM
    Commented Mar 16, 2012 at 20:18
  • Maybe I need to clarify the requirement: the user isn't worried about keeping an audit history of the table long-term -- the user just wants to be able to see what <DomainObject>s have been added since he/she started the program.
    – user39685
    Commented Mar 16, 2012 at 20:33
  • That model Morons provided does give you that though since you can simply log when the application was run and query against your auditing log.
    – Rig
    Commented Mar 16, 2012 at 20:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.