What are the pros and cons of implementing this requirement in different layers?

Question

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?

Answer 1

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:

CreateDateTime
CreateUser
ModifyDateTime
ModifyUser

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:

TableName
RowID
FieldName
User
DateTime
OldValue
NewValue

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.

Answer 2

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.

Answer 3

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.