Re: Normalization question

Steve Kass <skass_at_drew.edu> writes:
> The example here in fact is very similar to some popular schemes
>for image compression. Recording just the changes is a lossless
>compression scheme. And the parallel goes a bit further, in that
>both for a .gif image and for the table in the compact form Erland
>suggested (no End_dt), there are similar disadvantages. If a row of
>the table is corrupted or lost, a potentially large amount of information
>can become irretrievable. And without corruption, the information itself
>becomes harder to retrieve (to wit Erland's use of both
>compressed and uncompressed tables, depending on the situation).

That reminds me of a quite horrendeous war story. We had a table in our systems for a certain type of objects call them bananas. Then we introduced a somewhat different slant of banaas, which result in the three tables:

    bananas - the virtual subtable with all bananas     yellow_bananas - the old kind of bananas we always had supported.     red_bananas - the new kind of bananas.

The yellow_bananas and red_bananas tables are very different. 95% of all bananas are in the yellow_bananas tables, and at sites that don't deal with red_bananas 100% of all bananas are in the yellow_bananas table. Furthermore, the red_bananas table only has the columns specific to the red bananas, where as yellow_bananas duplicates many of the columns in the main table (as some of the column in the bananas_tree table) of legacy reasons.

What happened at one site was that the bananas table became corrupt. This site does not have red bananas, so we were able to fully reconstruct the bananas table from yellow_bananas table. And I have to say it scared me. Sure, at this incident it was good, but since the bananas tables are transaction-intensive, that means that our system is doing a lot of extra work.

Quite recently, I have arrived at the conclusion that introducing the supertable was a mistake. After all, we have already squeezed in blue bananas in the yellow_bananas table, and they are actually even stranger creatures than the red ones. So the idea now is to go back to the original single-table concept.

-- 
Erland Sommarskog, Stockholm, sommar_at_algonet.se