Hmmit may be easier to just do this...
Group Definition Table
DefID
GroupName
User Definition Table
---
UserID
GroupMember Table
---
MapID
UserID
GroupID
Then you can assign a user to a group by
inserting a new record into the GroupMember Table where UserID is the user,
and GroupID is
the new group.
If you want to search all users in Group "West" you could say
(assuming Group Definition Table is populated with
1 North
2 South
3 East
4 West
)
SELECT UserID from GroupMemberTable WHERE GroupID = 4;
or if you wanted to check multiple groups,
SELECT UserID from GroupMemberTable WHERE GroupID IN (4,2,3);
The map table may get huge depending on the number of users/groups,
but it's a possibility.
-Szii
- Original Message -
From: "Doug Schasteen" <[EMAIL PROTECTED]>
To: "php-db list" <[EMAIL PROTECTED]>
Sent: Friday, May 18, 2001 1:13 PM
Subject: [PHP-DB] Need Database Design help
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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
