0

I want to store multiple values into a single row for column name, value. This is what I have for now.
Table user_values_definition

id user_status name value
1 approved User can't access section 1, not qualified no_access_section_1
2 approved User Can't access section 1, User can't access section 5 no_access_section_1, no_access_section_3

Table user_values

id user_id user_values_definition_id
1 121000 2

What is the best way to store data in order to have a good structure of the database and have a good performance. For now I can think of a JSON data type to keep multiple values but I don't know what to say

Robert
  • 39
  • 6
  • 7
    *Store multiple values in one column*. No don't do that, you'll be *forever* regretting it. Read up on normalisation. – Stu May 02 '23 at 08:18
  • 1
    If you don't query/filter on the data that sits inside this JSON and only use it for audit/history - then it is okay to use JSON. In all other cases - you should use a separate (child) table for these values and split them in rows (one row per value). – IVO GELOV May 02 '23 at 08:30
  • 1
    Think about SET datatype. – Akina May 02 '23 at 08:52

1 Answers1

1

The best way to store your data would be to have individual entries in user_values_definition and to link multiple values via user_values instead of using a set in user_values_definition.

E.g: A user can not access section 1 & section 5. There should be two entries in user_values, linking the user two the related entries in your definition.

id user_status name value
1 approved User can't access section 1 no_access_section_1
... ... ... ...
5 approved User Can't access section 5 no_access_section_5
id user_id user_values_definition_id
1 121000 1
2 121000 5

Model: Relation between user, user_values & user_values_definition

That allows you to have the minimum amount of entries in user_values_definition, while you can still cover all cases of user access via your intermediary table user_values. As it is a small table, it will still be good in terms of performance, even if having a lot of entries.

You can use so-called "views" (basically selects stored on the database, that act like a selectable table) to make the selection easier.

However, is for whatever reason you really do want to have an entry with a set of values (trust me you don't), saving a JSON would be fine, otherwise just use a delimiter such as ; or |.

A-Tech
  • 806
  • 6
  • 22