Re: Normalizing Tree Data
Date: Mon, 11 Feb 2008 17:18:52 -0400
Message-ID: <47b0bbbe$0$4046$9a566e8b_at_news.aliant.net>
cr113 wrote:
> On Feb 8, 5:07 pm, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
>
>>Is the first level always Company? Is the next level always Division? Is >>the next level always Manager? Is the next level always Employee?
>
> Yes.
>
>
>>What does the ID table give you that you don't get from foreign key >>references from Employee to Manager, from Manager to Division, and from >>Division to Company?
>
> IBM
> Div-A
> Jones
> Smith
> Div-B
> Smith
> Microsoft
> Div-A
> Johnson
> Div-B
> Jones
> Smith
>
> I'm not sure what you mean by foreign key references.
>
> Do you mean this?
>
> Company Table
> Company
> IBM
> Microsoft
>
> Division Table
> Company Division
> IBM Div-A
> IBM Div-B
> Microsoft Div-A
> Microsoft Div-B
>
> Manager Table
> Company Division Manager
> IBM Div-A Jones
> IBM Div-A Smith
> IBM Div-B Smith
> Microsoft Div-A Johnson
> Microsoft Div-B Jones
> Microsoft Div-B Smith
>
> Or this?
>
> Company Table
> CompanyKey Company
> 1 IBM
> 2 Microsoft
>
> Division Table
> DivisionKey Division CompanyKey
> 1 Div-A 1
> 2 Div-B 1
> 3 Div-A 2
> 4 Div-B 2
>
> Manager Table
> ManagerKey Manager DivisionKey
> 1 Jones 1
> 2 Smith 1
> 3 Smith 2
> 4 Johnson 3
> 5 Jones 4
> 6 Smith 4
>
> I've tried both of these and they seem to get awkward with 3 or more
> levels. Are you talking about one of these methods?
Those both use foreign key references. Yes, either of these methods. What does your ID table give you that you don't get from using foreign key references?
(I strongly suggest you read up on database design. Not knowing what a foreign key reference is is basically inexcusable for someone designing a database.)
What specifically gets awkward? This brings us full circle to my earlier question regarding your most important queries using the tree.
>>You did not include an Employee table. Is there a difference between the >>Employee table and the Manger table? What is the difference?
>
> I'm just using that as an example. My actual project has 4 levels so I
> added a 4th level to make it similar.
I reiterate:
What does the ID table give you that you don't get from foreign key references from Employee to Manager, from Manager to Division, and from Division to Company?
What are the most important queries you need to evaluate using the tree? Received on Mon Feb 11 2008 - 22:18:52 CET