Best way of recording activity from a user in the database?

Question

Hi,

I am building an application that perform checks on users on specific days. I need to keep track of how many times these checks are performed against that particular user.

My initial thought was to just basically have a field in one of my tables which I just increment when the checks have been performed. My other thought was to create a table called "AuditUser" that will insert a new record each time the customer has been checked.

This is really a question of...What is the best practise approach?

If anyone else has a better solution please suggest.

Thanks in advance.

Answer 1

Use an audit table.

Then you can date partition and also archive to disk and shrink the table as needed.

Answer 2

use the AuditUser table, you will then have a record of each check (with date+time), which is better than just a count and a last date+time.

Answer 3

If you will only ever need a count, I would go with the field that you increment.

If you are ever going to need to do anything else, such as determine the number of times checked within a range of dates, then I would use the second table.