How to 'normalise' this scenario

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 Received on Fri May 13 2011 - 09:22:38 CEST