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.
.