Cпособы хранения деревьев в реляционной БД

Существует 4 основных способа хранения деревьев в реляционной БД:

Adjacency List 

Matherialized Path - полный путь до узла хранится в виде строки типа 0035.0993.7324.1256 (т.е. узел верхнего уровня содержит строку 0035, узел второго уровня - строку 0035.0993 и т.д.)

Nested Set - для каждого узла хранятся 2 числа: левый и правый индексы. Причём левый индекс узла меньше левого индекса любого потомка этого узла, а правый индекс соответственно больше.

Closure Table - отдельно храним данные, отдельно иерархию.

Также есть комбинации этих способов.

Ссылки:

http://janiwanow.com/blog/closure-table-part-1/

http://janiwanow.com/blog/closure-table-part-2/

https://habrahabr.ru/post/193166/

http://www.php.su/articles/?cat=phpdb&page=013

https://www.opennet.ru/docs/RUS/hierarchical_data/

http://www.getinfo.ru/article610.html

https://habrahabr.ru/post/153861/

Есть готовые решения для фреймворков:

https://github.com/paulzi/yii2-adjacency-list

https://github.com/paulzi/yii2-nested-sets

http://sqlfiddle.com/#!2/7e58b/3

https://habrahabr.ru/post/266155/

https://github.com/dakujem/oliva-tree

https://github.com/pistol88/yii2-tree

комментарии (3)

