Re: Normalizing Tree Data

cr113 wrote:

> On Feb 8, 2:41 pm, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
>

>>cr113 wrote:
>>
>>>On Feb 8, 11:42 am, TroyK <cs_tr..._at_juno.com> wrote:
>>
>>>>On Feb 8, 10:10 am, cr113 <cr..._at_hotmail.com> wrote:
>>
>>>>>What's the simplest way to set up a relationional database with tree
>>>>>data? I'm more interested in simplicity vs speed.
>>
>>>><snip - see OP>
>>
>>>>People will be able to guide you better if you can expand more on what
>>>>criteria are important to you.
>>
>>>Here are some specifics: I'm using Access. There will only be a few
>>>thousand records maximum in any table. Around 4-6 fields in each
>>>table. Very few concurrent users. The users need to be able to add/
>>>edit/delete any of the nodes in the tree. There are 4 levels in the
>>>tree. Expanding the previous example it would look something like
>>>this:
>>
>>>Company
>>>    Division
>>>        Manager
>>>            Employee
>>
>>What are your most important queries using the tree?

I disagree. Your design depends on what is important--not vice versa.

  I'll need to be
> able to display the entire tree (using a TreeView object in Visual
> Studio if you are familiar with that). Add/Edit nodes. Delete a node
> and it's underlying children.
>
> Have you looked at the way my tables are set up in my example? Am I at
> least in the ballpark? Should I include a "level" field in the ID
> table perhaps (root = level 0, children of root = level 1, etc)?

Is the first level always Company? Is the next level always Division? Is the next level always Manager? Is the next level always Employee?

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?

You did not include an Employee table. Is there a difference between the Employee table and the Manger table? What is the difference?

Your requirements will determine where the ballpark is. Since those are mostly unknown to the rest of us, none of us can legitimately say whether you are in it. Received on Sat Feb 09 2008 - 00:07:15 CET