Database table structure: storing lists of values ​​along with regular values

Question

MySQL

DB: MySQL.
Task: store dictionary-like data as id:int->value:string.
Problem: it turned out that sometimes you need a list of values ​​to match one id. In this case, even if the list consists of one element, you still need to distinguish it from the usual value.

I see several solutions, but I don't like any of them.

1) Store data not as a string, but in some format: XML, JSON, etc. Then it will be possible to save the whole object in one string field.
The variant is not liked by the fact that as a result we get data denormalization and the problems associated with it, for example, the inability to operate on list values ​​separately using standard SQL tools. Reading and changing individual elements will have to be implemented by means of the application.

1.a) Store data on a single line with a delimiter. This is a special case of option 1, and the cons are the same.

2) Create a separate table for list values.
The variant is not pleasant that it is necessary to make requests already to two tables both at reading, and at record.

3) Store all the data in one table, just don't make the dictionary row id a unique key, then you can add multiple entries for one id.
I don't like the fact that it is then difficult to determine whether the element is a regular element, or part of a list. Adding a special flag field a la is_list_element is a crutch.

Answer 1

Well, as for me, option 2 (Create a separate table for list values.) Is optimal and standard. Usually they leave it only in non-standard situations. I do not recommend reinventing the wheel.

Answer 2

What you describe is the development of a relational model into a network one ... in mysql, as I know, there are no means for this, postgresql has support for arrays, only performance is not optimal in all cases.
Your task is best solved all the same by serialization. The problem of updating data when expanding the functionality is not so critical as to refuse serialization only on the basis of this.
Also, do not turn a blind eye to the regular implementation of lists with the second table M-1.
And of course, no one bothers to combine both approaches (store data in 2 tables and cache with additional fields in the main one, for example, information about the number of elements in the list, the value of the first element, ..)

Answer 3

Just in case, suddenly decide to use the first option:
dev.mysql.com/doc/refman/5.1/en/xml-functions.html

Answer 4

Create a table
ID ListID Value
where ID is a unique record number in the table (primary key), ListID is some kind of your internal identifier, Value you understand what. Then you can extract it as a single row by typing SELECT * From TableName Where ListID = 1 or several values, which will already make a list for you. Find out how many values ​​you have with one ListID like this: SELECT Count( * ) From TableName Where ListID = 1; and you don't need additional fields to mark whether it's a list or not.
Please note that (according to your options) separators or multivalues ​​will also need to be processed somehow, spending time, and (probably) doing SELECT again, spending additional time and resources on it. With my options, you will just have to join if needed.
Do not by any means lose the unique key in your tables as you wish in 3!

Answer 5

Still, if there are relatively few "list" elements, then the last option has the right to exist.