[MySQL] Meest efficiënte veel-op-veel relatie?

[r0bert]

Ik probeer een basissysteem van de grond te krijgen dat generiek om moet gaan met relaties in databases. Bij 1-op-1 of 1-op-veel is dat geen probleem, maar bij veel-op-veel kom ik toch wat in de problemen. D.w.z. het lukt wel, maar naar mijn idee niet efficient genoeg (min. 3 aparte queries).

Vb:

code:

BAND
+----+
| oid|
+----+
| bid|
+----+
oid  -> objectid
bid  -> bandid

BANDGENRE
+----+
| rid|
+----+
| oid|
+----+
|grid|
+----+
rid  -> relatieid
oid  -> objectid
grid -> genregroepid

GENRE
+----+
| oid|
+----+
| gid|
+----+
|grid|
+----+
oid  -> objectid
gid  -> genreid
grid -> genregroepid

Die middelste tabel gaat het volgens mij vooral om. Dit zou eigenlijk een generieke relatietabel die voor iedere relatie gebruikt kan worden (voor iedere tabel).

Met content erin wordt het zoiets:

code:

BAND
+----+----+
| oid| bid|
+----+----+
| 51 | 1  |
+----+----+
| 52 | 2  |
+----+----+
| 53 | 3  |
+----+----+

BANDGENRE / RELATION
+----+----+----+
| rid| oid|grid|
+----+----+----+
| 1  | 51 | 2  |
+----+----+----+
| 2  | 51 | 3  |
+----+----+----+
| 2  | 52 | 2  |
+----+----+----+
| 2  | 53 | 1  |
+----+----+----+
| 3  | 53 | 2  |
+----+----+----+
| 2  | 53 | 3  |
+----+----+----+

GENRE
+----+----+----+
| oid| gid|grid|
+----+----+----+
| 54 | 1  | 1  |
+----+----+----+
| 55 | 2  | 2  |
+----+----+----+
| 56 | 3  | 1  |
+----+----+----+
| 57 | 4  | 1  |
+----+----+----+
| 58 | 5  | 3  |
+----+----+----+

Om nu vanuit BAND alle bijbehorende GENREs te pakken aan de hand van GRID, wordt een erg lelijke query. Dit zou met een gestapelde JOIN kunnen, maar volgens mij is daar een hele grote optimalisatie mogelijk, maar na een paar uur prutsen en tekenen heb ik het nog niet gevonden. Hulp is zeer welkom. Des te meer reden waarom een JOIN op den duur niet lukt, is het volgende (probleem met kolomnamen/keynamen):

Daarbij wil ik de middelste tabel (RELATIE) graag generiek maken, zodat niet alleen de koppeling van BAND en GENRE op GRID erin komt, maar ook bijv AUTOHANDELAAR naar AUTO op MERK.

Dat zou dan zoiets worden?

code:

+------+
|  rid |
+------+
| srcid|
+------+
| refid|
+------+

Maar dan mis ik weer over welke kolommen/keys het gaat e.d.

Ik heb het idee dat ik ergens een grove denkfout maak. If so, wijs me er s.v.p. op want volgens mij werk ik mezelf steeds verder in de hoek.

[ Voor 3% gewijzigd door r0bert op 26-03-2008 10:56 ]

[SchizoDuckie]

Oke alsjeblieft maak *NIET* die fout die je nu probeert te maken.

Maak alsjeblieft een *APARTE* koppeltabel aan voor elke (veel:veel) relatie anders gaat nooit iemand ooit meer uit kunnen vinden hoe je datamodel in elkaar steekt, en ga je zelf ook verschrikkelijk in de problemen komen.

Dus, als je banden aan autos wil knopen veel op veel heb je 3 tabellen:

Banden
Autos
Banden_Autos

Banden ook aan types? geen probleem

Banden
Autos
Banden_Autos
Types
Banden_Types

