let's say I have these tables:
customers
orders
orderdetails
products
then after the order is placed, the customer changes his mind and wants a book instead of a CD.
What's the best way to track these kinds of changes that can happen to all tables? I've thought of 2 ways:
#1 - track all changes in another table like this:
trackchanges(
id int NOT NULL,
tablename,
olddata,
newdata,
datetime,
primary key (id)
)
#2 - duplicate all tables and insert into those tables each time anything has changed. in other words, I'd introduce this set of new tables:
trackchanges2customers
trackchanges2orders
trackchanges2orderdetails
trackchanges2products
#2 will make the DB a lot bigger, but tracking things seem to be easier because I have a real log of what has changed and there is less coding overhead vs #1.
#1 will require that i insert to that table for each change of each field. It's not that complicated to do so, but I still consider it overhead (time, effort).
Any thoughts, suggestions or comments?
customers
orders
orderdetails
products
then after the order is placed, the customer changes his mind and wants a book instead of a CD.
What's the best way to track these kinds of changes that can happen to all tables? I've thought of 2 ways:
#1 - track all changes in another table like this:
trackchanges(
id int NOT NULL,
tablename,
olddata,
newdata,
datetime,
primary key (id)
)
#2 - duplicate all tables and insert into those tables each time anything has changed. in other words, I'd introduce this set of new tables:
trackchanges2customers
trackchanges2orders
trackchanges2orderdetails
trackchanges2products
#2 will make the DB a lot bigger, but tracking things seem to be easier because I have a real log of what has changed and there is less coding overhead vs #1.
#1 will require that i insert to that table for each change of each field. It's not that complicated to do so, but I still consider it overhead (time, effort).
Any thoughts, suggestions or comments?