2

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.

ACL table example User table example

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.

Improve this question
4
  • I could then use INNER JOIN and get all necessary data with a single query You can doit using single query on current structuire too. Are there any drawbacks to such a structure or perhaps better solutions? in general the structure looks as optimal.
    – Akina
    Commented Mar 13, 2020 at 14:45
  • @Akina how would you do it with a single query? Simply joining the tables would result in getting repeated User row for each ACL row. I need 1 User row and all ACL rows that have a corresponding UserID.
    – user3757605
    Commented Mar 13, 2020 at 15:20
  • Create a fiddle with some sample data (or post CREATE TABLE + INSERT INTO, 5-6 users enough), and show desired result for this data.
    – Akina
    Commented Mar 13, 2020 at 16:24
  • @Akina Here it is: db-fiddle.com/f/9KQJAFP1f845pZ1g7SLbeA/1
    – user3757605
    Commented Mar 13, 2020 at 16:54

2 Answers 2

Reset to default
1

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.

Improve this answer
0

Look, maybe it is a query which you need?

SELECT `UserName`, 
       `Score`, 
       GROUP_CONCAT(`GroupID` ) groups_csv,
       CAST(CONCAT('[', 
                   GROUP_CONCAT(`GroupID` ), 
                   ']') AS JSON) groups_json_array
FROM `User` 
INNER JOIN `ACL` ON `User`.UserID = `ACL`.UserID 
WHERE `User`.UserID = 1
GROUP BY `UserName`, `Score`;
Improve this answer
4
  • Are you sure this is any better than just having a JSON field in the first place? What are the advantages of using this method?
    – user3757605
    Commented Mar 13, 2020 at 17:32
  • @user3757605 You may freely swap fields position and/or remove excess field(s). JSON field is more difficult in manipulations (but you may of course). What are the advantages of using this method? One recors instead of several ones.
    – Akina
    Commented Mar 13, 2020 at 17:39
  • Is it necessary to group by every selected column? What if I have a high number of columns in my User table?
    – user3757605
    Commented Mar 13, 2020 at 20:32
  • @user3757605 What if I have a high number of columns in my User table? List them all. Longer query text does ot mean more work to the server in this case. Alternatively you may use ANY_VALUE() function or temporarily change SQL server modse.
    – Akina
    Commented Mar 14, 2020 at 10:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.