Re: Bill of materials / Groups in Groups

joe_celko_at_my-deja.com wrote:

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

Chucks key will NEVER change! It never has to because it has no business meaning. The key is in a 1-1 relation with Chuck's soul, not his name, or his SIN, or anything. 1-1 with the Chuck you grew up with, share memories with, and attend his funeral.

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

Efficiency is why I used NULL. But NULL is perfectly allowable in normalized tables. NULL is just another allowable 'letter' in the alphabet used by the boss column.

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

My answer was in the last post. If you consider storing a directed graph in a DB, I hope we can agree that

	Edges                       Node
	Key   Head   Tail           Key
	=================           ============

is the best, most elegant solution. You may fight me on Edge.Key, but that is irrelevant. Since a hierarchy is a simple version of a directed graph, it stands to reason that it's table structure would be the same or simpler. The Personnel table I proposed above is that.  

> This is a graph, not a tree and therefore not an organizational chart.

But it could be! I worked at a bank, and these types of organizational charts are possible! They use the term "dotted-line relationship" to represent the idea that one person 'works' for another, even though the other is not the person's boss. There is not limit the the number of dotted line relationships.

> Also, the key column in the Management Relation table is redundant --

There is no redundancy. The key is again 1-1 with the relationship, and none of its attributes. The relationship is just like "Chuck", its existence is not defined by its attributes.

> 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

A naive query will show that 7 is a employee of 1, which is wrong. Therefore I must conclude that you at least need another table, and a column to indicate what tree each relationship is in. But like you said your method is not to be applied to this situation. Received on Tue Jan 11 2000 - 00:00:00 CET