what's the best USER to USERGROUPS implementation?

Tea_J

scenario is,
A user can be a member of one or more groups, or none.

unlimitted number of usergroups can be created (in my application im expecting hundreds if not thousands of groups)

there must be a way to query all users of a specific user group

the only way i can think of are:
USERGROUP MASTER TABLE w/ USERGROUP USERS TABLE
eg.
usergroup: admin, group_id = 1

usergroup_users: group_id = 1 / user_id = 1
usergroup_users: group_id = 1 / user_id = 2
usergroup_users: group_id = 1 / user_id = 3

so on...

the above is the easiest, but darn there's gona be a bunch of rows in that usergroup_users table .. specially if one is expecting 1000 users / group x 1000 groups .. lolz

the other way, w/c is harder is,

do away with the usergroup_users table, and just do this:
USER: user_id=1 / usergroup_ids = "1,2,3,4,5,";

i saw this being used in different applications, like popular forum software.. but this is gona be HELLISH to manage/update

adding a user to a group, once needs to process the usergroup_ids (split/explode etc) for each user x 1000 or more ...

hmm...

what's the best method? or is there other more "cool" way to do this?

tnx

johanafm

Your first idea is the way to go, i.e. 3 tables: user, usergroup and usergroup_user. Read about database normalization at for example en.wikipedia for more information about how to structure your data. Comma separated values break normalization and will give you endless troubles down the road.

Tea_J

i do feel more comfortable w/ the first idea too...

question though, how come invisionboard, mybb, and a lot of these forum software use CSV .. ther's gota be some reason behind it?

laserlight

Tea_J wrote:
question though, how come invisionboard, mybb, and a lot of these forum software use CSV .. ther's gota be some reason behind it?

They suck :p

Tea_J

hey man.. how are you? good to see you're still around.. haha . i've not been on the forums for a LOONG arse time..

anyway Hahahah 🙂 .. but i did realize something.. i would probably use a column w/ CSV of group_id's for a user, but only to make things faster in the SQL side of things.. i mean not to rely on it as the actual primary data..

for short,
USER 1 joins 100 groups
GROUP_USERS table will have 100 entries for this user
USER 1 will have a column called groups w/c is CSV data of all 100 group ID's

this groups column will be used for faster checking of group the user (or users) belongs to, so one does not need to run a separate query just for the group check 🙂

