CODEWITHSUNDEEP
javascriptphpmysql
hiyum
hiyum

Reputation: 140

how should i store lists of lists in MYSQL

I want to store this list of lists but I don't know how to store it in MySQL

list[x][y] the items in this list contains {li:[{x:x,y:y}] , pos:{x:y}}

list[x][y].li[z].x
list[x][y].li[z].y
list[x][y].pos.x
list[x][y].pos.y

for better undersing, please have a look at thisthis

edited:enter image description here

is this right? so this means i will only have 2 tables?

Upvotes: 1

Views: 566

Answers (1)

Sasha Pomirkovany
Sasha Pomirkovany

Reputation: 542

You should use a separate table with sub-lists that have a column parent_id, and then a third table with actual list items of low level lists.

The query for this will look like this:

SELECT li.x, li.y, sl.id
FROM li_items li  
JOIN sub_lists sl on li.list_id = sl.id
JOIN lists l on sl.parent_id = l.id;

The process of converting the result rows depends on if you use some ORM or plain mysql client.

You could also store it as a JSON, as deleted answer has suggested, but than you wan't be able to query specific items without selecting and parsing all the lists. You could also use MySQL's JSON column, but In your case having separate tables seems to be better

Upvotes: 2

Related Questions