5.5 Datamodellering for komplekse systemer

Når relasjonene floker seg

Nå som du kan SQL, NoSQL og databasedesign, er det tid for virkelighetens floker: komplekse systemer med mange entiteter og relasjoner. La oss starte med den mest utfordrende relasjonstypen, mange-til-mange (M:N). Den oppstår når én A kan knyttes til mange B, og én B kan knyttes til mange A. Studenter og kurs er det klassiske eksempelet: én student tar flere kurs, og ett kurs har flere studenter. Det samme gjelder forfattere og bøker, eller skuespillere og filmer.

Problemet er at en M:N-relasjon ikke kan modelleres direkte. Du kan ikke ha en kursID-kolonne i Student-tabellen – hva om studenten tar fem kurs? Løsningen er en koblingstabell (også kalt junction table eller associative entity). Den har én rad per student-kurs-par, med fremmednøkler til begge tabellene:

CREATE TABLE StudentKurs (
    studentID INTEGER,
    kursID INTEGER,
    registrert_dato DATE,
    karakter INTEGER,
    PRIMARY KEY (studentID, kursID),
    FOREIGN KEY (studentID) REFERENCES Student(studentID),
    FOREIGN KEY (kursID) REFERENCES Kurs(kursID)
);

Koblingstabellen har flere fordeler. Den kan bære ekstra informasjon om selve relasjonen, som registreringsdato og karakter. Og den lar deg enkelt finne alle kurs for en student, eller alle studenter i et kurs – ved å koble gjennom tabellen med JOIN.

Tabeller som refererer til seg selv

Noen ganger må en entitet referere til seg selv. Tenk på et organisasjonshierarki: hver ansatt har en leder, men lederen er også en ansatt. Dette løser vi med en rekursiv relasjon – en fremmednøkkel som peker tilbake til samme tabell:

CREATE TABLE Ansatt (
    ansattID INTEGER PRIMARY KEY,
    navn TEXT NOT NULL,
    lederID INTEGER,
    FOREIGN KEY (lederID) REFERENCES Ansatt(ansattID)
);

Toppsjefen har lederID = NULL fordi hun ikke har noen leder over seg. For å finne alle ansatte under en bestemt leder, søker du på lederID. For å vise hver ansatt sammen med lederens navn, gjør du en selv-JOIN – du kobler Ansatt-tabellen til seg selv med to ulike aliaser, som Ansatt og Leder.

Samme mønster dukker opp ved kommentarer med svar. En kommentar kan være et svar på en annen kommentar, så vi gir den en svar_på-kolonne som peker til kommentarID-en til foreldrekommentaren – eller NULL hvis den er på toppnivå. Men her ligger en utfordring: SQL er ikke god til å hente hele trær, altså svar på svar på svar i mange nivåer. En vanlig løsning er å hente de flate dataene fra databasen og bygge selve treet i Python eller JavaScript etterpå. Rekursive relasjoner er kraftige for å modellere hierarkier, men dype trær trekker fort grensen for hva ren SQL håndterer elegant.

Forretningsregler i databasen

En god database håndhever ikke bare struktur, men også forretningsregler – betingelser om hva som er gyldige data. Databasen kan gjøre dette selv, gjennom constraints og triggers, slik at ugyldige data aldri slipper inn, uansett hvilket program som forsøker.

Det vanligste verktøyet er CHECK-constraints, som setter krav til verdiene i en kolonne. En produkttabell kan kreve at prisen er minst null, at antallet ikke er negativt, og at rabattprosenten ligger mellom 0 og 100:

CREATE TABLE Produkt (
    produktID INTEGER PRIMARY KEY,
    pris DECIMAL(10,2) CHECK (pris >= 0),
    antall INTEGER CHECK (antall >= 0),
    rabatt_prosent INTEGER CHECK (rabatt_prosent BETWEEN 0 AND 100)
);

Forsøker noen å sette inn en negativ pris, avviser databasen det automatisk. I tillegg har vi UNIQUE-constraints, som garanterer at en verdi ikke gjentas – som at hver bruker må ha en unik e-postadresse – og NOT NULL, som krever at et felt har en verdi. For mer avanserte regler finnes triggers, som er kode databasen kjører automatisk når noe skjer, for eksempel for å oppdatere et lager når en ordre legges inn.

Poenget med å legge regler i databasen er robusthet: dataintegriteten er sikret på ett sted, uavhengig av om dataene kommer fra en webapp, et skript eller en annen tjeneste. Det er siste forsvarslinje mot ugyldige data.

Oppsummering

Komplekse systemer krever at vi mestrer virkelighetens floker. Mange-til-mange-relasjoner kan ikke modelleres direkte, men løses med en koblingstabell som har fremmednøkler til begge tabellene og kan bære ekstra informasjon om forholdet. Rekursive relasjoner lar en tabell referere til seg selv via en fremmednøkkel – som lederID i et organisasjonshierarki eller svar_på for kommentarer – selv om dype trær ofte bygges ferdig i Python.

Til slutt så vi at databasen kan håndheve forretningsregler selv, gjennom CHECK-, UNIQUE- og NOT NULL-constraints og mer avanserte triggers. Det gir robust dataintegritet uansett hvilket program som skriver. Med dette har du fullført seksjonen om databaser. I neste seksjon går vi over til moderne webutvikling.