Re: Normalizing Tree Data

On Feb 11, 3:18 pm, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
> 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?

The first method uses up huge amounts of space. I think you may be right, though. I'm starting to lean towards the 2nd method. I lied a little. I never actually used the 2nd method. I started a few times but changed my mind because I thought it would be too hard to use. I'm thinking if I write some simple functions it would make it much easier. For example a function like GetCompany(Manager). Otherwise all those SQL joins would get ugly.

Thanks! Received on Mon Feb 11 2008 - 23:37:54 CET