---

Hej,

(MySQL 4.0.21, evt. via php, perl eller lignende)

Jeg har brug for en genopfriskning på en simpel problemstilling, der
omhandler at finde fællesmængden, hvor flere egenskaber i en
relationstabel skal optræde.

I øjeblikket laver jeg en relation til relationstabellen, bruger GROUP
BY på primærnøglen i hovedtabellen og derefter en HAVING på COUNT(*)
for kun at få de rækker, hvor alle egenskaber er sat (fællesmængden).
Jeg er bare i tvivl om dette er den kvikkeste måde.

Forsimplet eksempel: Jeg vil søge min filmsamling igennem efter film,
der både tilhører genren "Action", "Krimi" og "Thriller". Tabellerne
er normaliseret til en filmtabel, en genretabel og en mellemtabel til
mange-til-mange-relationer.

Filmtabel - felter: id, titel
1, Min Første Film
2, Min Anden Film
3, Min Tredje film

Genretabel - felter: id, navn
1, Action
2, Krimi
3, Komedie
4, Sci-Fi
5, Thriller
6, Western

Relationstabel - felter: filmtabel_id, genretabel_id
1, 1
1, 2
1, 3
1, 5
2, 1
2, 3
2, 4
2, 5
3, 1
3, 6

Det vil altså sige:
Film 1 har følgende genrer: Action, Krimi, Komedie, Thriller
Film 2 har følgende genrer: Action, Komedie, Sci-Fi
Film 3 har følgende genrer: Action, Western

Jeg skal så finde de film, der har alle af følgende genrer:
Action, Krimi, Thriller (genre-id 1,2,5). Det er ligegyldigt, om en
film tillige har øvrige genrer.

Jeg er nået frem til følgende query, som i og for sig virker fint (jeg
undlader for overskuelighedens skyld her at join'e videre over til
genretabellen)

SELECT Filmtabel.titel, COUNT(*) AS antal
FROM Filmtabel, Relationstabel
WHERE Filmtabel.id = Relationstabel.filmtabel_id
AND Relationstabel.genretabel_id IN (1,2,5)
GROUP BY Filmtabel.id
HAVING antal = 3

Mine variable i søgningen er altså hvilke og hvor mange genrer, jeg
søger. HAVING er sat til antallet af genrer, der skal matches (tre i
dette tilfælde). Hvis det ikke behøvede at være en Krimi, så skulle
jeg kun finde genretabel_id IN (1,5) og HAVING antal = 2. Jeg kan på
den måde også let finde film, der blot har (mindst) to ud af tre
genrer sat.

Jeg er fint tilfreds med normaliseringen, men jeg føler, jeg har
overset noget helt basalt i denne problemstilling, der gør at jeg let
kan viderebygge problemstillingen. Fx hvis en film skal tilhøre mindst
to bestemte genrer, men ikke må tilhøre en tredje genre (og de
resterende genrer er ligegyldige).

Er jeg på rette spor, eller burde jeg gå i seng, sove på det og så
læse op på teorien?

--
- Peter Brodersen

Ugens sprogtip: professionel (og ikke proffessionel)

---

On Fri, 01 Oct 2004 05:27:17 +0200, Peter Brodersen wrote:

> Er jeg på rette spor

Jeg kan ikke se, at du overser noget "helt basalt".

Jeg ville nok undlade at tildele surrogatnøgler til genre-tabellen: Det
er vanskeligt at forestille sig en situation, hvor man ønsker at have
flere genrer med samme navn.

Og til queries, hvor du vil have film, der hører til genre X og Y, men
ikke Z, vil det nok give bedre svartider, hvis du benytter ALL og NOT
EXISTS subqueries i stedet for at optælle med COUNT+GROUP BY.

I øvrigt kan følgende artikel måske give lidt inspiration:
http://www.dbazine.com/celko1.html

--
Greetings from Troels Arvin, Copenhagen, Denmark

---

On Fri, 01 Oct 2004 09:27:31 +0200, Troels Arvin <troels@arvin.dk>
wrote:

>Jeg ville nok undlade at tildele surrogatnøgler til genre-tabellen: Det
>er vanskeligt at forestille sig en situation, hvor man ønsker at have
>flere genrer med samme navn.

Det er allermest af gammel vane.

Mine umiddelbare tanker plejer at være er, at en tabel, der kun
indeholder integers (surrogatnøglerne), har et fixed row format, og er
hurtigere at slå op i. Man kan selvfølgelig have en ENUM for
genrelisten, men her vil jeg så hellere foretage en INSERT eller en
UPDATE på en række i en tabel, end at ALTER'e en tabel for at ændre
ENUM-feltet til at rette et felt eller rumme endnu en mulighed.

Når det så er sagt, så er der dog ingen tvivl om at man skal op på en
væsentlig mængde belastning og datamængder, før det bliver relevant -
samt selvfølgelig folks foretrunke metoder om at bruge surrogatnøgler
eller ej.

>Og til queries, hvor du vil have film, der hører til genre X og Y, men
>ikke Z, vil det nok give bedre svartider, hvis du benytter ALL og NOT
>EXISTS subqueries i stedet for at optælle med COUNT+GROUP BY.

Det giver også god mening, ja. Først var jeg bekymret over at skulle
have en lang række subqueries (hvis der var mange kategorier og mange
undtagelser), men det kræver jo næppe mere end to styk: Én for
positiv-listen, og én for negativ-listen.

>I øvrigt kan følgende artikel måske give lidt inspiration:
>http://www.dbazine.com/celko1.html

Jeg takker!

God artikel, i øvrigt. Blandt andet forskellen på "Alle kan flyve alle
flyene i hangaren, for der er jo ikke nogen" og "Ingen kan flyve alle
flyene i hangerene, for der er jo ikke nogen" er sjov (og relevant).

--
- Peter Brodersen

Ugens sprogtip: så længe (og ikke sålænge)