I think my title is kinda unclear but I don't konw how to tell that otherwise.

My problem is:

We have users that belong to groups, there are many types of groups and any user belong to exaclty one group for each type.

Example: With group types A, B and C, containing respectively the groups (A1; A2; A3), (B1; B2) and (C1; C2; C3)
Every User must have a list of groups like [A1, B1, C1] or [A1, B2, C3] but never [A1, A2, B1] or [A1, C2]

We have messages that target to certain groups but not just a union, it can be more complex collection operations

Example: we can have message intended to [A1, B1, C3], [A1, *, *], [A1|A2, *, *] or even like ([A1, B1, C2] | [A2, B2, C1])

(* = any group of the type, | = or)

Messages are stored in a SQL DB, and users can retrieve all messages intended to their groups

How may I store messages and make my Query to reproduce this behavior ?

CodePudding user response：

So you have 3 main tables:

• Messages
• Users
• Groups

You then create 2 relationship tables:

• Message-Group
• User-Group

If you want to limit users to have access to just "their" messages then you join: User > User-Group > Message-Group > Message

CodePudding user response：

An option could be to encode both the user groups and the message targets in a (big) integer built on the powers of 2, and then base your query on a bitwise AND between user group code and message target code.

The idea is, group 1 is 1, group 2 is 2, group 3 is 4 and so on.

Level 1:

Assumptions:

• you know in advance how many group types you have, and you have very few of them
• you don't have more than 64 groups per type (assuming you work with 64-bit integers)
• the message has only one target: A1|A2,B..,C... is ok, A*,B...,C... is ok, (A1,B1,C1)|(A2,B2,C2) is not.

Solution:

• Encode each user group as the corresponding power of 2
• Encode each message target as the sum of the allowed values: if groups 1 and 3 are allowed (A1|A3) the code will be 1 4=5, if all groups are allowed (A*) the code will be 2**64-1
• you will have a User table and a Message table, and both will have one field for each group type code
• The query will be WHERE (u.g1 & m.g1) * (u.g2 & m.g2) * ... * (u.gN & m.gN) <> 0

Level 2:

Assumptions:

• you have some more group types, and/or you don't know in advance how many they are, or how they are composed
• you don't have more than 64 groups in total (e.g. 10 for the first type, 12 for the second, ...)
• the message still has only one target as above

Solution:

• encode each user group and each message target as a single integer, taking care of the offset: if the first type has 10 groups they will be encoded from 1 to 1023 (2**10-1), then if the second type has 12 groups they will go from 1024 (2**10) to 4194304 (2**(10 12)-1), and so on
• you will still have a User table and a Message table, and both will have one single field for the cumulative code
• you will need to define a function which is able to check the user group vs the message target separately by each range; this can be difficult to do in SQL, and depends on which engine you are using
• following is a Python implementation of both the encoding and the check

class IdEncoder:
    def __init__(self, sizes):
        self.sizes = sizes
        self.grouplimits = {}
        offset = 0
        for i,size in enumerate(sizes):
            self.grouplimits[i] = (2**offset, 2**(offset   size)-1)
            offset  = size

    def encode(self, vals):
        n = 0
        for i, val in enumerate(vals):
            if val == '*':
                g = self.grouplimits[i][1] - self.grouplimits[i][0]   1
            else:
                svals = val.split('|')
                g = 0
                for sval in svals:
                    g  = 2**(int(sval)-1)
                if i > 0:
                    g *= self.grouplimits[i][0]
            print(g)
            n  = g
        return n

    def check(self, user, message):
        res = False
        for i,size in enumerate(self.sizes):
            if user%2**size & message%2**size == 0:
                break
            if i < len(self.sizes)-1:
                user >>= size
                message >>= size
        else:
            res = True
        return res

c = IdEncoder([10,12,10])
m3 = c.encode(['1|2','*','*'])
u1 = c.encode(['1','1','1'])
c.check(u1,m3)
True

u2=c.encode(['4','1','1'])
c.check(u2,m3)
False

Level 3:

Assumptions:

• you adopt one of the above solutions, but you need multiple targets for each message

Solution:

• You will need a third table, MessageTarget, containing the target code fields as above and a FK linking to the message
• The query will search for all the MessageTarget rows compatible with the User group code(s) and show the related Message data