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

"--CELKO--" <71062.1056_at_compuserve.com> wrote in message news:c0d87ec0.0209251018.5249d3f4_at_posting.google.com...
> >> 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).

What the heck is a legal entity? Is there a formal definition for illegal entities? What do we do with one if we catch one?

I can see where adding additional conditions and criteria based on personal insight might help one in determining whether some modelling construct should be identified as either a entity or a relationship, but for every Celko with his own idea of distinguishing between the two, there is an anti-Celko who believes just the opposite. Why? Because it is more in the realm of <interpretation> and <opinion> than on any formal modelling framework.

Regardless, my original criteria and stated constraints were that both the entity and relationship simply had attributes of a husband and a wife, and I probably should have added the constraint that the combination of the two property values should be unique (this would disallow re-marriages). There is no rational reason why the married-to relationship couldn't have the same 'marriage license' attribute as a 'marriage' entity, where they both will map to logically equivalent predicates.

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

You've in one fell swoop basically stated that any unary relationship should be discounted as a valid alternative for both modeling and implementation. You can't be saying this on purpose.

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.

So what? You have to do this for any 1-M or N-M relationship when you need to change the referenced key or key component value within a relational database....The basic integrity aspect of the relational model includes referential integrity, and ideally a robust set of referential actions. An ON UPDATE CASCADE referential action would be ideal, and would be one way of addressing the situation you describe. If vendor's don't provide referential action functionality to this extent, then the obvious alternative would be to at least RESTRICT operations so that a user or programmatic construct would have to delete the referenced tuple and create another one with the altered value.

The defining characteristic of a
> normalized table is that you have one fact, one place, one time.
>
I see much beauty in the concept of automatic navigation based on data element values rather than using user-visible navigation links between data structures. How do you propose to maintain this model without being able to have more than one of the same value (attribute) within a database? Good grief.

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

Daniel Guntermann Received on Thu Sep 26 2002 - 03:17:52 CEST