[thelist] Complex SQL problem. Groups and rules

[Richard Livsey]

I'm working on bit of semi complicated SQL that I can't get my head 
round. Apologies for the rambling nature of this email, this is a bit of 
a brain dump!

Firstly a bit of background.

We have a bunch of groups, and these groups have rules that users must 
meet in order for them to see them.
I'm trying to write a query that will return all the groups that a 
specific user is eligible to join.

The group may have any number of rules, or none at all (any user can 
join it).

A rule is an entry in a lookup table (of which we have 4). For this we 
have 4 rules - boroughs, wards, roles and sics.
And address has a borough and a ward, a user has a role and an sic.

Now a user may have a number of different addresses and the rules can 
only apply to one of their addresses, IE if there is a rule to say that 
the address must be in borough 'a' and ward 'b' and you have 2 addresses:

address1
 - borough 'a'
 - ward 'y'
 
address2
 - borough 'z'
 - ward 'b'
 
Then this user can't join the group. However if they had an address like 
this then they could join:

address
 - borough 'a'
 - ward 'b'

To make this even more complicated, a groups rules can be applied as 
'AND' or 'OR'. IE 'users must match all rules' or 'users can match any 
rules'.

Think it may take a couple of queries (at least) to work this one out.. 
but so far I've not been able to get my head round it.

So as an example, a group set of rules could be something like:

from as simple as:

users who have role 'x'
users who are in borough 'a'

to ones as complex as:

users who are in borough 'a' AND ward 'b' AND have role 'c' AND sic 'd'
users who are in borough 'a' OR borough 'b' OR have role 'c'

(note that we don't mix AND and OR which makes it slightly less 
complicated to work out)

Here are the relevant tables:

addresses
 - id
 - userId
 - boroughId
 - wardId
 
users
 - id
 - roleId
 - sicId

groups
 - id
 - name
 - combination (this is an enum or 'AND' and 'OR')
 
groupBoroughs
 - boroughId
 - groupId
 
groupRoles
 - roleId
 - groupId
 
groupWards
 - wardId
 - groupId
 
groupSics
 - sicId
 - groupId
 
On top of that, we then should only show groups that the user is not 
already a member of. But I'll worry about that later and should be 
simple compared to the rest of it!
Members are stored in the groupMembers table.

groupMembers
 - userId
 - groupId

Ugh.. anyway - any pointers would be greatly appreciated as I've been 
getting kicked about with this one for a few hours now and don't seem to 
be making much headway.
Also - any alternative database layouts which would make the query 
easier would also be great.

I'm sure I've tackled things like this in the past, but it's friday and 
I'm not thinking straight :o)

Thanks in advance.

-- 
R.Livsey
www.livsey.org