Many to many self joins

raghavshukla · August 18th, 2009, 08:24 AM

without wasting time here is the problem scenario
1. a MANAGER can have more than one imidiate SUBORDINATES
2. The SUBORDINATE in turn could be MANAGER of other SUBORDINATES

now if we create a MANAGER-SUBORDINATE table, against every manager record we have to have more than one subordinates that we may have to enter as comma seperated values. Means it is UnNORMALIZED form.

How do we create normalized self join for the above scenario ?

Raghav Shukla
india

peace95 · August 18th, 2009, 08:07 PM

Raghavshukla:

The problem lies in the way you have the relationships stated between the Manager and the Subordinate. See Link below.

http://i31.tinypic.com/skvme0.jpg

To avoid the Primary Key Violation the CEO would have to have a unique number, different from the Employee ID, say 99999. Building normalized tables will not be difficult Think of an Organization Diagram and defining the Person at the very top of the diagram.

Hope this helps.

Old Pedant · August 18th, 2009, 09:59 PM

I'm afraid I don't quite understand what peace95 is talking about.

I don't see any place in raghavshukla's post where he even mentioned any primary key violation.

raghavshukla: The real problem is that you should *NEVER NEVER NEVER* have a delimited list in a single DB field.

Also, I do *NOT* see that you have a need for a many-to-many table here. It's just a one-to-many, but it's a bit deceiving.

Example:

Code:

Table: Employees
    empid :: empname
      1   :: adam
      2   :: bob
      3   :: candy
      4   :: doug
      5   :: earl
      6   :: fran
 
Table: Subordinates
    empid :: sub_empid
      3   ::    2
      3   ::    5
      2   ::    1
      2   ::    4
      5   ::    6

That is:
-- Candy manages both Bob and Earl
-- Bob manages both Adam and Doug
-- Earl manages Fran

See? Simple as that.

NOW...

Now the REAL problem comes when you need to build an oranization tree from a set of tables like that.

The easiest way to do this is to know, ahead of time, the maximum "depth" or your tree.

Here, our depth does not exceed 2 (that is, two levels of subordinates).

And we can build the tree thus:

Code:

SELECT 3 AS level, empid, empname 
FROM employees WHERE empid NOT IN (
    SELECT empid FROM subordinates )
UNION
SELECT 2 AS level, empid, empname 
FROM employees WHERE empid IN (
    SELECT empid FROM subordinates 
    WHERE empid NOT IN ( 
        SELECT empid FROM subordinates )
    )
UNION
SELECT 1 AS level, empid, empname
FROM employees WHERE empid IN (
    SELECT empid FROM subordinates 
    WHERE empid IN ( 
        SELECT empid FROM subordinates )
    )

But... But you can see that such a query can get really really clumsy pretty quickly.

If you were using SQL Server, you *could* use a recursive stored procedure. But it's not clear to me that's a good idea even when it's possible.

A better way, I think, is to rethink the entire design. Use the same kind of table one might use for, say, a threaded forum. [This forum is *NOT* threaded...it's a simple flat system.]

peace95 · August 19th, 2009, 12:31 AM

Old Pendent:

Oh I don't know, but when person in this forum mentions relations or relationships between two entities and mentions "Normalization" my mind automatically shifts to Relational Database. RDBMS involves the relationships of Primary and Foreign Keys and their constraints in tables. Whereas you spoke of the Hierarchial Data Structure, the Tree view, most popular in OOP.

The diagram I sent is called an E-R Diagram, Entity-Relationship Diagram. It clearly shows the relationships between Manager- Subordinate which is One-to-Many and not Many-to-Many. The only self-join is that of the Manager at the very top of an organiztion, say the CEO or Company President. Do I need to show the tables?

raghavshukla · August 19th, 2009, 02:41 AM

you both have provided me with good insight. peace has said nearly the same thing although the depth of hierarchy concept of found interesting, That is useful for my application, thanks for the code too,

The beauty of databases is that a concept that may be simple in conception may turn out to be toughest in implementation and the one that may be complex may turn out to have the shortest and sweetest implementation procedure. Any how hierarchical database structures are something to think over...certainly

Thanks both of you

Raghav Shukla

peace95 · August 19th, 2009, 10:32 PM

Old Pendent:
This particular thread ended when raghavshukla posted his response "Thanks alot peace and pendent". But it appears that you want to directly attack my responses and since ths is the case, then you should start a new thread. ou are not reading the entire response.

At the bottom of my responses I state that what I write "... is my opinion" and I do have that right.

Old Pedant · August 20th, 2009, 02:46 PM

Removed my prior post. Seems pointless to try to discuas differences of opinion.

raghavshukla · August 21st, 2009, 02:47 AM

Yes I would certainly want to know about how to build hierarchical databases. But please be more Fundamental in appraoch.

Thanks