Re: help with nested set

From: lawpoop <lawpoop_at_gmail.com>
Date: Tue, 14 Apr 2009 07:12:49 -0700 (PDT)
Message-ID: <d578e8d5-644c-4605-aa15-b089fdd5cfbe_at_q14g2000vbn.googlegroups.com>

On Apr 14, 6:18 am, --CELKO-- <jcelko..._at_earthlink.net> wrote:
> First, buy a ccopy of TREES & HIERARCHIES IN SQL.  It will five you a
> lot more infomatuin than a Newsgroup.   Then it would help if you
> would post real DDL instead a narrative.
>
> 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.
>
> CREATE TABLE Clients
> (client_id CHAR(9) NOT NULL PRIMARY KEY, -- duns number?
>  client_name VARCHAR(15) NOT NULL,
> .. );
>

Thanks, Celko, your articles have been exceedingly helpful for me in understanding this problem. I'll pick up a copy of your book!

The actual Clients table DDL that we're using has a bit more stuff in it, but for this problem, all that's relevant is basically what you posted:

CREATE TABLE IF NOT EXISTS `Clients` (
  `id` int(10) unsigned NOT NULL auto_increment,   `client_name` varchar(40) NOT NULL default '',   ...
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

The Locations table looks similar, for this purpose:

CREATE TABLE IF NOT EXISTS `Locations` (

  `id` int(10) unsigned NOT NULL auto_increment,
  `client_id` int(10) unsigned NOT NULL,
  `location_name` varchar(30) character set utf8 NOT NULL default '',
  `unit_number` varchar(30) character set utf8 NOT NULL,

  ...
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

ALTER TABLE `Locations`
  ADD CONSTRAINT `Locations_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `Clients` (`id`) ON DELETE CASCADE ON UPDATE CASCADE )

It used to have client_id as a foreign key, but it will change to have org_chart_id as a foreign key, to describe its place in the hierarchy.

ALTER TABLE `Locations`
  ADD CONSTRAINT `Locations_ibfk_1` FOREIGN KEY (`org_chart_id`) REFERENCES `ClientOrgChart` (`id`) ON DELETE CASCADE ON UPDATE CASCADE )

Now for the ClientOrgChart. I decided to go with a modified preorder tree traversal algorithm:

CREATE TABLE IF NOT EXISTS `ClientOrgChart` (

  `id` int(10) unsigned NOT NULL auto_increment,
  `client_id` int(10) unsigned NOT NULL,
  `level` varchar(50) collate utf8_unicode_ci NOT NULL COMMENT 'the
name of the organizational level', e.g. ''Eastern Region''',

  `lft` int(10) unsigned NOT NULL,
  `rgt` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `client_id` (`client_id`,`lft`,`rgt`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='This is a modified preorder tree traversal algorithm.' ;

ALTER TABLE `ClientOrgChart`
  ADD CONSTRAINT `ClientOrgChart_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `Clients` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

What was bugging me was the client_id -- it seemed to me that I really should only need to know it in one place, at the root node, like a parent/child relationship. But actually, in this scheme, it's a way of differentiating one tree from another, since the nodes of all trees are mixed together in a single table.

Since I'm using MySQL, I'm limited in the SQL that I have available. Received on Tue Apr 14 2009 - 16:12:49 CEST