A littel help with table design....

Hi all,

I have a fairly tricky problem that I'm not sure how to approach.

I'm making a web application that manages drug trials. One of the requirements of the system is if anyone makes changes to a field, the old value and the new value need to be stored, along with the time of the change and the reason for the change

The problem is I don't know how to support this for all the various fields in all the various tables.

For example I have tables for storing basic patient details and then tables for storing data on patient visits, patient screening data and so on.

Can anyone suggest how I could make a table or tables to store this audit data for all the fields in all the tables? I'm not sure how to do it!

:-(

Thanks to anyone who can help

SimonPersonally I'd do that logic in the objects rather than the DB. But if you want to use the DB then you need to create an Audit Table(s). Then use a trigger to write the values and a timestamp into the Autit table whenever the value changes.|||I need to do the similar thing.
I created second database as the log for the main one. It has all the table as the main one, and with additional fields to save userId, updata type, update date/time, etc.
Trigers are added to the main tables to insert old value into log database.
I appreciate it if anybody in this forum has better idea.