Re: Bill of materials / Groups in Groups

>> CREATE TABLE Personnel

   (emp CHAR(10) PRIMARY KEY,
    boss CHAR(10), -- unneed & denormalizes the table     salary DECIMAL(6,2) NOT NULL,
    lft INTEGER NOT NULL,
    rgt INTEGER NOT NULL);

   Personnel
   emp boss salary lft rgt

   Albert   NULL     1000.00   1   12
   Bert     Albert    900.00   2    3
   Chuck    Albert    900.00   4   11
   Donna    Chuck     800.00   5    6
   Eddie    Chuck     700.00   7    8
   Fred     Chuck     600.00   9   10

The Personnel table is not normalized because there is a functional dependency between the person and the persons name. Change the table to the following to be normalized. The limitation of databases to handle these types of tables says more about the implementation than about the table layout.

 Personnel
 key name boss salary

  1   Albert NULL     1000.00
  2   Bert    1        900.00
  3   Chuck   1        900.00
  4   Donna   3        800.00
  5   Eddie   3        700.00
  6   Fred    3        600.00  <<

WRONG! When you have a NULL, always worry about normalization. This is not a normalized table and you can prove it very quickly. In a normalized databsae, you have one fact, in one place, one time. Change Chuck's id number and you have to change the boss column in three other places.

I used the names as the primary key instead of an employee id number just to save space. What I should have had is one table for the organizational chart and and another table for the personnel. That is another normalization rule -- a table represetns one entity or relationship. My table shows the personnel enetities and the organizational relationship.

>> The above table is normal, WRT the information provided. It is also
the most elegant table design; unfortunate that database implementations can not handle this common scenario. <<

Why do you think that faking pointer chains is an elegant solution?

>> This is an interesting solution, but it is only good for directed
graphs without cycles, and limited to 2 dimensions. <<

Yes, this method is strictly for Trees and they are always planar graphs.

>> Example, hierarchy defined when many bosses, for a single employee,
is possible.

 Personnel		Management Relation
 key  name   		key  Boss  Employee
 ===========		===================
  1   Albert              A    1      4
  2   Bert                B    1      5
  3   Chuck               C    1      6
  4   Donna               D    1      7
  5   Eddie               E    2      5
  6   Fred                F    2      7
  7   Roger               G    2      8
  8   Bernie              H    2      9
  9   Kyle                I    3      6
 10   Rhonda              J    3      7
                          K    3      9
                          L    3     10

The above simply is equivalent to a three sets, all sharing a single employee (5), each pair with a shared employee (5, 6, 9) and an exclusive employee each (4, 8 10). Notice that this can not be mapped to the method you proposed. The hierarchy defined must have an overlapping edge (not 2D). <<

This is a graph, not a tree and therefore not an organizational chart. Also, the key column in the Management Relation table is redundant -- the edges should be unique. Let me assume that emps (1,2,3) are bosses because they have no indegree. Represent the strucuture as a forest of three trees, like so:

 OrgChart
 emp_id lft rgt

  1      1 10
  4      2  3
  5      4  5
  6      6  7
  7      8  9
  2      1  8
  7      2  3
  8      4  5
  9      6  7
  3      1  8
  7      2  3
  9      4  5
 10      6  7

>> With cyclic graphs information is lost about the relationship between
the nodes. <<

I have not found a good way to handle cycles as sets and keep falling back to this "pointer-chains and adjacency list" approach, which then leads to procedural code and cursors.

> Last, the table design you propose is not normal because there is a functional dependency between the LFT and RHT columns; LFT<=RHT at all times. Fix it by replacing RHT with DELTA=RHT-LFT. <<

That is not a functional dependency; it is a constraint. The lft value is not a determiner rgt. The difference between them is important for queries, however.

--CELKO-- Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Jan 11 2000 - 00:00:00 CET