help with nested set

From: lawpoop <lawpoop_at_gmail.com>
Date: Mon, 13 Apr 2009 09:17:25 -0700 (PDT)
Message-ID: <76d95109-5b7a-4017-b951-8791840a07e8_at_3g2000yqk.googlegroups.com>

I am working on a data model that uses a nested set in MySQL. I'm semi- aware of the relational and other shortcomings of MySQL; please forgive me if discussion of the platform is verboten here. I have a problem that I believe is a general RDBMS problem; if it is instead a MySQL problem, please let me know and give me an idea of the specific solution as it might be implemented in another RDMS

 We have a table Clients that's a list of clients. We have a table of Units which is retail units in a physical location. The simple model is to give each unit a client_id, but there is a hierarchical grouping system that we need to do reports by, so a simple one-to-many won't do it.

For any client, a unit might belong under various organizational levels. A unit might be a corporate unit, so it's simply under corporate. A unit might belong to a franchise, so the org chart would go something like Corporate - Franchise A - Unit 12 . A unit might be in the New York district of the Eastern Region, so the org chart there would look like Corporate - Eastern Region - New York District - Unit 36. In this case, the Eastern Region wouldn't have any Units that belong solely under it; it's simply a 'virtual' grouping level.

Any client might have any kind of organizational chart, with whatever naming schema. I'm going to assume for now that they're all strictly hierarchical -- no overlapping. And, there may be any number of virtual grouping levels.

So, now I will make a ClientOrgChart, which will be used to join Clients to Units. I've been looking at both adjacency list and the preorder tree traversal algorithm from this web page: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html .

But, now I'm not sure the best way to relate the ClientOrgChart to clients. In any of the ways I imagine it, I can see ambiguity arising, and I don't see any way to prevent it using the DDL. I'll use the adjacency list in the examples since it's easier to type out.

Suppose I had this adjacency list:

id client_id parent_id level

1       1        NULL      Corporate
2       1          1       Franchise 1
4       1          1       Eastern Region
5       1          4       New York District

All child nodes *should* have the same client_id as the ultimate root, but I can't think of any way to ensure this in the DDL. I'll just have to "be careful" in code.

A benefit of this is that I can easily join a unit to the client table, by looking that the client_id of the ClientOrgChart record that it's related to, but again, there's a ( hopefully small ) risk of it being wrong.

Or, I could make the client_id allow a NULL value

id client_id parent_id level

1       1        NULL      Corporate
2      NULL       1       Franchise 1
4      NULL       1       Eastern Region
5      NULL       4       New York District

So have I fixed anything? I can't enforce a rule like 'Either client_id or parent_id must be null'. Figuring out the client that a unit ultimately belongs to would be a slightly more complex query, also.

Another way to do it would be to make a one-to-one table ( never thought I'd use that ) that joins Clients to ClientOrgChart, but there's no way to prevent the insertion of a non-root node value in this table... same problem.

The usual solutions that my mind jumps to all seem not really to solve the underlying problem -- possible conflicting client_id values. Is this problem intractable? Is the best solution a compromise, not a perfect one? Received on Mon Apr 13 2009 - 18:17:25 CEST