Re: How to 'normalise' this scenario

On May 13, 7:22 pm, "Fred." <ghrno-goo..._at_yahoo.com> wrote:
> On May 13, 3:22 am, Frank Millman <fr..._at_chagford.com> wrote:
>
>
> > Hi all
>
> > I have read up on 'nested sets', and I like the concept.
>
> > It seems to me that there are (at least) two possible scenarios -
>
> > 1. The table *is* the data - e.g. staff members in a hierarchy.
>
> > 2. The table is used as navigation for a separate data table. For
> > example, you might have thousands of product codes, and you want to
> > set up a separate table to provide a hierarchical 'view' of the
> > product code table, allowing a user to drill down to find the code
> > they are looking for.
>
> > The problem with the second scenario is that 'branch' nodes and 'leaf'
> > nodes are conceptually different.
>
> > A 'branch' node may require columns for 'code' and 'description'.
>
> > A 'leaf' node requires a column that contains a foreign key to the
> > products table.
>
> > It is tricky to ensure that there is one, and only one, 'leaf' node
> > for each row in the product table.
>
> > It is tricky to ensure that 'branch' codes are unique, as 'leaf' nodes
> > will have a null 'code', and MS SQL Server does not allow more than
> > one null in a unique column.
>
> > Is there an approach that satisfies the requirement, but avoids these
> > problems?
>
> > Thanks for any insights.
>
> > Frank Millman

Thanks for the reply, Fred

>
> The models I've seen give the leaf nodes in the hierarchy left and
> right traversal numbers as well.  Except while the hierarchy is being
> modified each of the traversal numbers will be unique to the node even
> using integer traversal numbers, so each of the traversal numbers
> fields can have a unique constraint.  If you are using rational rather
> than integer traversal numbers then I'd think it would be possible to
> modify the hierarchy  without violating the uniqueness contraint.  Of
> course, the left and right tranversal number sets should be non-
> intersecting, and have a bunch of other constraints, but there is a
> limit to what you can check.
>

Agreed - the 'nested set' model requires that *every* node has a left and right traversal number. I was referring to the additional columns that may be required - in my second scenario, they differ depending on whether the node is a 'branch' or a 'leaf'.

> As far as linking the hierarchy table and the product table, it might
> be a good idea to look at what your business rules are going to
> become.  While currently you have the product table and are looking at
> the hierarchy table as an index, it might be reasonable in the future
> to create the leaf node first, when you first conceive of making or
> carrying a product, and later create a product entry which points to
> the leaf node.  This use would make a descriptor applicable to the
> leaf node, and at the same time remove the pointer to the product
> table.  Once things were set up, the pointer in the product table to
> the leaf node could be uniquely constrained.
>

That is an interesting idea. I will give it more thought.

> The issue here, which you would have to deal with in any case, is that
> some ways of adding to hierarchy can promote a leaf node which has an
> associated product.  If you want to control this, you could make an
> indicater or not a node is a leaf node part of the primary key, and
> constrain that portion of the foreign key in the product table to have
> the leaf value only.
>

I agree that this is an issue. I was planning to control this at application level rather than through SQL constraints.

> Fred.

Thanks for the ideas - much appreciated.

Frank Received on Mon May 16 2011 - 08:33:21 CEST