[RESOLVED] Database Normalisation - Group within a Group???

itsallgood

Hi guys,

I've not worked with databases for so long! I've forgot how to do things 🙁

I'm hoping tthis project will bring it all back to me :p

I'm trying to do the following:

[ATTACH]4633[/ATTACH]

I want a user to be able to be put into a group (GROUP-A, and GROUP-😎

I also want to be able to put groups in groups (as shown in GROUP-C)

There needs to be no limit to the number of users, or groups in a group.

I can work out i need a table called users 🙂

TABLE_USER
#UserID
UserName

And i'm guessing i need a table called group...

TABLE_GROUP
#GroupID

But i don't know how to do the link -- so a group can hold users, and groups -- and keep the table normalised.

Thanks so much for any help!!

Cheers!

group-in-group.png

laserlight

I expect a join table to associate a user with a group, and another join table to associate a group with another group. The latter may not be the best design if you expect many (as in deep) levels of subgroups, but it will work.

itsallgood

Thanks for the quick reply laserlight 😃

Can you tell me if this picture is right...

[ATTACH]4637[/ATTACH]

I have 3 tables...

USER
#userID
userName

GROUP
#groupID
groupName

GROUP_MEMBER_USER
#groupID
#userID

GROUP_MEMBER_GROUP
#groupID
inGroupID

The latter may not be the best design if you expect many (as in deep) levels of subgroups, but it will work.

Can you explain this? I'm hoping this database will be the start of a big system - so would like to get the group parts right.

Thanks again!

group-in-group-eg.png

Derokorian

I think I would have 3 tables, USER, GROUP, and GROUP_MEMBER. Where GROUP_MEMBER would be GROUP_MEMBER_ID INT NOT NULL PRIMARY KEY, MEMBER_TYPE ENUM('GROUP','USER') NOT NULL, MEMBER_ID INT NOT NULL (references group_id or user_id depending on the value of member_type).

Just my two cents.

barand

If you hold parent group in the group table it makes for simple groups within groups to any number of levels

+------------+        +------------+        +-----------+
|  user      |        | membership |        | group     |
+------------+        +------------+        +-----------+
|  userID    | --+    | id         |   +--- | groupID   | ---+
|  name      |   +--< | userID     |   |    | descrip   |    |
+------------+        | groupID    | >-+    | parentGrp | >--+
                      +------------+        +-----------+

itsallgood

Thanks guys!

I was going to go with barand -- it looks simple, and cuts down on queries. -- but it only let's a group have one "parentGrp". Which wouldn't work for me, as a group can have many "parent" groups.

Derokorian -- I think i'll go with your example 🙂

Thanks again to everyone!

          |  Users   | Groups  | TOTAL USERS
---------------------------------------------
GROUP A   |    5     |    0    |      5
---------------------------------------------
GROUP B   |    3     |    2    |      13
---------------------------------------------
GROUP C   |    0     |    3    |      30

johanafm

itsallgood;11011669 wrote:
Can you explain this?

Due to the number of joins you'd need: one per level of subgroups. Google hierarchical data in relational databases. There are techniques for not needing recursion / increased number of joins for deeper levels of nesting.

I'm curious about how a group can have multiple parent groups? I'm not talking about grandparents etc, since that regards only what laserlight talks about: nest level. A 3 level deep structure means the leaf child has one parent, which in turn has one parent. It does not mean the leaf child has two parents. Multiple parents would mean that you could not unambigously determin the parent of such a child since it could be either one of them or both. Also, I'm not saying a child can't have multiple parents - I'm simply courious about what kind of real world representation would require that.