Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > BOOK: Beginning Database Design
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Beginning Database Design
This is the forum to discuss the Wrox book Beginning Database Design by Gavin Powell; ISBN: 9780764574900
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning Database Design section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old August 18th, 2009, 08:24 AM
Registered User
 
Join Date: Apr 2008
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Many to many self joins

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
Reply With Quote
  #2 (permalink)  
Old August 18th, 2009, 08:07 PM
Friend of Wrox
Points: 793, Level: 10
Points: 793, Level: 10 Points: 793, Level: 10 Points: 793, Level: 10
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2005
Location: OKC, OK, USA.
Posts: 227
Thanks: 1
Thanked 7 Times in 7 Posts
Default

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.
__________________
Disclaimer: The above comments are solely the opinion of one person and not to be construed as a directive or an incentive to commit fraudulent acts.
Reply With Quote
  #3 (permalink)  
Old August 18th, 2009, 09:59 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.]
Reply With Quote
  #4 (permalink)  
Old August 19th, 2009, 12:31 AM
Friend of Wrox
Points: 793, Level: 10
Points: 793, Level: 10 Points: 793, Level: 10 Points: 793, Level: 10
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2005
Location: OKC, OK, USA.
Posts: 227
Thanks: 1
Thanked 7 Times in 7 Posts
Default

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?
__________________
Disclaimer: The above comments are solely the opinion of one person and not to be construed as a directive or an incentive to commit fraudulent acts.
Reply With Quote
  #5 (permalink)  
Old August 19th, 2009, 02:41 AM
Registered User
 
Join Date: Apr 2008
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Thanks alot peace and pendent

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

Last edited by raghavshukla; August 19th, 2009 at 03:05 AM.
Reply With Quote
  #6 (permalink)  
Old August 19th, 2009, 10:32 PM
Friend of Wrox
Points: 793, Level: 10
Points: 793, Level: 10 Points: 793, Level: 10 Points: 793, Level: 10
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2005
Location: OKC, OK, USA.
Posts: 227
Thanks: 1
Thanked 7 Times in 7 Posts
Default

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.
__________________
Disclaimer: The above comments are solely the opinion of one person and not to be construed as a directive or an incentive to commit fraudulent acts.
Reply With Quote
  #7 (permalink)  
Old August 20th, 2009, 02:46 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Removed my prior post. Seems pointless to try to discuas differences of opinion.
Reply With Quote
  #8 (permalink)  
Old August 21st, 2009, 02:47 AM
Registered User
 
Join Date: Apr 2008
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default @Old Pendent

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

Thanks
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
What are joins? Bhalchandra SQL Server 2000 2 July 2nd, 2007 11:29 PM
Problems with joins brettdalldorf SQL Server 2000 4 December 22nd, 2005 01:50 AM
Joins nalla Oracle 0 December 14th, 2005 04:54 AM
Joins r_ganesh76 SQL Server 2000 2 February 9th, 2005 11:21 PM
Joins marthaj SQL Server 2000 7 June 26th, 2003 09:02 AM



All times are GMT -4. The time now is 04:27 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.