Дървовидна структура в MySQL

[erik_1001]

Здравейте. Надявам се няма да ви досадя и доколкото видях няма друга такава тема в сайта, ако има, простете, ще се зарадвам на връзка и ще я прочета.
Въпросът ми е как да създам дървовидна структура от безкраен брой елементи, които могат да бъдат премествани, добавяни и активирани/деактивирани (със съответните им подчинени елементи), опцията за изтриване не ме вълнува особено.
За целта си имам вече съставена таблица:

section_ID 	section_parent_ID 	section_level   	title 
1                      0                                      1              title 1 
2                      0                                      1              title 2 
3                      0                                      1              title 3 
4                      2                                      2              subtitle 1 of title 2

Проблемът идва от това, най-вече може би, че имам не особено дълбоки познания по SQL, после, може би, че е безкраен броят подчинени елементи.
Това което четох е че има два основни начина за структуриране на данните, единия е с Adjacency Model, другия е Nested Model. От това, което прочетох, на мен работа би ми вършил по-скоро Adjacency Model-а.
Изключително бих се радвал ако някой ми помогне в структурирането и обяснението на заявките или поне ми даде връзка, където да прочета обяснено какво трябва да правя, защото не желая папагалски да копирам код, ами да го разбера, за да не ви занимавам друг път с моите лаишки проблеми.
Благодаря ви предварително.

 

[stonybg]

Сторед мене е въпрос на логика аз лично бих го направил по следния начин

-------------------------------------------------------------------------
| ID |  ID_Parent                      | Submenu_id      | Title      |
-------------------------------------------------------------------------
| 1   |  id na stoka /novina etc  |         0                | bla bla |
-------------------------------------------------------------------------
|  2  |     NULL                           |           1              | bla bla |
-------------------------------------------------------------------------

като там кадето суб менюто е нула (или NULL) да го разпознава като главно меню

а там кадето Паренд ид то е NULL или 0 определяш дали е суб меню като по Submenu_id определяш кой е родителя по ID-то на реда в таблицата.

Другото е само да се разпише логиката на php или езика на който пишеш


моята препорака е да разделиш в таблици Титле менюто и чилдовете за да оскориш работата с SQL-а

 

[palex]

Това е болното място на релационните бази данни. Аз лично до сега съм го реализирал с JOIN-ове като съм си направил VIEW, което е ограничено в броя нива, но когато са ми нужни повече - просто го модифицирам. Направил съм си тригер на таблицата, който "гледа" до кое ниво стига и прегенерира view-то когато се наложи. Ето примерно VIEW с твоята таблица до 4-то ниво, което ти можеш да си разшириш:

[sql]
select i.ID, i.id_parent, 1 as Level from items i

union all

select i.ID, i2.ID, 2 as Level from items i
left join sServ.items i2 on i2.id=i.parent_id
where i2.id is not null

union all

select i.ID, i3.ID, 3 as Level from items i
left join sServ.items i2 on i2.id=i.parent_id
left join sServ.items i3 on i3.id=i2.parent_id
where i3.id is not null

union all

select i.ID, i4.ID, 4 as Level from items i
left join sServ.items i2 on i2.id=i.parent_id
left join sServ.items i3 on i3.id=i2.parent_id
left join sServ.items i4 on i4.id=i3.parent_id
where i4.id is not null

[/sql]

Не съм го тествал, така че може да има някоя синтактична грешка. Идеята е да получиш select, в който имаш ID и ParentID, като родителския възел може да е на по-горно ниво. Така когато трябва да се направи селекция за всички (например стоки), които са в някоя група аз просто правя нещо такова:
[sql]
select * from items i where exists(select id from itemgroupview g where g.id=i.id and g.ID_Parent=<group_id>
[/sql]

Надявам се това да ти е полезно. Моята почти 15 годишна практика със складови и счетоводни системи до момента не ми е показала нужда от повече от 10 нива и то при ненормални потребители, които малко след като започнат да работят опростяват структурата.

 

[ivo_pi4a]

Това което е дал @palex е вариант, но с толкова много join-ове ще се зарежда много бавно страницата. Препоръчвам ти да се замислиш за цикъл в цикъла :?: От опит мога да ти кажа, че зарежда в пъти по-бързо и има същия резултат

 

[palex]

Зависи от базата Иво. Аз имам около 150 потребителя, непрекъснато ровещи в дървото, а базата ми е около 230 000 записа и трябва да ти кажа, че postgres-a се справя за около 2 секунди, а PHP-то с memcached върху сериозно 8 ядрено желязо с 32Г памет и 8 истински SAS-а в RAID 10 се справя за около 7 секунди с apache и 5 секунди върху nginx. Така че конкретното решение зависи от конкретната ситуация, а с индекси по ID и ID_Parent не е особен проблем до 1-2 мил.записа.
Все пак избора на решение наистина много зависи от конкретната ситуация, аз се спрях на това защото генерирам дървото с ajax и итеративно е много тегаво.

 

[stonybg]

palex да твойта база е малка аз работя с една ERP система и там са 1,5м до 2м записа (при дйойнове означава 10-15 сек. моткане) и 24GB рам с два ксеон процесора се задъхват, затова Иво е прав цикъл в цикъла е много подобре от join-ве, но на erik_1001 му трябва повече структората, най добрия вариянт е да раздели главните менюта от суб менютата в различни таблици с форенкейове. и като index-ира кейовете така подобрява скороста на базата а и щте спести място при по големи бази.

 

[palex]

2М записа си е впечатляващо - като те стигна ще питам за хинтове

 

[stonybg]

винаги съм помагал на колеги ,
п.с За заявки в SQL е желателно да се гонят 400 мили секунди това е златната среда