Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
    
Should be in a trigger - an sp won't always get called –  HLGEM Mar 16 '12 at 19:25
    
@HLGEM it will if sps are the only way to access db –  Ryathal Mar 16 '12 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 Mar 16 '12 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. –  Matt Fenwick Mar 16 '12 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 Mar 16 '12 at 20:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.