Lookup-table med tre kolumner?

Jag har precis börjat lära mig MySQL och snickrar på min första databas, som ska innehålla information om fotografier. Två grundläggande tabeller jag har är då bilder och personer. Mellan de två finns en naturlig många-till-många-relation eftersom flera personer kan vara med på samma bild och samma person kan finnas med på flera bilder. Så långt är allt klart för mig.

I databasen ska också fotografer vara identifierbara och eftersom det händer att fotografer även kan vara med på bild tycks det mig naturligt att lägga även fotografer i persontabellen. Därmed uppstår behovet att precisera relationer. Vad är smartast och naturligast för er med lite mer erfarenhet? Att ha en lookup-table (svenska?) med kolumnerna bildid, personid och relation? Att ha två olika persontabeller? Att lägga in booelska relationsfält i persontabellen (fotograf: true/false; i_bild: true/false)? Något annat?

Schemat du beskrivit i ditt första stycke låter både enkelt och helt rätt.

Självklart skall även fotografer ligga i persontabellen (den enda persontabellen) - de är ju personer allihop. Däremot känns det onödigt att blanda in kopplingstabellen mellan bilder och personer för att peka ut fotografen, för - kan en bild ha fler eller färre än en fotograf, den har väl i princip alltid ganska exakt en fotograf?

Håll det lite simpelt; Jag tycker fotografen (person-id) helt enkelt borde vara ett fält i bild-tabellen. Det är ju i två olika syften du lagrar personid; i det ena fallet vilka som är med i bilden (behov av din kopplingstabell), och det andra i ett helt annat syfte - vem som tagit den (metadata för bilden, tillsammans med ev. övrig metadata såsom beskrivningstext, filstorlek, uppladdningsdatum etc...)

Det låter vettigt. Tack, jag kör på det.

Men sedan kommer nästa fråga hur jag smartast håller isär fotograf och övriga personer vid behandlingen av söksvaret.

Anta att jag vill söka fram fotoort och fotograf (för- och efternamn)

samt namnet på dem som är med på bilden


Hur slå ihop dessa till en query?

Det går att göra i en query, men det absolut enklaste är väl att sätta UNION mellan frågorna, se bara till att båda frågorna returnerar samma antal kolumner av samma typ och i samma ordning.

Vill du i return-settet skilja på fotografen och motiven kan du generera en egen kolumn i båda frågorna, typ 0 AS IsPhotographer respektive 1 AS IsPhotographer.

EDIT: Eller jag kanske läste frågan lite slarvigt och missförstod. Är detta för utläsningen av fotograf och motiv av ett enskilt foto, eller till en lista med alla foton?

Ett annat alternativ är ju att joina in person-tabellen två gånger i olika syften, ena via JOIN person ON bild.fotografid = person.id, andra via JOIN pers_bild ON ... JOIN person ON pers_bild.personid = person.id.

Se till att definiera tabellaliases bara, annars blir det omöjligt att referera "ena eller andra" person-tabellen i resultatet. Dina queries är förvirrade nog redan nu så du borde nog använda aliases ändå

Exempel på tabellaliases:
...
FROM bilder b
INNER JOIN personer p ON b.fotografid = p.id
WHERE b.id = ..



Men i det här fallet är det väl kanske så att du vill returnera en bild (med en fotograf) föreställandes tio personer. Skall du då köra en query för att returnera tio rader, där bildinfo och fotografinfo är samma på de tio raderna? Håll isär det i separata queries, en för att hämta bildinfo (en rad), och en för att hämta info om personer i bilden (noll eller flera rader).

Tack till båda.


Jag var nog lite otydlig. Jag hade tänkt mig både och, fast i listan över foton räcker nog fotograf. Väljer man en enskild bild ska man få mer info om den, bl.a. vilka personer som är med. Men det kanske är bäst att hålla isär de två frågorna som gadzoox föreslog.


Det verkar vara ett bra tillskott till arsenalen, oavsett hur jag löser just detta. Alias har ännu inte introducerats i den bok jag utgår från, men de kommer om ett tag ser jag.

Och jag lär nog återkomma med fler frågor innan grunderna sitter där de ska ...

Ja, nu blev det dags för nästa funderingar.

Främmande nycklar och referensintegritet låter som en bra idé, så därför har jag valt InnoDB som standardmotor. Men jag vill också ha fulltextindexerade bildtexter, vilket inte stöds av InnoDB, så den bästa lösningen jag kom på var att göra en särskilt MyISAM-tabell för bildtexterna (trots att de rimligen är unika och därför skulle passa bra i bildtabellen) och bara lägga in ett bildtextid i bildtabellen. Finns det någon annan smartare lösning (förutom att använda Sphinx eller liknande)?

Sedan inbillar jag mig också att det kan vara en god idé att lägga geografisk information om bilderna i en särskild tabell, på samma sätt med fotograferna. Om t.ex. massor av bilder är tagna i Uppsala är det väl bättre att ha en tabell för kommuner och bara ha ett kommunid i bildtabellen i stället för att skriva in Uppsala där massor av gånger?

Som alltid tacksam för tankar och insikter.

Om alla kommunid X = Uppsala och alla Uppsala = kommunid X har du ett 1:1-förhållande mellan tabellerna, och har således denormaliserat databasen (i onödan?).

Ja, det är ju det jag undrar. Men det är väl en sådan sak som kommer med erfarenheten och kanske inte gör vare sig till eller från annat än estetiskt.