Re: Normalizing Tree Data

On Feb 8, 3:03 pm, cr113 <cr..._at_hotmail.com> 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?- Hide quoted text -
>
> I'm not sure. It depends on how I set up my tables. 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)?- Hide quoted text -
>
> - Show quoted text -

If your design goals lean toward simplicity and inspectability, you may want to forego the "ID" table altogether and specify your relationships via subset requirement constraints (implemented as foreign keys) between the tables directly.

This approach (as any other) will require careful consideration of the identifying attributes (candidate keys) for each of the tables, as each referencing table will make use of the same. The IDs you have in your initial sketch look to be, conceptually, something akin to "object ids", which are generally contraindicated -- that which identifies a company is usually of a different domain than that which identifies a division, and a manager (although a manager and an employee may be identified by values from the same domain, e.g., employeeId).

I'll again reiterate that there are a number of design criteria (some in conflict with one another) that you'll need to consider. As Bob has pointed out, the types of queries you will need to run against the data also come into play.

If this is a "toy" project of yours, I suggest looking over some of the designs at this website: http://www.datamodel.org/ and seeing if you can find/morph/apply solutions similar to the domain you're working with.

If this is for a professional engagement you're involved in, I would suggest bringing in a data modeling consultant to help you jumpstart the project -- assuming you don't have the requisite time to educate yourself more fully on the data modeling discipline.

HTH,
TroyK Received on Sat Feb 09 2008 - 00:21:25 CET