when USER joins another group, or leaves a group, the USER>groups column will simply be UPDATED as a whole (SELECT ALL GROUPS user belongs to and CSV those ID's into the groups column) lolz

makes sense? althoug i see no indication that these forums (includin our forum i belive) uses such.. but they simply store the group ids in csv .. strange..

laserlight

Tea_J wrote:
this groups column will be used for faster checking of group the user (or users) belongs to, so one does not need to run a separate query just for the group check

I think that is a misguided attempt at optimisation. The separate query will be very fast.

Tea_J

...if i may continue to discuss this (for better knowledge)..

how will a separate query be better than column data..

say if i need to list 1000 users and get all the group ID's they're involved in , the CSV trick will simply require 1 SELECT QUERY to all 1000 targetted users..

where as having to issue a SEPARATE query for EACH of the 1000 users to retrieve their related group IDs, well that's 1000 x 1000 = 1M queries that took place..

i guess there's a nice little JOIN one can do w/ this perhaps? I've not begin to analyze it though, and regardless the JOIN w/ also be an additional overhead.

anyway just playing around w/ the idea, not really debating it lolz

laserlight

Tea_J wrote:
how will a separate query be better than column data..

You avoid duplication of data. Granted, you may say that the join table will be the canonical version, but if somehow a cached field does get out of sync, then your code will function inconsistently. This may be acceptable if you find that the cached field improves performance significantly, but at the moment all you have is the blind supposition that it will perform better. This is misguided, premature optimisation.

Tea_J wrote:
say if i need to list 1000 users and get all the group ID's they're involved in , the CSV trick will simply require 1 SELECT QUERY to all 1000 targetted users..

Say if I need to list 1000 groups and get all the user IDs of users that belong to those groups. The CSV trick will simply require 1 SELECT query to all 1000 targeted groups. Are you going to include a CSV column for the group table?

Tea_J wrote:
where as having to issue a SEPARATE query for EACH of the 1000 users to retrieve their related group IDs, well that's 1000 x 1000 = 1M queries that took place..

Sorry, I'm going to facepalm here. You need to read a book on how to use relational databases if you really believe that you need a million queries to do this. Even a naive implementation would only require 1000 queries (i.e., 1 for each user) because selecting the group ids from the users/groups join table is enough.

Tea_J wrote:
i guess there's a nice little JOIN one can do w/ this perhaps? I've not begin to analyze it though, and regardless the JOIN w/ also be an additional overhead.

Yes, a join of the users table with the users/groups join table will come in handy to reduce your hypothetical 1 million queries to a single query. If the user ids are already known, a join is not needed at all.

As for overhead of joins... here's a challenge: suppose that each group has a name. Now, I want to retrieve for each of 1000 users the names of the groups that they belong to. How will you do that? I can do that with a single query without making use of the CSV column by also joining on the group table.

Tea_J

Hi mate

good points you got.. just to disect..

Say if I need to list 1000 groups and get all the user IDs of users that belong to those groups. The CSV trick will simply require 1 SELECT query to all 1000 targeted groups. Are you going to include a CSV column for the group table?

I was thinking of a like %% approach on this query, perhaps not use CSV to avoid possible mix up w/ single and double digit id's (eg. 1 and 11) , like= '%:1:%' using ":" as delimeter.. i do know using LIKE will slow the query down but i dont think by that much.

Sorry, I'm going to facepalm here. You need to read a book on how to use relational databases if you really believe that you need a million queries to do this. Even a naive implementation would only require 1000 queries (i.e., 1 for each user) because selecting the group ids from the users/groups join table is enough.

lolz , i was just trying to make a SIMPLE example. There can be situations where a single query w/ multiple JOINs will not be enough to pull out the data the you want, or so i expect (in the realm of possibilities) ...and in these cases you're gona have to issue at least 1 query for every row you pull out.. you can lemme know if i'm wrong about this assumption.. but i have had experience such .. specially w/ complex apps integration, you can't always have control over the structures, so you gona have to go w/ it instead of changing the whole deisgn of everything.. hence instead of doin so I simply add "related" column to the master table w/c needs to integrate w/ a table from a different app.

Yes, a join of the users table with the users/groups join table will come in handy to reduce your hypothetical 1 million queries to a single query. If the user ids are already known, a join is not needed at all.

As for overhead of joins... here's a challenge: suppose that each group has a name. Now, I want to retrieve for each of 1000 users the names of the groups that they belong to. How will you do that? I can do that with a single query without making use of the CSV column by also joining on the group table.

this one's easy really.. 1st query is to pull out ALL the group name and related IDs..

set them in an array $group_name_array['group_id'] = $group_name;

then for every user, w/ the group csv 1,3,4,5

break apart the csv, and loop through it

$cur_group_name = $group_name_array[$index];

and there you have all the group names..
so it's like 1001 queries total lolz

😛

laserlight

Tea_J wrote:
I was thinking of a like %% approach on this query, perhaps not use CSV to avoid possible mix up w/ single and double digit id's (eg. 1 and 11) , like= '%:1:%' using ":" as delimeter.. i do know using LIKE will slow the query down but i dont think by that much.

I think you missed my question 😉

Tea_J wrote:
i was just trying to make a SIMPLE example.

You also have to get your analysis correct. I mean, it would be downright dishonest or incompetent if I claimed, with no intention of exaggerating, that by using a CSV or whatever format field you have in mind, you need a billion queries to do anything.

Tea_J wrote:
There can be situations where a single query w/ multiple JOINs will not be enough to pull out the data the you want, or so i expect (in the realm of possibilities) ...and in these cases you're gona have to issue at least 1 query for every row you pull out.. you can lemme know if i'm wrong about this assumption..

Yes, it is certainly possible that due to choices made in the design of the database that the desired information cannot be obtained with a constant number of queries.

Tea_J wrote:
but i have had experience such .. specially w/ complex apps integration, you can't always have control over the structures, so you gona have to go w/ it instead of changing the whole deisgn of everything.. hence instead of doin so I simply add "related" column to the master table w/c needs to integrate w/ a table from a different app.

Bad example: your '"related" column' can be substituted by a join table, which is precisely what johanafm praised as "the way to go" in this thread.

Tea_J wrote:
so it's like 1001 queries total

Actually, that is only two queries in total, but you will be simulating a join in PHP.

But this is really related to my question of whether you will include a CSV (or whatever format) column for the group table. If it turns out that what you really want to deal with are group names, this cached field of group ids will not be very useful. My point is really that until you have evidence that not using such a field is significantly worse off than using such a field, you should not use such a field because it introduces duplication of data.