Re: Multiple-Attribute Keys and 1NF

From: Bob Badour <>
Date: Tue, 28 Aug 2007 13:43:49 -0300
Message-ID: <46d4507b$0$4053$>

JOG wrote:

> On Aug 28, 5:05 pm, Bob Badour <> wrote:

>>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
>>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.
> Well, practically, the surrogate key is the way that I would go. My
> question is rather whether this corresponds naturally to the original
> propositions, which don't require a new domain in order to be
> manipulated in FOL.

You assume a color domain so imagining a different color domain changes the design without adding anything new.

   I seem to remember a while back there was a

> discussion involving Marshall and a few others considering situations
> where a nested relation was /necessary/ (I need to have a dig for it),
> and it didn't sit comfortably then.

I would argue that nested relations are never necessary; although, they are certainly handy at times. I would choose the discipline of base relations having no nested relations. In fact, the princicple of cautious design suggests--as tools evolve toward nested relations--to allow them only in derived relations. Received on Tue Aug 28 2007 - 18:43:49 CEST

Original text of this message