Json or multiple row

[Phil Siyam]

Hi,


I need to store following data :

Traditional I used this table structure since we did not have json field data type :

Key_id, Attr , position
1, A , 1
1, B , 2
2, A, 1
2, B, 2
2, A, 3
3, A, 1
4, A, 1
4, B,2
4, C, 3
4, D, 4

Column Attr can have any combination of Alphabets, each unique combination is assigned key_id.


Each time combination is created I need to check, does combination exists in same order if no insert new combination and assign new unique key_id

Occasionally I have to check in which row alphabet A exists or B exist etc

Table may end up having 150000 rows

I have view that coverts above table to horizontal form using group_concat function

Converting above data in horizontal form :-


1, (A,B)
2, (A,B,A)
3, (A)
4, (A,B,C,D)
5, (A,A,Z)
6, (B,A)

Is it better to store data in table with 2 columns Key_id and attr (json type)? Are there any benefits of one approach over other ?


Json approach will end up lot lesser rows may be 40000 rows

MySQL Ver 8

Thanks in Advance