Denormalising?

[zingmatter]

We're rebuilding our site, including the database. We want to improve the database by introducing foreign key constraints to all our tables.

The argument brewing amongst the developers is to do with delete of certain records. Strictly speaking we don't actually want to delete the record but just effectively remove it from "display". Currently we've been doing this (for a small number of record types) by negating the foreign key, i.e. a record associated with a certain userid 12345 (the foreign key) gets changed to -12345. That way, if we need to restore the record we can simply look for the negative version of this key. But this would break a fk constraint and effectively denormalises the database.

I would prefer to have a boolean "active" field on the table so that we just set this to false in order to acheive the same effect. The downside is more server load and queries will always have to include "... AND Active = 1 .." .

Does anyone have any experience of this kind of thing? Is the method of "denormalising" by negating a fk field such a bad thing? Are there significant benefits from keeping the database as pure as the driven snow in a practical sense (not just in the minds of purists and academics)

Any thoughts greatly appreciated. Thanks.

[russell]

your preferred method is the right one. the other is utter nonsense.

adding a bit field to flag records as active/inactive is extremely common. it adds almost no overhead as it is the smallest possible data type.

changing the value of the data completely defeats the purpose of referential integrity and is purposely introduing bad data into your system. it is a dengerous path to go down. use your idea of the bit flag.

then take the people who want to modify the data out to the parking lot and tell em not to come back :)

by the way, what you describe is not "denormalizing" denormalization refers to flattening out a normalized database structure -- combining the data from many tables into few. or few into one. What you describe is breaking referential integrity.

edit and no, this is not an academic exercise. i manage very large databases for some pretty large companies and i'd be out of work pretty quickly if i suggested doing that...

[zingmatter]

As I thought - relevant parties taken to said parking lot (and pushed into the nearest canal, just to be sure :eek: ).

Thanks

[Ribeyed]

Hi,
just a suggestion but why don't you make an Archieve database for your main database then create INSERT, DELETE and UPDATE triggers for your main tables (not your lookup tables) to archieve your data and the delete the record from your main database. Tat way you have a record of everything that has happened in your main database.

[zingmatter]

Hi

(Also an NHS developer!!) we thought of that although the database is automatically backed up every 4 hours (total backup every 2 days) so in theory we can restore deleted data in most cases. The problem is the sheer size of the data that would have to be stored just for the occasional retrieve.

The way we're going is probably to put an active field on the handful of tables where erroneous deletes might occur and for all other instances we just have a lot of - "are you sure you want to delete this?" - "are you REALLY sure?" - " there's no going back if you delete this..." - "well if you're definitely going throught with this..." - "is this your final answer" - [delete confirmed] - "arrggggghhhh - THAT'S IT, YOU DONE IT NOW, THE RECORD IS DELETED - so please don't now going bothering support to ask for it to be undeleted". Whereupon they immediately contact support to ask for it to be undeleted.

That's the biggest problem with web sites - people are allowed to use them...

[russell]

Whereupon they immediately contact support to ask for it to be undeleted.

That's the biggest problem with web sites - people are allowed to use them...

LOL

[mattyblah]

If you had an active/archive database/tables, and you allowed them to delete/archive, why not allow them to restore the records also?

[zingmatter]

In some cases they will - again a lot easier to do by toggling a single boolean field rather than trying to fish it out of an archive db.

[mattyblah]

It might be easier to toggle a single boolean field but what if 9/10th of the data is archived? Now your table is 10 times the size it needs to be because you don't want to fish out of an archive db. Just a different perspective.

[russell]

true, for essentially obsolete data, best to pop into an archive database. this will help keep your oltp system flying.