Re: Multiple-Attribute Keys and 1NF

JOG wrote:

> I am still fighting with the theoretical underpinning for 1NF. As
> such, any comments would be greatfully accepted. The reason for my
> concern is that there /seems/ instances where 1NF is insufficient. An
> example occurred to me while I was wiring up a dimmer switch (at the
> behest of mrs. JOG, to whom there may only be obeyance). Now I don't
> know the situation in the US, but in the UK a while back the colour
> codes for domestic main circuit wiring changed. Naturally the two
> schemes exist in tandem, as exhibited in every house I've had the joy
> of doing some DIY in:
>
> Brown -> live.
> Red -> live
> Blue -> neutral.
> Black -> neutral.
> Green and yellow -> earth.
>
> The issue with encoding these propositions is that the candidate key
> for each proposition may consist of one _or_ two colours. Now I have a
> couple of options, none of which seem satisfactory. I could leave
> green & yellow as some sort of set-value composite, but obviously this
> would affect my querying capabilities, so thats out straight off the
> bat. Similarly adding attributes Colour1 and a nullable Colour2 is
> simply so hideous it isn't worth consideration. So, I could ungroup to
> give me:
>
> Colour Type
> -----------------
> Brown live
> Red live
> Black neutral
> Blue neutral
> Green earth
> Yellow earth
> -----------------
>
> But again this is unsatisfactory as I have lost the information that
> one wire is green and yellow, but none is brown /and/ red.
>
> I could introduce a surrogate to give me:
>
> Id Colour Type
> -----------------
> 1 Brown live
> 2 Red live
> 3 Black neutral
> 4 Blue neutral
> 5 Green earth
> 5 Yellow earth
> -----------------
>
> But this seems wholly artificial given that all the information I
> required for identification was available in the original
> propositions, and that did not require some artificial id. A [shudder]
> non 1NF variation such as:
>
> Id Colour Type
> -----------------
> 1 Brown live
> 2 Red live
> 3 Black neutral
> 4 Blue neutral
> 5 Green, earth
> Yellow
> -----------------
>
> is clearly hideous as it denies the fundamental mathematical principle
> that that one attribute should take one value from one domain,
> nevermind the fact that it introduces query bias.
>
> I could of course introduce nested relations, but I am uncertain as to
> the theoretical consequences of having nested relation as a key (I
> guess it would be fine, if adding seemingly unnessecary complexity to
> subsequent queries). But moreover it again seems unintuitive, given
> that in this case it would indicating that the original propositions
> contained, as a value for one of their attributes, a further
> proposition, and this was not the case.
>
> I am having a crisis of faith with the way 1NF is currently viewed.
> Any ideas to solve my dilemma? Am I on my own in being perturbed?
>
> Regards, Jim.

There is one obvious way to represent that in 1NF:

Create a color domain where a single value represents green and yellow, another value represents green, and a third represents yellow etc. The domain could even represent thick green/thin yellow as a separate value from thick yellow/thin green if one chooses.

Regardless whether one creates only the domain or also uses it as a candidate key for some sort of lookup table, the resulting relation is simply:

Colour Type
======= -------
...

Your ID above is one example of such a domain. However, the domain need not be numeric or have any external numeric representations. It need only exist with a distinct value for green and yellow. Received on Tue Aug 28 2007 - 18:05:29 CEST