Mysql – Is using a JSON data type here appropriate

jsonMySQL

I am using MySQL 8.0 and I have an ACL table, which has two columns int UserID and tinyint GroupID. They both form a composite primary key.
A user can be in many groups, however there are only a few users who have such groups (administrators, moderators, etc.).
I have to get the user's ACL groups when they log in, but because of that I have to perform two SELECT queries, one of User table and another of ACL table.

Would it be a good choice to change the data type of GroupID to JSON and store all group IDs of a user in one field? I could then use INNER JOIN and get all necessary data with a single query and UserID would become the primary key of ACL table. Are there any drawbacks to such a structure or perhaps better solutions?
I intend to mostly just use the SELECT operation on the GroupID field, UPDATE and INSERT would be rarely used.

Best Answer

Simply no, with a single index on UserID you can easy and fast request the groups and delete also simlpy user from groups.

That is all posible with json, but needs much more code and so can cause much more problems.

As long you don't need it in your application and also dont change it much, json is good to store a lot of data, but that is xml also.

Related Solutions

Mysql – Getting the data from a foreign key table

A simple JOIN query would be in order

SELECT B.* FROM UsersClients A
INNER JOIN Clients B ON A.clientid = B.id
WHERE A.userid = 2;

Give it a Try !!!

Mysql – Finding rows for a specified date range

Another way to get the result is this. It finds first all groups that the teacher has surely taught (or is going to) by checking that she has started within the month and then in another subquery it finds - for every group - the last teacher that started at the first day of the month or earlier.

With the unique index you have on the table, the second subquery should be quite efficient. The first subquery would benefit from an index on (teacherid, startdate, groupid):

SELECT groupid
FROM pupilgroupteacher
WHERE teacherid = @teacher 
  AND startdate >= @month + INTERVAL 1 DAY
  AND startdate < @month + INTERVAL 1 MONTH

UNION DISTINCT

SELECT gg.groupid
FROM 
    ( SELECT DISTINCT groupid
      FROM pupilgroupteacher
    ) AS gd
  JOIN pupilgroupteacher AS gg
    ON  gg.groupid = gd.groupid
    AND gg.startdate =
        ( SELECT MAX(gi.startdate)
          FROM pupilgroupteacher AS gi 
          WHERE gi.groupid = gd.groupid
            AND gi.startdate < @month + INTERVAL 1 DAY
        )
WHERE gg.teacherid = @teacher ;

Best Answer

Related Solutions

Mysql – Getting the data from a foreign key table

Mysql – Finding rows for a specified date range

Related Question