Re: A Question On Many-To-Many Linking Table(s)

In article <c0d87ec0.0209251018.5249d3f4_at_posting.google.com>, --CELKO-- <71062.1056_at_compuserve.com> wrote:
>
>The most common example of this kind of error is an adjacency list
>model for a tree which has both the tree structure and the node
>information embedded in it. Somehting that looks like this
>
>CREATE TABLE OrgChart
>(emp CHAR(10) NOT NULL PRIMARY KEY,
> boss CHAR(10) DEFAULT NULL REFERENCES OrgChart(emp),
> salary DECIMAL(6,2) NOT NULL DEFAULT 100.00);
>
>OrgChart
>emp boss salary
>===========================
>'Albert' 'NULL' 1000.00
>'Bert' 'Albert' 900.00
>'Chuck' 'Albert' 900.00
>'Donna' 'Chuck' 800.00
>'Eddie' 'Chuck' 700.00
>'Fred' 'Chuck' 600.00
>
> Albert (1,12)
> / \
> / \
> Bert (2,3) Chuck (4,11)
> / | \
> / | \
> / | \
> / | \
> Donna (5,6) Eddie (7,8) Fred (9,10)
>
>The first table is denormalized in several ways.

Er, the first table is in 5NF. Is there a special JCNF (Joe Celko normal form) you have in mind that goes beyond that? :-)

>Another problem with the adjacency list model is that the boss and
>employee columns are the same kind of thing (i.e. names of personnel),
>and therefore should be shown in only one column in a normalized
>table. To prove that this is not normalized, assume that "Chuck"
>changes his name to "Charles"; you have to change his name in both
>columns and several places. The defining characteristic of a
>normalized table is that you have one fact, one place, one time.

Yes, and the adjacency list model does exactly that. That Chuck is the boss of Donna, Eddie and Fred is three atomic facts, not one. (One might change without the other.) Your example would only make sense if you had modeled facts of the form "X has name Chuck". In that case the change of a name would indeed be the update of one fact and therefore should only require the update of one row. However, you don't have a notion of the identity of a person that is separate from their name, so your update is, as far as the database knows and understands, not a renaming but the replacement of a person with another person. That this entails the update of more than one fact does not in any way prove denormalization.

In fact, it could be argued that it is the nested-set model that is in some sense "denormalized" because it contains redundant information: the second number can be omitted without loss of information. Moreover, if Bert becomes Eddie's boss than you need to do some renumbering of other rows as well.

So using normalization as an argument for the nested-set model is not really correct.

• Jan Hidders