I'll try to simplify my problem so that this isn't a long read and is easy to
understand.
Tree Table:
------------------
node_id
node_name
parent_id
I use the above table's rows to create an online tree in php and javascript. The
parent_id column is just a pointer to a the node_id of which the current node branches
off of. The tree is used to group users, which we store in another table.
Users Table:
--------------------
user_id
node_id
Now, if I wanted to compare side by side the users of one node to the users of another
node, I would do a select * from tree, users where users.node_id = tree.node_id and
node_id = 'x' for each node id. I created the database this way so that I could do
comparisons with polling results. My problem comes when the tree gets large and I want
to compare a group of nodes to another group of nodes. For instance, the tree splits
out into southwest and northwest divisions, and under each of those it splits out into
the groups where the users reside. There will be no users linked directly to the
southwest or northwest nodes, but I want to compare all the users that are in groups
that are PART of the northwest node to all users that are in groups that are part of
the southwest node. I could probably do a "Where parent_id = 'node id of northwest
division" but what if the tree splits up even farther than that? What if the tree
originally splits into North and South divisions, and under each of those divisions it
goes into East and West, and then splits into groups. I'd have to be able to know the
parent's of the parents in a single SQL statement.
I don't see how I can do this without redesigning the database, and I'm not even sure
what I would need to change in order to get the database to work. If anyone out there
is a database design expert and can see my mistake right away, please help me out.
- Doug Schasteen
