Re: How to 'normalise' this scenario

On 16 mei, 08:50, Frank Millman <fr..._at_chagford.com> wrote:

> Thanks for the reply, Joe.

I have documented some of the fallacies in his reply.

You might be interested.

Following are my remarks to your original post.

> 1. The table *is* the data - e.g. staff members in a hierarchy.

Can you give me an example of a table that "is NOT" the data ?

> 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.

"How data is used" does not make a difference as to whether it is data or not.

> The problem with the second scenario is that 'branch' nodes and 'leaf'
> nodes are conceptually different.

Then you are outside the realm of graphs, and whatever your problem might be, the nested sets stuff is not a solution for it.

The essence of graphs is that their nodes are NOT "conceptually different".

> 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.

Graphs are tricky no matter what.

> Is there an approach that satisfies the requirement, but avoids these
> problems?

You have not stated what "the requirement" is. Received on Tue May 17 2011 - 12:08:36 CEST