Ik heb de volgende tabel:
ID en naam spreken voor zich.
ligt_in is een foreigh key naar dezelfde tabel, dus wijst naar een andere lokatie.
Soort is het soort lokatie.
Mogelijkheden zijn als volgt: provincie, gemeentelijke, plaats, wijk, buurt, straat.
Die ligt_in wordt gebruikt om aan te wijzen in welk 'grotere' gebied een bepaalde lokatie ligt. Zo ligt de "Kalverstraat" in de wijk/stadsdeel "Binnenstad", dat ligt in plaats "Amsterdam", provincie "Noord Holland". De ligt_in van Noord Holland is 0, wat betekent dat het in Nederland ligt dus niks groters dat in de tabel te vinden is. Zo worden lokaties geordend.
Die lokatie-tabel wordt gebruikt door een tabel nieuwsberichten. Elk nieuwsbericht speelt zich op bepaalde lokaties af. Dit kan provinciaal zijn, maar ook gericht op 1 bepaalde straat.
Stel nieuws heeft betrekking op de Kalverstraat. Dan is de lokatie Kalverstraat verbonden met het nieuwsbericht. Het nieuwsbericht speelt zich ook af in Noord Holland, daar ligt de Kalverstraat immers, maar dat staat niet opgeslagen in het nieuwsbericht. Evenmin dat in dit voorbeeld Amsterdam is gekoppeld aan het nieuwsbericht. Dat blijkt namelijk wel uit de ketting lokaties, redundantie. In de nieuwstabel staat alleen de kleinste relevante lokatie.
En nu het probleem: Ik wil MySQL (en PHP) laten zoeken naar nieuws-items in een bepaalde lokatie. Stel ik ga zoeken naar nieuws in Amsterdam, dan wordt een nieuwsbericht dat is gelinkt aan Kalverstraat niet direct gevonden. Het zoek-machanisme moet alle onderliggende lokaties van Amsterdam nagaan om tot een volledig resultaat te komen. Dat dient recursief te gebeuren, immers elk stadsdeel heeft weer buurten en buurten hebben straten en op al die lokaties kunnen nieuwsberichten van toepassing zijn. Dat levert een enorme berg queries op.
Natuurlijk kan ik met PHP eerst een array maken met ID's van lokaties die relevant zijn, en daarna met PHP een lijst met alle nieuwsberichten doorlopen, maar dat vreet geheugen en CPU-kracht.
Kan dit niet simpeler?
Is er niet een leuke MySQL query te bedenken waar die recursie al in zit?
Ik heb zelf het volgende bedacht:
Uit de waarde van ID moet al een hierarchische ordening blijken.
Er zijn 12 provincies, maximaal 100 gemeenten, per gemeente maximaal 100 plaatsen, maximaal 50 wijken, maximaal 50 buurten, maximaal 500 straten per buurt.
ID van een wijk is bijvoorbeeld ID[provincie]*(100+100+50+50+500) + ID[gemeente]*(100+50+50+500) + ID[plaats]*(50+50+500) + ID[wijk]*(50+500)
Door het ID van een bepaalde lokatie van de betreffende grotere lokaties af te laten hangen, krijg je bij elkaar horende lokaties bij elkaar, en hoef je alleen maar in je whereis-clause een ID-bereik aan te geven in je query.
Ik vind het echter geen schoonheidsprijs waard (bezwaar bijv.: systeem moet op de kop als er ineens gemeenten met 101 plaatsen ontstaan), dus betere oplossingen welkom.
code:
1
2
3
4
5
| CREATE TABLE lokaties ( ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, naam VARCHAR(40) NOT NULL, ligt_in INTEGER UNSIGNED NOT NULL, soort INTEGER UNSIGNED NOT NULL, |
ID en naam spreken voor zich.
ligt_in is een foreigh key naar dezelfde tabel, dus wijst naar een andere lokatie.
Soort is het soort lokatie.
Mogelijkheden zijn als volgt: provincie, gemeentelijke, plaats, wijk, buurt, straat.
Die ligt_in wordt gebruikt om aan te wijzen in welk 'grotere' gebied een bepaalde lokatie ligt. Zo ligt de "Kalverstraat" in de wijk/stadsdeel "Binnenstad", dat ligt in plaats "Amsterdam", provincie "Noord Holland". De ligt_in van Noord Holland is 0, wat betekent dat het in Nederland ligt dus niks groters dat in de tabel te vinden is. Zo worden lokaties geordend.
Die lokatie-tabel wordt gebruikt door een tabel nieuwsberichten. Elk nieuwsbericht speelt zich op bepaalde lokaties af. Dit kan provinciaal zijn, maar ook gericht op 1 bepaalde straat.
Stel nieuws heeft betrekking op de Kalverstraat. Dan is de lokatie Kalverstraat verbonden met het nieuwsbericht. Het nieuwsbericht speelt zich ook af in Noord Holland, daar ligt de Kalverstraat immers, maar dat staat niet opgeslagen in het nieuwsbericht. Evenmin dat in dit voorbeeld Amsterdam is gekoppeld aan het nieuwsbericht. Dat blijkt namelijk wel uit de ketting lokaties, redundantie. In de nieuwstabel staat alleen de kleinste relevante lokatie.
En nu het probleem: Ik wil MySQL (en PHP) laten zoeken naar nieuws-items in een bepaalde lokatie. Stel ik ga zoeken naar nieuws in Amsterdam, dan wordt een nieuwsbericht dat is gelinkt aan Kalverstraat niet direct gevonden. Het zoek-machanisme moet alle onderliggende lokaties van Amsterdam nagaan om tot een volledig resultaat te komen. Dat dient recursief te gebeuren, immers elk stadsdeel heeft weer buurten en buurten hebben straten en op al die lokaties kunnen nieuwsberichten van toepassing zijn. Dat levert een enorme berg queries op.
Natuurlijk kan ik met PHP eerst een array maken met ID's van lokaties die relevant zijn, en daarna met PHP een lijst met alle nieuwsberichten doorlopen, maar dat vreet geheugen en CPU-kracht.
Kan dit niet simpeler?
Is er niet een leuke MySQL query te bedenken waar die recursie al in zit?
Ik heb zelf het volgende bedacht:
Uit de waarde van ID moet al een hierarchische ordening blijken.
Er zijn 12 provincies, maximaal 100 gemeenten, per gemeente maximaal 100 plaatsen, maximaal 50 wijken, maximaal 50 buurten, maximaal 500 straten per buurt.
ID van een wijk is bijvoorbeeld ID[provincie]*(100+100+50+50+500) + ID[gemeente]*(100+50+50+500) + ID[plaats]*(50+50+500) + ID[wijk]*(50+500)
Door het ID van een bepaalde lokatie van de betreffende grotere lokaties af te laten hangen, krijg je bij elkaar horende lokaties bij elkaar, en hoef je alleen maar in je whereis-clause een ID-bereik aan te geven in je query.
Ik vind het echter geen schoonheidsprijs waard (bezwaar bijv.: systeem moet op de kop als er ineens gemeenten met 101 plaatsen ontstaan), dus betere oplossingen welkom.
[ Voor 10% gewijzigd door SvMp op 29-11-2004 01:26 ]
/u/24411/img.png?f=community)
/u/107051/jeroenmadtrix60.png?f=community)
)
/u/1830/acm.png?f=community)