Re: Normalizing Tree Data

cr113 wrote:

On Feb 8, 5:07 pm, Bob Badour <bbad...@xxxxxxxxxxxxxxxx> 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?
.

Relevant Pages

  • Re: Normalizing Tree Data
    ... edit/delete any of the nodes in the tree. ... 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? ... Your requirements will determine where the ballpark is. ...
    (comp.databases.theory)
  • Re: Nested Model
    ... querying for the UserID's the manager manages, but the node id of the ... matter how deep the tree. ... FROM OrgChart AS O1, OrgChart AS O2 ... The employee and all his subordinates as a nice symmetry to it. ...
    (microsoft.public.sqlserver.programming)
  • Re: Merge Document and Fill in Form (Word 2000)
    ... Whereas all the Manager needs to do is fill out the ... However, when it comes to appending the signature, you're probably ... going to need some VBA. ... This way the Employee Name can be plced on ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Count person only 1 time and show person not counted
    ... (SELECT Employee, qtr, Manager ... I tried this query and I am getting a missing operator error. ... "John Spencer MVP" wrote: ...
    (microsoft.public.access.queries)
  • Re: Count person only 1 time and show person not counted
    ... "John Spencer MVP" wrote: ... The structure I proposed would have up to four records for each employee ... (SELECT Employee, qtr, Manager ... Can you show me how it can be one query? ...
    (microsoft.public.access.queries)