Re: How to 'normalise' this scenario

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

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 nonintersecting,  and have a bunch of other constraints, but there is a limit to what you can check.

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.

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.

Fred. Received on Fri May 13 2011 - 19:22:24 CEST