Boristiq Boristiq 2021-01-09 19:21:27
Medicines information leaflet. Short-Term Effects. <a href="https://viagra4u.top">cost viagra without prescription</a> in Canada. Best about medicine. Read information now. [url=http://bluesnow.jpn.org/blue/yui/?date=20091107#c01]Everything information about medicines.[/url] [url=https://bragagnolosrl.it/2020/09/30/documento-unico-di-regolarita-contributiva-durc-nella-fase-di-emergenza-da-covid-19/#comment-20645]Actual news about medicine.[/url] [url=https://amp.en.vaskar.co.in/translate/1?to=ru&from=en&source=Medicament%20information%20leaflet.%20Generic%20Name.%20%3Ca%20href%3D%22https%3A%2F%2Fviagra4u.top%22%3Ehow%20can%20i%20get%20viagra%20no%20prescription%3C%2Fa%3E%20in%20US.%20Actual%20about%20medicines.%20Read%20now.%20%0D%0A%3Ca%20href%3Dhttps%3A%2F%2F925.lviv.ua%2Fgvks-5-1400h500h350-mm%3EBest%20information%20about%20pills.%3C%2Fa%3E%20%3Ca%20href%3Dhttp%3A%2F%2Fafootincoldwater.com%2Fphpafic%2Fviewtopic.php%3Ff%3D6%26t%3D749%3ESome%20news%20about%20medicines.%3C%2Fa%3E%20%3Ca%20href%3Dhttps%3A%2F%2Factivity.rmu.ac.th%2Fhome%2FViewActivity%2F1841%3EEverything%20trends%20of%20pills.%3C%2Fa%3E%20%2092f6fcf%20&result=%D0%98%D0%BD%D1%84%D0%BE%D1%80%D0%BC%D0%B0%D1%86%D0%B8%D0%BE%D0%BD%D0%BD%D1%8B%D0%B9%20%D0%BB%D0%B8%D1%81%D1%82%D0%BE%D0%BA%20%D0%BF%D0%BE%20%D0%BB%D0%B5%D0%BA%D0%B0%D1%80%D1%81%D1%82%D0%B2%D0%B5%D0%BD%D0%BD%D1%8B%D0%BC%20%D1%81%D1%80%D0%B5%D0%B4%D1%81%D1%82%D0%B2%D0%B0%D0%BC.%20%D0%A0%D0%BE%D0%B4%D0%BE%D0%B2%D0%BE%D0%B5%20%D0%98%D0%BC%D1%8F.%20%3Ca%20href%3D%22https%3A%2F%2Fviagra4u.top%22%20%3E%20%D0%BA%D0%B0%D0%BA%20%D1%8F%20%D0%BC%D0%BE%D0%B3%D1%83%20%D0%BF%D0%BE%D0%BB%D1%83%D1%87%D0%B8%D1%82%D1%8C%20%D0%92%D0%B8%D0%B0%D0%B3%D1%80%D1%83%20%D0%B1%D0%B5%D0%B7%20%D1%80%D0%B5%D1%86%D0%B5%D0%BF%D1%82%D0%B0%3C%20%2F%20a%20%3E%20%D0%B2%20%D0%A1%D0%A8%D0%90.%20%D0%A1%D0%BE%D0%B1%D1%81%D1%82%D0%B2%D0%B5%D0%BD%D0%BD%D0%BE%20%D0%BE%20%D0%BB%D0%B5%D0%BA%D0%B0%D1%80%D1%81%D1%82%D0%B2%D0%B0%D1%85.%20%D0%90%20%D1%82%D0%B5%D0%BF%D0%B5%D1%80%D1%8C%20%D1%87%D0%B8%D1%82%D0%B0%D0%B9.%20%3Ca%20href%3Dhttps%3A%2F%20%2F%20925.lviv.ua%20%2F%20gvks-5-1400h500h350-mm%3E%D0%BB%D1%83%D1%87%D1%88%D0%B0%D1%8F%20%D0%B8%D0%BD%D1%84%D0%BE%D1%80%D0%BC%D0%B0%D1%86%D0%B8%D1%8F%20%D0%BE%20%D1%82%D0%B0%D0%B1%D0%BB%D0%B5%D1%82%D0%BA%D0%B0%D1%85.%3C%20%2F%20a%3E%20%3Ca%20href%3Dhttp%3A%2F%2Fafootincoldwater.com%20%2F%20phpafic%20%2F%20viewtopic.php%3Ff%3D6%26t%3D749%3E%D0%BD%D0%B5%D0%BA%D0%BE%D1%82%D0%BE%D1%80%D1%8B%D0%B5%20%D0%BD%D0%BE%D0%B2%D0%BE%D1%81%D1%82%D0%B8%20%D0%BE%20%D0%BB%D0%B5%D0%BA%D0%B0%D1%80%D1%81%D1%82%D0%B2%D0%B0%D1%85.%3C%20%2F%20a%3E%20%3Ca%20href%3Dhttps%3A%2F%2Factivity.rmu.ac.th%2Fhome%20%2F%20ViewActivity%20%2F%201841%3E%D0%B2%D1%81%D0%B5%20%D1%82%D0%B5%D0%BD%D0%B4%D0%B5%D0%BD%D1%86%D0%B8%D0%B8%20%D1%82%D0%B0%D0%B1%D0%BB%D0%B5%D1%82%D0%BE%D0%BA.%3C%20%2F%20a%3E%2092f6fcf]Best information about drug.[/url] 010171_
Boristiq Boristiq 2021-01-09 21:45:13
Medicine information leaflet. What side effects? depakote without dr prescription https://depakote4u.top in US Some what you want to know about drugs. Get information now. http://barcelonaforum.ru/obschii-2/xxx-vido-downolt-7833/#post200632 All information about medicines. https://amp.en.vaskar.co.in/translate/1?to=ru&from=en&source=Drug%20information%20leaflet.%20Drug%20Class.%20%0D%0Asingulair%20buy%20https%3A%2F%2Fsingulair4u.top%20in%20Canada%20%0D%0AAll%20information%20about%20medicines.%20Get%20information%20now.%20%0D%0A%20%0D%0Ahttp%3A%2F%2Fadeoluadewumi.org%2Fblog%2Fcovenant-of-exemption%20Everything%20about%20meds.%20https%3A%2F%2Fakarui-mirai.blog.ss-blog.jp%2FUSS-Forrestal-CVA-59-01%3Fcomment_success%3D2021-01-10T00%3A13%3A22%26time%3D1610205202%20Actual%20information%20about%20meds.%20http%3A%2F%2Fa.mblg.tv%2Fmoiil%2Fentry%2F3%2F%23comments%20Best%20about%20drug.%20%20a3c01d9%20&result=%D0%98%D0%BD%D1%84%D0%BE%D1%80%D0%BC%D0%B0%D1%86%D0%B8%D0%BE%D0%BD%D0%BD%D0%B0%D1%8F%20%D0%B1%D1%80%D0%BE%D1%88%D1%8E%D1%80%D0%B0%20%D0%BE%20%D0%BD%D0%B0%D1%80%D0%BA%D0%BE%D1%82%D0%B8%D0%BA%D0%B0%D1%85.%20%D0%9A%D0%BB%D0%B0%D1%81%D1%81%20%D0%9D%D0%B0%D1%80%D0%BA%D0%BE%D1%82%D0%B8%D0%BA%D0%BE%D0%B2.%20singulair%20%D0%BA%D1%83%D0%BF%D0%B8%D1%82%D1%8C%20https%3A%2F%2Fsingulair4u.top%20%D0%B2%20%D0%9A%D0%B0%D0%BD%D0%B0%D0%B4%D0%B5%20%D0%B2%D1%81%D1%8F%20%D0%B8%D0%BD%D1%84%D0%BE%D1%80%D0%BC%D0%B0%D1%86%D0%B8%D1%8F%20%D0%BE%20%D0%BB%D0%B5%D0%BA%D0%B0%D1%80%D1%81%D1%82%D0%B2%D0%B0%D1%85.%20%D0%9F%D0%BE%D0%BB%D1%83%D1%87%D0%B8%D1%82%D0%B5%20%D0%B8%D0%BD%D1%84%D0%BE%D1%80%D0%BC%D0%B0%D1%86%D0%B8%D1%8E%20%D0%BF%D1%80%D1%8F%D0%BC%D0%BE%20%D1%81%D0%B5%D0%B9%D1%87%D0%B0%D1%81.%20http%3A%2F%2Fadeoluadewumi.org%2Fblog%2Fcovenant-of-exemption%20%D0%B2%D1%81%D0%B5%2C%20%D1%87%D1%82%D0%BE%20%D0%BA%D0%B0%D1%81%D0%B0%D0%B5%D1%82%D1%81%D1%8F%20%D0%BB%D0%B5%D0%BA%D0%B0%D1%80%D1%81%D1%82%D0%B2.%20https%3A%2F%2Fakarui-mirai.blog.ss-blog.jp%2FUSS-Forrestal-CVA-59-01%3Fcomment_success%3D2021-01-10T00%3A13%3A22%26time%3D1610205202%20%D0%B0%D0%BA%D1%82%D1%83%D0%B0%D0%BB%D1%8C%D0%BD%D0%B0%D1%8F%20%D0%B8%D0%BD%D1%84%D0%BE%D1%80%D0%BC%D0%B0%D1%86%D0%B8%D1%8F%20%D0%BE%20%D0%BB%D0%B5%D0%BA%D0%B0%D1%80%D1%81%D1%82%D0%B2%D0%B0%D1%85.%20http%3A%2F%2Fa.mblg.tv%2Fmoiil%2Fentry%2F3%2F%23comments%20%D0%BB%D1%83%D1%87%D1%88%D0%B5%20%D0%B2%D1%81%D0%B5%D0%B3%D0%BE%20%D0%BE%20%D0%BD%D0%B0%D1%80%D0%BA%D0%BE%D1%82%D0%B8%D0%BA%D0%B0%D1%85.%20a3c01d9 Everything news about medication. https://de.streema.com/comments/posted/?c=1271026 All information about meds. 52735a8
Boristiq Boristiq 2021-01-10 02:35:32
Pills information sheet. Short-Term Effects. <a href="https://viagra4u.top">where to buy cheap viagra without dr prescription</a> in Canada. Everything information about medicine. Get now. [url=http://gossi.co.kr/home/201204205zlhCUhRQQ/home.php?go=pds_list&pds_admin_num=8710&num=147863&start=0&mode=&field=&s_que=&year=&month=&tag=&gong_number=]Actual trends of drugs.[/url] [url=https://amp.de.vaskar.co.in/translate/1?to=en&from=de&source=Medicament%20information%20for%20patients.%20Long-Term%20Effects.%20%3Ca%20href%3D%22https%3A%2F%2Fprednisone4u.top%22%3Ecan%20i%20get%20prednisone%20without%20insurance%3C%2Fa%3E%20in%20USA.%20Some%20about%20drugs.%20Read%20information%20here.%20%0D%0A%5Burl%3Dhttps%3A%2F%2Fdienmaysonganh.com%2Fsan-pham%2Fmay-phat-dien-chay-xang-honda-sh4500ex-co-de.html%5DSome%20about%20drug.%5B%2Furl%5D%20%5Burl%3Dhttps%3A%2F%2Fhkppltravel.com%2F46614%2Flifestyle%2F7-eleven-x-disney-tsum-tsum-%25e6%258e%25a8%25e5%25b2%2580%25e7%2599%25be%25e8%25ae%258a%25e5%25b0%258f%25e6%25ab%2583%25e6%25a1%25b6%2F%23comment-5277%5DActual%20what%20you%20want%20to%20know%20about%20medicament.%5B%2Furl%5D%20%5Burl%3Dhttps%3A%2F%2Factivity.rmu.ac.th%2Fhome%2FViewActivity%2F754%5DActual%20what%20you%20want%20to%20know%20about%20medicines.%5B%2Furl%5D%20%208032308%20&result=Medicine%20information%20for%20patients.%20Long-Term%20Effects.%20%3Ca%20href%3D%22https%3A%2F%2Fprednisone4u.top%22%3Ecan%20i%20get%20prednisone%20without%20insurance%3C%2Fa%3E%20in%20the%20USA.%20Some%20about%20drugs.%20Read%20the%20information%20here.%20%5Burl%3Dhttps%3A%2F%2Fdienmaysonganh.com%2Fsan-pham%2Fmay-phat-dien-chay-xang-honda-sh4500ex-co-de.html%5DSome%20about%20the%20drug.%5B%2Furl%5D%20%5Burl%3Dhttps%3A%2F%2Fhkppltravel.com%2F46614%2Flifestyle%2F7-eleven-x-disney-tsum-tsum-%E6%8E%A8%E5%B2%80%E7%99%BE%E8%AE%8A%E5%B0%8F%E6%AB%83%E6%A1%B6%2F%23comment-5277%5DActual%20what%20you%20want%20to%20know%20about%20medicine.%5B%2Furl%5D%20%5Burl%3Dhttps%3A%2F%2Factivity.rmu.ac.th%2Fhome%2Fview%20activity%2F754%5DActual%20what%20you%20want%20to%20know%20about%20medicines.%5B%2Furl%5D%208032308]Some what you want to know about medicament.[/url] [url=https://amp.pl.vaskar.co.in/translate/1?to=en&from=pl&source=Medicament%20information%20sheet.%20Effects%20of%20Drug%20Abuse.%20%3Ca%20href%3D%22https%3A%2F%2Fviagra4u.top%22%3Ewhere%20to%20get%20cheap%20viagra%20without%20rx%3C%2Fa%3E%20in%20US.%20Everything%20news%20about%20meds.%20Get%20here.%20%0D%0A%5Burl%3Dhttps%3A%2F%2Fbellamumma.com%2Ftried-loved-cotton-kids-new-improved-fit-pyjamas%2F%23comment-13606%5DSome%20information%20about%20medicine.%5B%2Furl%5D%20%5Burl%3Dhttps%3A%2F%2Flogisticstrendsandinsights.com%2Fcan-walmart-find-profitable-success-in-canada%2F%23comment-113173%5DSome%20news%20about%20medicament.%5B%2Furl%5D%20%5Burl%3Dhttps%3A%2F%2Fcinderellascloset.co.za%2Ftypes-of-wedding-dresses%2F%23comment-1952%5DSome%20trends%20of%20medication.%5B%2Furl%5D%20%20194efc5%20&result=Medicament%20information%20sheet.%20Effects%20of%20Drug%20Abuse.%20%3Ca%20href%3D%22https%3A%2F%2Fviagra4u.top%22%3Ewhere%20to%20get%20cheap%20viagra%20without%20rx%3C%2Fa%3E%20in%20US.%20All%20news%20about%20medicines.%20Get%20here.%20%5Burl%3Dhttps%3A%2F%2Fbellamumma.com%2F%D0%BF%D1%80%D0%BE%D0%B1%D0%BE%D0%B2%D0%B0%D0%BB%D0%B8-%D0%BF%D0%BE%D0%BD%D1%80%D0%B0%D0%B2%D0%B8%D0%BB%D0%BE%D1%81%D1%8C-cotton-kids-new-improved-fit%20pyjamas%2F%23comment-13606%5DSome%20information%20about%20medicine.%5B%2Furl%5D%20%5Burl%3Dhttps%3A%2F%2Flogisticstrendsandinsights.com%2Fcan-walmart-find-profitable-success-in-canada%2F%23comment-113173%5DSome%20news%20about%20medicament.%5B%2Furl%5D%20%5Burl%3Dhttps%3A%2F%2Fcinderellascloset.%D1%87%D1%82%D0%BE.%D1%81%2Ftypes-of-wedding-dresses%2F%23comment-1952%5DSome%20trends%20of%20medication.%5B%2Furl%5D%20194efc5]Everything about drug.[/url] ccbf6f2