Re: Nearest Common Ancestor Report (XDb1's $1000 Challenge)

On Sat, 22 May 2004 12:12:34 +1000, thirdrock <iktaccounts at optusnet dot com dot au> wrote:

(big snip)
>>>> This example of a bad design has redundant storage of the same
>>>> relation.
>>>
>>> Er.. actually it's an example of non-normalised data. I don't think
>>> design
>>> has anything to do with it.
>>
>> Huh? Normalizing *IS* one of the steps you take when you design the
>> schema
>> of a relational database. And the aim of normalizing is to remove
>> redundant relationships (not relations) from the design.
>
>True. However, sometimes your design will include data that is not
>normalised for reasons of avoiding unneccessary overhead or to produce
>certain types of behaviour (which I won't go into in this post).
>Therefore, non-normalised data *can* be poor design, but it not
>neccessarily so. It really depends on the context.
>My point is (and I do have one) that the generalisation that
>non-normalised data is bad design is not always true, and in fact is
>probably the wrong way around. Good relational design will, as a general
>rule, produce normalised data, but the inverse is probably not generally
>true.

Yes, you are correct. There may be very valid reasons for violoating normalization rules in the design of a relation database. A testbook I once read made a distiction between DEnormalized design (where the data is normalized first, then denormalized if there is a valid reason to do so) and UNnormalized design (where the designer never bothered to normalize): the UNnormalized design might even be exactly the same as the DEnormalized design, but the former only got it right "by accident".

This is of course a generalization (really good designers might be able to produce the correct design directly, without writing out the normalized design first), but it does show that violating normalization is not wrong in itself, but only if there is a good reason to do so. Never without thinking it over and consciously making the decision to denormalize.

>>>> You seem to be thinking that storing the character strings 'fido' and
>>>> 'leader' several times is bad.
>>>
>>> Bad, or just not normalised?
>>
>> If I understand Neo correctly, he thinks it is not fully normalised, AND
>> he thinks that anything that is not fully normalised is bad.
>
>Which you don't, I assume, so we are in agreement.

I don't. We are.

>> Hmmm, you're right. I did indeed forget to add "on update cascade" and
>> "on
>> delete cascade" to the "references" clauses in my original message (the
>> first one I posted in this discussion, four days ago).
>
>Better add that to collect the 1000 bucks :)
>
>>
>>
>>>> I don't know what definition of "redundant" you've been taught. I think
>>>> that "Exceeding what is necessary or natural; superfluous." (*) does
>>>> quite
>>>> accurately describe the schema with both thingID and thingName, not my
>>>> original schema!
>>>
>>> Well, writing a trigger to cascade out fido's name change to 'fifi' is
>>> redundant if your model was such that it was un-neccessary.
>>
>> Why write a trigger if you can use DRI?
>
>Exactly! Therefore my statement about it being redundant holds true, yes?

Writing a trigger if DRI actions can be used is redundant. If, for whatever reason, DRI actions can't be used writing a trigger makes perfect sense.

Best, Hugo

-- 

(Remove _NO_ and _SPAM_ to get my e-mail address)