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