>>Hi guys.
>>
>>When I came to work for my current company, I saw something that I had

never seen before

>>(Back then I had 5+ years of experience working with various SQL Server

DBs):

>>
>>on each table in database there is a trigger which is fired up before

updating the row.

>>The trigger makes a copy of the updated row and marks it as inactive by

setting field

>>RECORD_ACTIVE = 0. I think that this copy-on-update model is very..very

bad.

>>
>>There is one and only one reason of making such copy, data safety - it

means that we have

>>history for row changes. We are able to tell what and when had changed.
>>
>>Here are my reasons against it:
>>1. Decreased performance when making updates. In fact, we had to introduce

a check in trigger

>>to not make a copy of record if we are importing and updating data via

import tool because

>>it worked to slow. So I think the whole point of data safety is lost

anyway because we can

>>not keep the data safety policy everywhere.

Reality conquers theory. Though if you keep the files you are importing,
then I wouldn't say the whole point of data safety is lost, you just have to
look in the imported file rather than in the database if you need to look
back in history (which can be cumbersome, but the data is there).

>>2. Increased table size - decreased performance when reading data.
>>
>>3. We have in each table the fields that saying which user( and when)

introduced last change

>>to the record. I think it is sufficient. Our software is not medical

software or banking

>>software, we do not keep so crucial data. There wont be any law suits when

some user will

>>accidentaly change some row. And after all, it is his duty to know what is

he changing.

Maybe management should decide what to keep? One thing is small accidental
changes, but what about changes done to several records due to an employee
not understanding how things should be done? Or an employee trying to steal
from your company (maybe delete customers because he wants to start his own
competing company and contact those customers himself).

>>4. Due to this design I am not able to introduce an unique constraint on a

column. For

>>example : often there is a need to add unique constraint on a NAME column

- I am not able

>>to do this because constraint will fail for the first copy of the given

record. (If I

>>create constraint on NAME and RECORD_ACTIVE - constraint will fail on

second copy.)

>>
>>5. As long as I am working here there was never a need to check for the

history of a record

>>in any database. And we have like 100 or more clients...
>>
>>So guys. I am asking you for a fresh point of view. Should I go to the

menagement and tell

>>them that this is generally a bad idea or maybe I am wrong? Maybe this is

the way it should

>>be done!?

>I don't think there is a generally right or wrong solution to this

question, except in extreme cases where you can guarantee that history is
irrelevant or know that history is as important as the present. It sounds a
bit >over the top to have this on EACH table in your database, e.g. if you
have a COUNTRIES table, then I'd say history is probably irrelevant.

>We do things slightly different that might be a bit more flexible. For our

three main tables, we have AFTER UPDATE and AFTER DELETE triggers that
copies the old values into other tables that have similar >structure with a
few additional fields. For most other tables we do not keep any history
(well, except daily backups, of course). Such a solution would allow you to
have a constraint on NAME in the main table, but >not on the table with old
values (though you might not want to constrain NAME, in many situations you
could experience the same name not being unique - e.g. there are two
countries named Congo). It wouldn't >help speed-wise regarding imports of
UPDATEs, but INSERTs wouldn't copy anything.

>As for talking to your management, it might be wiser to come up with an

alternative and say "I've got an idea, let's do it this way" rather than
just saying "I really don't like our system".

>HTH,
>Set

There is possibly different reason for such design:
You create an invoice, print it and send it to the customer. After some
time, customer moves to another address.
If you print old invoice again it should be the same as the first time.
The easiest way to achieve this is to keep the history of customer's
attributes under the same ID as it is recorded in the invoice data.
New version of Customer's attributes, gets new ID.
This behaviour can be , of course, achieved in various ways.
Regards
Bogdan









[Non-text portions of this message have been removed]