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

>> Tell me, is a relationship married-to with the attributes husband and wife any different than the conceptual entity, marriage, with the same attributes? <<

Is light a wave or a particle <g>?

I'd say if I have a marriage license number, then I am modeling a marriage. It has become a legal entity in its own right and it will exist after either the husband or wife or both are dead (we have to settle up the estate).

On the other hand, if I am modeling (boy, girl) pairs for a dinner party I do not give the pairings an identity of their own. It is just "John & Marsha", etc.

When I did Chen E-R diagrams, I would sometimes use a diamond inside a retangle to show that the item could be viewed either way. But not both at the same time. That is what I was getting at.

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 (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. We are modeling both the personnel (nodes) and the organizational chart (relationships) in one table. But for the sake of saving space, pretend that the names are job titles and that we have another table which describes the OrgChart that hold those positions.

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.

The final problem is that the adjacency list model does not model subordination. Authority flows downhill in a hierarchy, but If I fire Chuck, I disconnect all of his subordinates from Albert. There are situations (i.e. water pipes) where this is true, but that is not the expected situation in this case. Received on Wed Sep 25 2002 - 20:18:32 CEST