Datahierarkier: Att hantera hierarkiska data med EF Core och PostgreSQL | (

Inleiding

Hierarkiska data finns överallt i mjukvaruentwicklungen.:, trådlösa kommentarer,, organisationella diagram,, filesystem,, produktkategorier, och forumdiskussionar.. Den eviga frågan om ", hur lagrar jag ett träd i en relationell databas? ?" har hemsökt utvecklare ända sedan SQLs tidiga tid. först skrev om det här ämnet tillbaka i 2004, och de fundamentala utmaningarna är desamma idag

Varför är hierarkier svåra i SQL

Här är det fundamentala problemet. relationella databaser tänker i sets, inte träd. Som Joe Celko förklarar i sin fantastiska bok Att tänka i sets, SQL fungerar på hela tabeller , inte enskilda rader

Men en hierarki är rekursiv. För att hitta alla avkomma från en nod, måste du

Hitta de närmaste barnen
För varje barn, hitta deras barn
upprepa tills du' har passerat hela underträdet

Den här rekursiva traversalen kartlägger inte på ett naturligt sätt operationer. Du kan uttrycka alla avkomman i vilken djup som helst i en enda SQL-sats utan att behöva något.

Rekursiv CTE (koppelt till SQL :1999, men beräkningsmässigt dyrt
Många omloppsresor till databasen
En smart denormalisering som förutsätter relationer

Varje tillvägagångssätt i denna serie representerar en annan handel.-, bortsättning mellan skrivande komplexitet, ,, läskomplexitet och lagernad överhuvudtaget.., det finns inga gratis luncher. Träd och hierarkier i SQL för smarta, som täcker alla dessa tillvägagångssätt i djup

Det exempel: Threaded Comments

För att göra jämförelserna konkreta, så använder vi threadade kommentarer som vårt körande exempel.

flowchart TD
    subgraph Post["Post: How to Deploy Docker Containers"]
        C1["Comment 1: Great article!<br/>depth 0"]
        C2["Comment 2: Thanks!<br/>depth 1"]
        C3["Comment 3: Very helpful indeed<br/>depth 1"]
        C4["Comment 4: Agreed!<br/>depth 2"]
        C5["Comment 5: What about Kubernetes?<br/>depth 0"]
        C6["Comment 6: That's covered in part 2<br/>depth 1"]
    end

    C1 --> C2
    C1 --> C3
    C3 --> C4
    C5 --> C6

    style Post stroke:#10b981,stroke-width:2px
    style C1 stroke:#6366f1,stroke-width:2px
    style C2 stroke:#8b5cf6,stroke-width:2px
    style C3 stroke:#8b5cf6,stroke-width:2px
    style C4 stroke:#a855f7,stroke-width:2px
    style C5 stroke:#6366f1,stroke-width:2px
    style C6 stroke:#8b5cf6,stroke-width:2px

Vi behöver stödja dessa operationer

få alla kommentarer till en artikel (med trådsstruktur bevarad
få alla förfäder av en kommentar (broadcrumb trailM SK1
få alla barnbarn av en kommentar (omfattande underträde)
Lägg till en ny kommentar ( som ett svar på en existerande kommentar
Skräp en underträd (avlägsna en kommentar och alla svar
Flytta en underträd (revera en kommentar - mer sällsynt

De fem tillvägagångssätten

Varje tillvägagångssätt är detaljerat omfattat i sin egen artikel.

1. Närliggande lista Parentreferens

Ta upp hela artikeln

Den enklaste och mest intuitiva metoden

Hur det fungerar: Varje kommentarer har en nullable ParentCommentId column. Rootkommentar har NULL, svar pekar på sin moder

bäst för: Obekväma hierarkier ( mindre än 5-6 nivåer), frekvent subträds rörelserM SK3 när du vill att EF Coren ' navigerings egenskaperna ska fungera naturligt

Världshandel Att få förfäder eller ättlingar kräver rekursiv CTE eller flera frågeställningar. enkelt att skriva , potentiellt långsamt att läsa djupa träd

2. Slut جدول

Ta upp hela artikeln

Beräknar och lagrar varje förfader i en separat tabell.Istället för att lista ut relationerna vid querytid lagrar vi dem explicit med deras djup.

Hur det fungerar: En separat CommentClosure bordsbutiker ( förfader _id, efterfädernM SK3id~, djupttonningMska5 för varje parMske6 Berättelse mska7 under Berättelsen mmska8 via Berättande MMska9 skulle ha inskrywingar: มsK11 | | (3,4,1), | (4,4,0).

bäst för: Läsa- tunga tillämpningar , när du behöver ställa frågor vid godtyckliga djuper

Världshandel Mer komplexa insertioner ( måste lägga till schließningar ), lager växer med djupet M SK2 rörliga underträd kräver återbyggande schließningarna

3. Materialiserad väg

Ta upp hela artikeln

Speicherar hela ursprunget som en avgränsad raden. Tänk på det som att lagra hela postadressen snarare än bara namnen på gatan

bäst för: Breadcrumb generation, när förfäderna är efterfrågade mer än ättlingarn

Världshandel LIKE-sökningar kan vara långsamma utan korrekta index.

4. Nested Sets

Ta upp hela artikeln

Speglar varje nod till vänster och höger gränsnummer från en djup mellan moderens gränser

Hur det fungerar: Varje kommentarer har Left och Right värden. En nod med L=4, RMSC2 innehåller alla noder där M SK3 < vänster och höger < | | 7. Descendants kan hittas med enkla intervallsökningar

bäst för: Läsa- tunga , skriva- har sällan skenar som kategoriträdeMSC3 Utmärkt för M SK4 få hela underträdet i uppvisningsordning

Världshandel Inseror kräver att uppdatera många rader. ( ändra alla värden för att göra plats.

5. PostgreSQL ltree

Ta upp hela artikeln

PostgreSQL' den inhemska förnyelsen till hierarkiska data . Likt materialiserade vägar men med databasen - nivåoptimering och kraftfull mönstermatching

Hur det fungerar: använder ltree datatyp med vägar som "1.3.7". GiST-index gör det möjligt att utföra effektiva förfädernas sökningar med hjälp av operatörer som @> och <@.

bäst för: PostgreSQL- endast tillämpningar där kvaliteten spelar roll , när du behöver mönstermatchande queryr

Världshandel PostgreSQL- endast , lägger till database-extensions beroenden. Nota Npgsql-leverantör supporterar LINQ-översättningar för ltree via LTree typ, fast rekursiv CTE kräver fortfarande rå SQL

sammanfattning av jämförelse

Omgångssätt			Insert ♫		Query Subtree ♫ ♫ Query Ancestors ♫
Närliggande lista	OM SK1	O≥(n) med CTE	O×(dx) medCTE ♫	♫ O× (1) ♫ ♫	♫ Minimalt ♫
Slut Tabellen	OM SK1d	O~(1) ♪ ♪	♪ O^(1) ♪
Materialiserad väg	*	OM SK1	O~(s) ♫ ♫	♫ O~( ♫ d~) ♫ per nod ♫
Nesterade insättningar	OM SK1n	O~(1) ♫ ♫	♫ O^(1) ♫
träd	OM SK1	O~(1) ♫ ♫	♫ O~ (1) ♫ `LTree` typ) M SK1

n = totala noder , d = djupt , s = underträds storlek *Med rätt index

Beslutsdiagramm

flowchart TD
    Start([Start]) --> Q1{Shallow tree?<br/>< 5 levels}
    Q1 -->|Yes| Q2{Need EF Core<br/>navigation properties?}
    Q2 -->|Yes| AL[Adjacency List]
    Q2 -->|No| Q3{Performance<br/>critical?}
    Q3 -->|No| AL
    Q3 -->|Yes| MP[Materialised Path]

    Q1 -->|No| Q4{Read-heavy?}
    Q4 -->|Yes| Q5{Writes rare?}
    Q5 -->|Yes| NS[Nested Sets]
    Q5 -->|No| CT[Closure Table]

    Q4 -->|No| Q6{PostgreSQL only?}
    Q6 -->|Yes| LT[ltree]
    Q6 -->|No| Q7{Need breadcrumbs?}
    Q7 -->|Yes| MP
    Q7 -->|No| CT

    style Start stroke:#10b981,stroke-width:2px
    style AL stroke:#6366f1,stroke-width:2px
    style MP stroke:#8b5cf6,stroke-width:2px
    style NS stroke:#ec4899,stroke-width:2px
    style CT stroke:#f59e0b,stroke-width:2px
    style LT stroke:#14b8a6,stroke-width:2px

Verklig-World Choice: Denna blogg

Denna blogg använder Slut Tabellen tillvägagångssätt för kommentarer

kommentarer läsers mycket oftare än skrivna
Vi behöver visa nesterade kommentarer trådar effektivt
Depth-begränsande frågor är viktiga för prestationen (visar vi upp på nivåer ♫ 5 ♫ djupt ♫
Att flytta kommentarer är ovanligt. (moderatorer behöver ibland göra detta.

Se Del 1.2: Slutbrädan för fulla implementeringsdetails.

Del 1: Übersicht (den här artikeln )
Del 1.1: Närliggande lista - Enkela moderreferenser
Del 1.2: Slutbrädan - Förberäknade relationer
Del 1.3: Materialiserad väg - Pathsträngar
Del 1.4: Nested Sets - vänsterM SK1 högra gränser
Del 1.5: ltree - PostgreSQL-nativextension
Del 2: Raw SQL and Dapper (coming soon)