[SQL] aantal relaties bijhouden, transacties nodig?

[Genoil]

Ik ben bezig met het (her-)schrijven van wat ik mijn 'hypertree' noem. Kort gezegd is het een soort boomstructuur waarbij elke 'node' niet alleen meedere 'childnodes' kan hebben, maar ook meerdere 'parentnodes'. Bijbehorende (SQL-) databasestructuur bevat aldus een node tabel (id, name) en een relation tabel (id, parent_id, child_id).

Nu vraag ik mij af of het de moeite waard is om in de node tabel eveneens de volgende -ik neem aan voor zichzelf sprekende- velden op moet nemen: parent_count, child_count, of dat ik het aantal relaties van een node beter gewoon op kan zoeken in de relation tabel wanneer het nodig is.

Het grootste voordeel wat ik eruit haal, is dat het me behoorlijk wat SELECTS kan schelen wanneer ik de boom uitlees (hoef niet te kijken of een node children/nog meer parents heeft), of wanneer ik bijvoorbeeld nodes delete, ik meteen weet of de node toevallig ook nog onder een andere parent hangt (zodat ik kan bepalen of ik ook echt "DELETE FROM node ..." moet doen of slechts een relation moet DELETEen).

Het nadeel is dat het een "bitch" is om die counts netjes bij te houden: Bij elke "reference-", "move-", "delete-" node(s) bewerking aan de boom zal ik 1 of 2 UPDATEs moeten doen aan de node tabel*, terwijl ik anders slechts 1 keer hoef te INSERTen, UPDATEn of DELETEn in de relation tabel.

Ik ben van start gegaan met de extra velden voor de referentie-counts. Momenteel ben ik een behoorlijk eind gevorderd met het coden van de functies voor het "referencen", "moven" en "deleten/dereferencen" van nodes en selecties van nodes. Het uitlezen werkt al lang goed, maar is nog niet geoptimailseerd m.b.t het beperken van SELECTs.

Mijn vragen hieromtrent:
1. Hoe werken jullie in soortgelijke situaties? Of wat lijkt je het beste?
2. Kan ik mijn SELECTs uit de relation tabel evt. sneller maken door bv. "LIMIT node.child_count" te doen? (Dit als eventuele toevoeging op de echte winst die ik haal bij het helemaal niet zoeken naar parents/children wanneer de refcounts ervan op 0 staan)
3. Ben ik eigenlijk verplicht om gebruik te gaan maken van transaction-enabled databasetables, om te voorkomen dat er incongruentie onstaat tussen het daadwerkelijke aantal relaties en de bijgehouden aantallen, wanneer er bijvoorbeeld halverwege een bewerkingsactie iets misgaat of er een andere user tussendoor gaat rommelen?**

