Re: How to 'normalise' this scenario

> Since SQL is a set oriented language, this is a better model than the
> usual adjacency list approach you see in most text books.

Yolks. "If SQL is a set-oriented language, then the nested sets model is a better one than the adjacency list model." That one should go into RDB's hall of fame.

> The adjacency list table is denormalized in several ways.

Is that supposed to mean "denormalized" as in "not in 2NF", "not in 3NF", "not in BCNF" ? Exactly which of the normal forms is violated by an adjacency table {BOSSID, EMPID} KEY {BOSSID, EMPID} ?

> We are
> modeling both the Personnel and the Organizational chart in one table.

No we are not. An adjacency list table records/lists/documents/ models/... the organizational chart, NOT the personnel details.

If one has a need for recording personnel details, then that should go in another table, with another external predicate. Of course there might be constraints to enforce, but that is just a matter of defining all the appropriate CREATE ASSERTIONs (or implementing their equivalents using triggers).

> Another problem with the adjacency list model is that the
> boss_emp_name and employee columns are the same kind of thing (i.e.
> identifiers of personnel), and therefore should be shown in only one
> column in a normalized table.

No database table should ever have two columns of the same type ?

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

This has nothing to do with normalization.

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

The employee identified by employee number EMPID has the employee identified by employee number BOSSID as his direct chef.

Please explain how this "does not model subordination".

> Authority flows downhill in a hierarchy, but If I fire
> Chuck, I disconnect all of his subordinates from Albert.

If Chuck gets fired, then you remove or end-date the row for Chuck in the Personnel table.

Depending on whether there are constraints between the OrgChart table and the Personnel table, this might require that the organizational chart is adapted first, reflecting the fact that all of Chuck's former subordinates now have a new chef, prior to doing this update in the Personnel table.

> 5. The nested set model has an implied ordering of siblings which
> the adjacency list model does not.

Now THAT is a problem.

With the nested sets model.

Forcing orderings upon the user where none are needed. Received on Tue May 17 2011 - 11:47:39 CEST