|
|
Posted by Doug on 10/01/05 11:41
Off the top of my head, two fundamental ways.
Insert into the 4 tables, and have triggers on those tables for insert,
update, and delete that add rows to the trans table.
Two. utilize standardized stored procedures you use to add/edit/update
the 4 tables. Inside the stored procedures, do a begin trans/ commit
around the changes and the trans table. In this way, you can pretty
much ensure that it will keep up.
The stored procedure has the advantage of allowing more business logic
around changes. You can validate data changes external to the app.
However, you MUST enforce that all changes to the 4 tables MUST uses
the specified stored procedures.
The Triggers have the advantage of "simplicity." Again though,. you are
somewhat limited in what logic you can use.
Two comments you didn't ask for. Be very stingy with indexes on the
trans table. I'm not saying ot have none, BUT don't have a lot of
indexes. And the indexes you do have should be fairly unique. Trans
tables get very large very fast, and they really slow everything down
in a sneaky insidious sort of way in a few years.
Secondly, it can be hard to get the :"right" infomration into the trans
table. As a test to see whether you have all data you need, manually
try to take each trans record, and recreate the final data results. In
a perfect world, your trans table will allow a picture of exactly what
changed when and how, allowing hte final results to be seen.
For a final test, just go ahead and write the program that will read
the trans data and recreate teh table. This is almost never trivial,
but almost always worthwhile.
-doug
Navigation:
[Reply to this message]
|