* voor het UPDATEn van de ref-counts gebruik ik relatieve waarden ("UPDATE node SET child_count = child_count + $x ...", waarbij ik $x gelijk is aan het aantal geselecteerde nodes, die ik via een sessie-variabele verkrijg, m.a.w. geen SELECT voor nodig.

** voor zover ik m'n eigen code en database begrijp, kan er in principe weinig misgaan. stel: 2 users besluiten tegelijkertijd een node naar elk een andere parent te moven. Bij zo'n actie hoef ik alleen een relation.parent_id te veranderen, de parent_count verandert in dat geval niet van de te verplaatsen node, alleen de child_counts van de betrokken parent_nodes. Eén zo'n actie verloopt als volgt:

code:

($x $y en $z zijn user-input via sessievars)
UPDATE relation SET parent_id = $z WHERE parent_id = $x AND child_id = $y;  
UPDATE node SET child_count-- WHERE id = $x;  van de oude parent
UPDATE node SET child_count++ WHERE id = $z; van de nieuwe parent.

ik weet niet eens of het mogelijk is, maar ik ga even van het feit uit dat deze opeenvolging van queries wanneer twee users ze tegelijk uitvoeren zodanig is, dat de 2 eersten uit de serie van 3 exact na elkaar gescheduled worden. Die van de 2e user geeft aldus 0 affected rows terug. Waarop ik dus weet dat er iemand anders ook aan het 'moven' is. Dus maak ik gewoon een extra relatie aan ipv die laatste 2 updates:

code:

SELECT FROM relation WHERE parent_id = $z AND child_id =$y;
if (0 rows terug)
{
    INSERT INTO relation (parent_id, child_id) VALUES ($z, $y);
    UPDATE node SET parent_count++ WHERE id = $y;
    UPDATE node SET child_count++ WHERE id = $z;
}

die SELECT zit er omheen om te checken of beide users niet toevallig dezelfde nieuwe parent node hebben gekozen, indat geval blazen we actie van user 2 gewoon helemaal af en heft ie hetzelfde resultaat.

Dit lijkt me dus allemaal goed te gaan, maar het begint me allemaal een beetje te duizelen als ik weet dat users ook meerdere nodes (op 1 niveau) kunnen selecteren en daarmee kunnen gaan moven, referencen, deleten, etcetera. Lastiger wordt het bijvoorbeeld wanneer 2 users tegelijkertijd besluiten dezelfde node, die bij de ene user-view onder een andere parent hangt dan bij de andere user-view, te "unlinken", elk in de veronderstelling dat de node niet verloren gaat omdat ie toch nog wel onder de andere hangt. Dan kan ik prutsen wat ik wil, maar de node gaat hoe dan ook verloren!

Of moet ik het gewoon niet toestaan dat 2 users op hetzelfde niveau bepaalde acties uitvoeren (geselecteerde nodes in een temp table bijhouden?)

gekkenhuis in mijn hoofd, geeft niet als dit als complete nonsense overkomt, ik heb zelf al een beetje meer inzicht in de problematiek door het op te hebben geschreven...

[edit]
goh wat kan een stukje fietsen goed helpen...weg met die counts! het is al pittig genoeg zonder die krengen...al die UPDATEs wegen bij nader inzien niet echt op tegen die eventuele lege SELECTies die ik af en toe moet doen. of zijn er toch mensen die dit wel met succes toepassen?

[ Voor 11% gewijzigd door Genoil op 11-02-2003 03:52 ]

[ACM]

Als er maar een paar referenties zijn, dan lijkt me toch dat dat sowieso niet al te traag is, vanwege de kleine hoeveelheid nodes dat geteld moet worden.
Mits je zo verstandig was indices aan te leggen natuurlijk

Ik zou zeker, indien niet absoluut noodzakelijk, hier niet te veel van dat soort countertjes in verwerken. Want je wil vast ook soms dan alle nodes tot een diepte X eronder/boven en dan kan je ze toch al niet meer gebruiken.

[Genoil]

ja relation.parent_id en relation.child_id zijn geindexeerd. ik ga die counter-meuk er dadelijk uitslopen . thx voor de bevestiging

[Night-Reveller]

Ad 1. Je zegt het zelf al; weg met die counts, dat is nl. afleidbare informatie. Dat mag je mooit opslaan.
Ad 2. Ik zou performance achteraf pas bekijken. Zoals ACM al zegt, het zal niet zomaar traag zijn, daar moet je echt wat voor doen. Hou je eerst maar bij de basics; je datamodel.
Ad 3. Het is niet verplicht, maar wel handig. Zodoende krijg je nooit gezeik met phantom-reads etc.

Met welke dbms werk je eigenlijk? MSSQL? Ik hoop voor je dat je Foreign Keys kan gebruiken (iig. MSSQL wel). Dan kan je b.v. de db laten afdwingen dat nodes altijd een parent moeten houden, of b.v. dat als een node verwijderd wordt dat al zijn childs ook verwijderd moeten worden, etc.

[Genoil]

Night-Reveller schreef op 11 February 2003 @ 11:40:
..

Met welke dbms werk je eigenlijk? MSSQL? Ik hoop voor je dat je Foreign Keys kan gebruiken (iig. MSSQL wel). Dan kan je b.v. de db laten afdwingen dat nodes altijd een parent moeten houden, of b.v. dat als een node verwijderd wordt dat al zijn childs ook verwijderd moeten worden, etc.

MySQL . Kan ik dan wat mits ik InnoDB heb? Daar heeft iedereen het altijd over , toch? De problemen die je beschrijft hebben me in de eerste versie van het systeem al de nodige hoofdbrekens gekost... Ben al bezig het er bij de sysop van de eerste grote afnemer van het product in te masseren dat ie in het vervolg InnoDb moet meecompilen op z'n MySQL installatie

Ik moest maar eens een boek gaan lezen over RDBMSen...met die zelfverzonnen logica kom ik volgens mij niet echt ver

[ Voor 9% gewijzigd door Genoil op 11-02-2003 12:05 ]