October 24, 2008 at 3:03 am
Hi all,
Need your advice on something, I have some four tables, for this question lets say that they are;
Customer - stores the basic information on the customer (customer id, Type)
CustomerType - Lookup table, joins to the customer table. only a few records in it, such as 'Residential','Business' etc.
CustomerContact - The contact details of the customer - at least one record per customer
CustomerOrders - The order details of the customers - multiple records per customer changing all the time as orders are changed.
(these are made up tables for the purpose of this question).
Ok, well if I wanted to audit changes to these tables what method would you implement;
1. An archive/audit table for each, using a trigger to write to the table when a change occurs on the main table.
I.e a change occurs on the CustomerOrder table, trigger fires and writes to the ArchiveCustomerOrder table.
or
2. A generic table that captures the id values of the table, column and what changed and then all data gets written to it. Meaning a common solution, but a massive table and a bit of a nightmare to use in a report capacity
or
3. Something else?
I favour the first approach as it seperates out the data into logical audit history and is fairly quick and easy to report on but I' open to new idea or views.
Hope this all makes sense.
Jackal
October 24, 2008 at 3:24 am
Jackal (10/24/2008)
Hi all,Need your advice on something, I have some four tables, for this question lets say that they are;
Customer - stores the basic information on the customer (customer id, Type)
CustomerType - Lookup table, joins to the customer table. only a few records in it, such as 'Residential','Business' etc.
CustomerContact - The contact details of the customer - at least one record per customer
CustomerOrders - The order details of the customers - multiple records per customer changing all the time as orders are changed.
(these are made up tables for the purpose of this question).
Ok, well if I wanted to audit changes to these tables what method would you implement;
1. An archive/audit table for each, using a trigger to write to the table when a change occurs on the main table.
I.e a change occurs on the CustomerOrder table, trigger fires and writes to the ArchiveCustomerOrder table.
or
2. A generic table that captures the id values of the table, column and what changed and then all data gets written to it. Meaning a common solution, but a massive table and a bit of a nightmare to use in a report capacity
or
3. Something else?
I favour the first approach as it seperates out the data into logical audit history and is fairly quick and easy to report on but I' open to new idea or views.
Hope this all makes sense.
Jackal
Triggers approach
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply