Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
alfi
Starting Member
2 Posts |
 Posted - 2008-12-12 : 10:25:46
|
| I want to implement a prototype table that contains the following fields created_date created_by modify_data modify_by and Obj status that will be shared with all my other tables. I want to save the retyping those fields to each table separately, it seams like a great idea reducing redundancy and all, but it comes with the price of creating a join to what can be a very large table that in time will accumulate the number of records in all my database tables ( the ones at least I want to track with those attributes) . First I would like to know if someone already experienced with this architecture and can share his/her thoughts regarding performances issues. And The idea of implementing this oo approach and having all the tables have constraints to one table.thanks |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-12-12 : 23:32:09
|
| It can be a good idea but not for redundancy, space saving or typing reasons.What you almost describe is a business transaction table. It's going to be a bit hard to walk through without your full requirements and motivation for doing this but what I normally do is something along the lines of:Business transaction table with id, transaction type, date, time, user etc.The fist part of any operation is we add a row here and keep the ID. All rows in the main data tables that are affected for a given business level (which often corresponds to a db transactional level) operation get tagged with the transaction ID. That way you can reconstruct a transaction working out exactly what happened, when and who did it.You will need some modifications to this depending on your requirements. 2 popular additions are 1) keeping a parallel table of before the changes (i.e. a complete history of your DB and you never delete anything)2) Have a 'previous transaction number' in the transaction table so you can at least follow the chain although obviouslly you lose your old values unless you do 1 as well.There's nothing to stop you having a trigger based timestamp on the rows either, although I often save that for the history table rather than the table itself.For this to work effectively you need a certain amount of rigour in how you process transactions, such as use of SPs etc. You won't be able to log random hacks into the tables but allowing that is poor design anyway.HTH |
 |
|
|
|
|
|
prototype table