Extensive filtering using groups

Databases
1

I’m working on a project now where I have to create an extensive filtering. Users belong to a number of groups and I should process requests when user belongs to several groups. Say find users that are both in golf club and in beer club. (see case #3 here)
There is a solution there too. But I feel it is not optimal. I think there may be something better.

All ideas/suggestions are welcome. Both here and on the blog :wink:
Thanks for help beforehand!

2

Another query:


SELECT
  u.*
FROM
  USER u
INNER JOIN
  UserGroup ug1
ON
  u.id = ug1.userID AND ug1.groupID=7
INNER JOIN
  UserGroup ug2
ON
  u.id = ug2.userID AND ug2.groupID=15

Uses JOINs instead of subqueries, should be faster. Especially if UserGroup.groupID and UserGroup.userID both have an index.

3

COOL!!! Besides speed it is more simple to form such query!!!
Thanks!

Maybe there even more efficient ways of doing this? I’m OK to change DB structure, but I want to preserve the current flexibility.

4

I don’t see why you should change the DB structure to perform this query, or am I missing something?

5

No, I meant that I’m OK to redesign DB if needed in order to achieve the same result more efficiently. For example, I know I can store hierarchy more efficiently with additional fields that allow me to get all subtree of a node with the single query.
I thought that there may be other optimizations similar to this one for my task.