| Subject | I need your opinion about table design. |
|---|---|
| Author | un_spoken |
| Post date | 2012-10-11T07:02:49Z |
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.
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.
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 te 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!?
Thank you for your answers.
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.
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.
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 te 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!?
Thank you for your answers.