Zo zie ik gelijk al als ik naar je data model kijk dat er een veel:veel relatie tussen ligt met voor elke relatie een designated tabel. dit is *DE WAY TO GO* doe het alsjeblieft niet 'slimmer' zoals je nu bezig bent want je kan dan nooit meer normaal een query maken als je data model zich uitbreidt, en je ad-hoc bijv. wil tellen hoeveel verschillende type banden voor een bepaalde auto je nu hebt.

Dan moet je je relatie tabel gaan joinen? ( ) om een relatie uit te lezen van een bepaald type, en als je per ongeluk de verkeerde records weggooit ben je zwaar de sjaak. (en voor je het weet sta je op the daily wtf

[ Voor 63% gewijzigd door SchizoDuckie op 26-03-2008 11:04 ]

[r0bert]

Moet ik dat zien als een extra uitdaging?

edit:

Dan moet je je relatie tabel gaan joinen?

Moet hierboven toch ook?

[ Voor 60% gewijzigd door r0bert op 26-03-2008 11:10 ]

[SchizoDuckie]

r0bert schreef op woensdag 26 maart 2008 @ 10:58:
Moet ik dat zien als een extra uitdaging?

edit:

[...]

Moet hierboven toch ook?

Ja, maar zoals het in jouw voorbeeld staat moet je eerst je relatie tabel gaan joinen om te bepalen *wat* voor relatie het is, en daarna pas de echte relatie uitpluizen? Da's een No-No. Er horen op dat moment gigantische alarmbellen in jouw hoofd af te gaan die je zeggen dat dit *fout* is.

[Niemand_Anders]

Wat verwacht je nu, dat je queries krijgt of zo? Welke queries heb je nu (welke dus NIET efficiënt zijn)?

In elk geval kan ik je nu al vertellen dat eht gebruik van 1 koppeltabel voor alle koppelingen tussen tal van tabellen niet efficient is. Je query is het snelst als de tabellen zo min mogelijk records bevatten (zelfs met indexen). Omdat ID 4 zowel van tabel a als tabel b kan zijn, kun je dus ook unique indexen gebruiken. Iets wat wel mogelijk is als je een dedigated koppeltabel gebruikt. Immers de combinatie band <--> genre is namelijk uniek.

Probeer daarnaast zoveel mogelijk parameters uit je where clause naar de join clauses te verhuizen zodat een filtering plaats vind VOOR de join en het aantal records dus al beperkt is. Een where wordt NA de join uitgevoerd. Bij je super generieke koppel tabel betekend dit dat de database alle records uit de koppeltabel probeert te joinen en pas daarna gaat filteren. Hoewel dit basis kennis behoort te zijn, verbaasd het mij hoeveel join queries ik zie met daarachter nog een where..

[r0bert]

@SchizoDuckie
Dat is waar. Dat uitpluizen wat voor relatie het is, doet het sowieso de das om. En die alarmbellen gaan al Daarom kom ik ook naar GoT Is het niet mogelijk om VANUIT de relatietabel te beginnen?

@Niemand_Anders
Queries uitzoeken lijkt me niet handig als het datamodel nog niet eens op orde is. In mijn startpost staat beschreven hoe het momenteel in elkaar zit, zoals genoemd een 3x gestapelde JOIN.

[ Voor 15% gewijzigd door r0bert op 26-03-2008 11:31 ]

[r0bert]

SchizoDuckie schreef op woensdag 26 maart 2008 @ 11:13:
[...]


Ja, maar zoals het in jouw voorbeeld staat moet je eerst je relatie tabel gaan joinen om te bepalen *wat* voor relatie het is, en daarna pas de echte relatie uitpluizen? Da's een No-No. Er horen op dat moment gigantische alarmbellen in jouw hoofd af te gaan die je zeggen dat dit *fout* is.

Hoe zou het bovenstaande voorbeeld eruit zien dan als er niet wordt gejoint op unieke ID's, maar bijv. als in mijn voorbeeld GENREGROEPID? Er klopt volgens mij ergens iets niet