How can MySQL implement this relationship between two tables:

  • records from table А store unique numeric id = 3, 8, 67, 75, 100
  • in B there is a list field that can store values ​​only from the id field of table А , and there can be several of them at the same time, for example list=(3,67,8)
  • so and you connect that? usually stored as A: id, value B: id (if necessary), A_ID is zb '
  • Can [this] [1] fit? Although I am not a supporter of such methods. It is better to enter a one-to-many table [1]: dev.mysql.com/doc/refman/5.0/en/set.html - Donil
  • most likely you need one-to-many and then when requesting a PIVOT do - zb '
  • If you don’t mind storing as an array, you can convert the array into a Json object and put it into table B as a string. It is often used in practice. And the processing of such data - lay on PHP, in order to avoid complex requests, although you can argue here. But in your case - I recommend. - Jony

1 answer 1

Usually this is not done, denormalizing the data in this way, you lose all the advantages of the DBMS: processing speed, a rich and flexible set manipulation language. At the same time, you keep all the flaws: complex and old SQL query language, created at a time when developer time was cheap and computer - expensive, poor and unproductive string manipulation.

If you want to extract all the benefits from MySQL, you'd better normalize the schema.

  • records from table А store unique numeric id = 3, 8, 67, 75, 100
  • in table B each identifier from the set list=(3,67,8) has a separate row

for example

 1 3 2 67 3 8

With this organization of data, you get the ability to process using all of the SQL mechanisms (subqueries, connections, multiple set processing operators). If it is not convenient for you to rework table B , get an intermediate table С , where you store the data in a normalized form - believe, despite the apparent additional complexity, it will pay off when using and maintaining the application.