Re: How to normalize this?

On 2013-05-02 10:40:28 +0000, Erwin said:

> Op donderdag 2 mei 2013 03:35:15 UTC+2 schreef com..._at_hotmail.com het volgende:

>>> Op dinsdag 30 april 2013 23:39:58 UTC+2 schreef Jan Hidders het volgende:

>>> 

>>>> On 2013-04-30 14:21:47 +0000, Erwin said:

>>>>>> R1(a, b, c, d)

>>>>>> R2(a, b, e)

>>>>>> R3(b, c, e)

>>>>> In particular, that at all times R1 JOIN R2 === R1 JOIN R3.

>>>> But on the other hand, this type of restriction is often an>> > > 
>>>> artifact of the initial table design

>>>> And the getting rid of it is actually a feature and not a>> > > bug.

>>> It's often bothered me that normalization theory/procedure seems to 
>>> quietly ignore the notion of "nullability or not" of any of the 
>>> attributes in the "initial table design" ...

>>> 

>>> Iow, that normalization theory per se doesn't actually allow to 
>>> determine when and when not the phenomenon is "an artifact of the 
>>> iniital table design".

> 
> You've characterized my botherings with normalization stuff very well.
> 
> However, I'd argue that there is indeed "a phenomenon", and it's 
> exactly the phenomenon you described as the "...a...b...c...d...some 
> e..." vs. "...a...b...c...d..." confusion.
> 
> Jan observes that in practice most of the time, making this "mistake" 
> leads people to "discovering" the correct predicates for the business 
> problem at hand, and seems to be on the side that therefore it must be 
> a good thing, I personally am rather bothered with the fact that a 
> theory would be useful because misunderstanding it or only partially 
> understanding it, in practice mostly leads to correct designs being 
> derived from less correct ones ...

Well put, and I think we are not that far apart in these matters. I'm certainly not in favor of applying the normalization algorithms blindly and without understanding what it is that they do and do not achieve. Understanding that the usual normalization algorithms do not necessarily result in an equivalent schema is part of that. But most textbooks I know are correct and clear on this and will state that the goal is for example a lossless-join and dependency-preserving decomposition.

A question I often ask to see if people really understand normalization is the following:

The reason that is usually stated for not normalizing all the way up to BCNF and stopping at 3NF is that you cannot always reach BCNF while remaining dependency preserving. However, suppose we follow the following procedure:

1. Apply the 3NF normalization procedure we discussed earlier
2. Include with each component only the FDs that generated that component

This gives you a lossless-join dependency-preserving decomposition. And each component clearly is in BCNF for its included FDs. But wait, is that not exactly the thing that was stated as impossible? So what gives here?

> (I guess this must also be the reason why Fabian Pascal insists that if 
> you do the conceptual modeling properly from the get-go, you won't even 
> need normalization at all.)

In practice that is often true, but on the other hand it is theoreticallly possible that even in a correct conceptual model there are some normalization issues left. And apart from that I would argue that normalization theory is important to understand what happens if you start denormalizing in your implementation. And in practice it happens that you have to start working on an existing system where you need the theory to determine if and why the exisiting implementation is problematic, and if it is what you can do about it.

• Jan Hidders