5.5 Datamodellering for komplekse systemer

Datamodellering for komplekse systemer

Nå som du kan SQL, NoSQL og databasedesign, er det på tide å tackle virkelige utfordringer: komplekse systemer med mange entiteter, relasjoner og forretningsregler.

I dette kapittelet lærer du:
- Designe mange-til-mange-relasjoner med koblingstabeller
- Håndtere komplekse forretningsregler i databasen
- Modellere hierarkier og rekursive relasjoner
- Beste praksis for databasedesign i store prosjekter

Mange-til-mange-relasjoner

En mange-til-mange (M:N) relasjon eksisterer når:
- Én A kan være relatert til mange B
- Én B kan være relatert til mange A

Eksempler:
- Studenter ↔ Kurs (én student tar flere kurs, ett kurs har flere studenter)
- Forfattere ↔ Bøker (én forfatter skriver flere bøker, én bok kan ha flere forfattere)
- Skuespillere ↔ Filmer

Problem: Kan ikke modelleres direkte

Dette fungerer IKKE:

-- FEIL! En student kan ikke ha flere kurs i én kolonne
CREATE TABLE Student (
    studentID INTEGER PRIMARY KEY,
    navn TEXT,
    kursID INTEGER  -- Hva hvis studenten tar 5 kurs?
);

Løsning: Koblinger (junction table / associative entity)

-- Studenter
CREATE TABLE Student (
    studentID INTEGER PRIMARY KEY,
    navn TEXT NOT NULL,
    epost TEXT UNIQUE
);

-- Kurs
CREATE TABLE Kurs (
    kursID INTEGER PRIMARY KEY,
    kursnavn TEXT NOT NULL,
    studiepoeng INTEGER
);

-- Koblinger (én rad per student-kurs-par)
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)
);

Fordeler:
- Kan legge til ekstra info (registrert_dato, karakter)
- Kan enkelt finne alle kurs for en student
- Kan enkelt finne alle studenter i et kurs

Eksempel: Spørringer med koblingstabeller

Gitt tabellene Student, Kurs og StudentKurs:

Finn alle kurs for student med ID 1:

SELECT Kurs.kursnavn, StudentKurs.karakter
FROM StudentKurs
INNER JOIN Kurs ON StudentKurs.kursID = Kurs.kursID
WHERE StudentKurs.studentID = 1;

Finn alle studenter i kurset "IT2":

SELECT Student.navn, StudentKurs.registrert_dato
FROM StudentKurs
INNER JOIN Student ON StudentKurs.studentID = Student.studentID
INNER JOIN Kurs ON StudentKurs.kursID = Kurs.kursID
WHERE Kurs.kursnavn = 'IT2';

Finn studenter som IKKE tar noen kurs:

SELECT Student.navn
FROM Student
LEFT JOIN StudentKurs ON Student.studentID = StudentKurs.studentID
WHERE StudentKurs.kursID IS NULL;

Finn antall studenter per kurs:

SELECT Kurs.kursnavn, COUNT(StudentKurs.studentID) AS antall_studenter
FROM Kurs
LEFT JOIN StudentKurs ON Kurs.kursID = StudentKurs.kursID
GROUP BY Kurs.kursID, Kurs.kursnavn
ORDER BY antall_studenter DESC;

Hierarkier og rekursive relasjoner

Noen ganger må en entitet referere til seg selv.

Eksempel 1: Organisasjonshierarki

CREATE TABLE Ansatt (
    ansattID INTEGER PRIMARY KEY,
    navn TEXT NOT NULL,
    stillingstittel TEXT,
    lederID INTEGER,  -- Refererer til en annen ansatt
    FOREIGN KEY (lederID) REFERENCES Ansatt(ansattID)
);

-- Testdata
INSERT INTO Ansatt VALUES (1, 'CEO', 'Daglig leder', NULL);
INSERT INTO Ansatt VALUES (2, 'CTO', 'Teknologisjef', 1);
INSERT INTO Ansatt VALUES (3, 'Utvikler', 'Senior utvikler', 2);
INSERT INTO Ansatt VALUES (4, 'Utvikler', 'Junior utvikler', 2);

Finn alle ansatte under en leder:

SELECT Medarbeider.navn, Medarbeider.stillingstittel
FROM Ansatt AS Medarbeider
WHERE Medarbeider.lederID = 2;

Finn ansatt med deres leders navn:

SELECT
    Ansatt.navn AS ansatt,
    Leder.navn AS leder
FROM Ansatt
LEFT JOIN Ansatt AS Leder ON Ansatt.lederID = Leder.ansattID;

Eksempel 2: Kommentarer med svar

CREATE TABLE Kommentar (
    kommentarID INTEGER PRIMARY KEY,
    brukerID INTEGER,
    innhold TEXT,
    svar_på INTEGER,  -- NULL hvis toppnivå, ellers ID til parent-kommentar
    dato TIMESTAMP,
    FOREIGN KEY (svar_på) REFERENCES Kommentar(kommentarID)
);

Utfordring: SQL er ikke bra på å hente hele trær (alle svar til svar til svar...). Løsning: Hent i Python/JavaScript og bygg tre der.

Forretningsregler i databasen

Databasen kan håndheve regler via constraints og triggers.

CHECK constraints

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

UNIQUE constraints

CREATE TABLE Bruker (
    brukerID INTEGER PRIMARY KEY,
    brukernavn TEXT UNIQUE NOT NULL,  -- Må være unik
    epost TEXT UNIQUE NOT NULL,
    UNIQUE (fornavn, etternavn, fødselsdato)  -- Kombinasjon må være unik
);

DEFAULT-verdier

CREATE TABLE Ordre (
    ordreID INTEGER PRIMARY KEY,
    kundeID INTEGER,
    ordre_dato TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status TEXT DEFAULT 'ny',
    totalpris DECIMAL(10,2) DEFAULT 0.00
);

Triggers (avansert)

Triggers er kode som kjøres automatisk ved INSERT/UPDATE/DELETE.

-- Automatisk oppdater produktlager ved salg
CREATE TRIGGER oppdater_lager
AFTER INSERT ON OrdreLinjer
FOR EACH ROW
BEGIN
    UPDATE Produkt
    SET antall = antall - NEW.antall
    WHERE produktID = NEW.produktID;
END;

Advarsel: Triggers kan gjøre databasen vanskelig å debugge. Bruk med forsiktighet!

Beste praksis for databasedesign

1. Start med ER-diagram

Før du skriver én linje SQL:
- Identifiser entiteter
- Tegn relasjoner
- Normaliser til 3NF
- Diskuter med teamet

2. Navngivningskonvensjoner

Tabeller:
- Entall eller flertall? Velg én standard (jeg anbefaler entall)
- PascalCase eller snakecase? (Anbefaler snakecase for SQL)

Kolonner:
- Bruk beskrivende navn: registrert_dato > reg_dat
- Primærnøkkel: {tabellnavn}ID (f.eks. brukerID)
- Fremmednøkler: samme navn som i referert tabell

Eksempel:

-- Godt
CREATE TABLE ordre (
    ordre_id INTEGER PRIMARY KEY,
    kunde_id INTEGER,
    ordre_dato DATE,
    FOREIGN KEY (kunde_id) REFERENCES kunde(kunde_id)
);

-- Dårlig
CREATE TABLE Orders (
    ID INTEGER PRIMARY KEY,
    CustID INTEGER,
    dt DATE
);

3. Indekser strategisk

-- Primærnøkler får automatisk indeks
-- Legg til indeks på fremmednøkler
CREATE INDEX idx_ordre_kunde ON ordre(kunde_id);

-- Indeks på kolonner brukt i WHERE/JOIN
CREATE INDEX idx_ordre_dato ON ordre(ordre_dato);

-- Sammensatte indekser for vanlige spørringer
CREATE INDEX idx_ordre_kunde_dato ON ordre(kunde_id, ordre_dato);

4. Dokumentasjon

-- Bruk kommentarer
CREATE TABLE kunde (
    kunde_id INTEGER PRIMARY KEY,
    -- Fullt navn (ikke splitt i fornavn/etternavn enda)
    navn TEXT NOT NULL,
    -- ISO 3166-1 alpha-2 landskode
    land TEXT DEFAULT 'NO'
);

5. Migrasjonsstrategi

Aldri endre databaseskjema direkte i produksjon!

Bruk migrasjoner:

-- migrations/001_initial_schema.sql
CREATE TABLE bruker (...);

-- migrations/002_add_email_verification.sql
ALTER TABLE bruker ADD COLUMN epost_verifisert BOOLEAN DEFAULT FALSE;

-- migrations/003_add_user_preferences.sql
CREATE TABLE bruker_preferanser (...);

6. Sikkerhet

- Bruk parameteriserte spørringer (aldri string concatenation)
- Minste privilegium: Applikasjonen trenger ikke DROP TABLE
- Krypter sensitive data (passord, personnummer)
- Logg tilgang til sensitiv data

Assosiativ entitet: Kobling som også har egne attributter (f.eks. StudentKurs har karakter).

Rekursiv relasjon: En tabell som refererer til seg selv (f.eks. ansatt → leder).

Hierarki: Tre-struktur lagret i database (organisasjon, kategorier, kommentarer).

Constraint: Regel som databasen håndhever (CHECK, UNIQUE, NOT NULL, FOREIGN KEY).

Trigger: Kode som kjøres automatisk ved databasehendelser.

Migrering: Versionert endring av databaseskjema.

Dataintegritet: Sikre at data er konsistent og korrekt gjennom constraints og relasjoner.

Oppsummering

I dette kapittelet har du lært:

- Mange-til-mange: løses med koblingstabell.
- Rekursive relasjoner: hierarkier i samme tabell.
- Forretningsregler: CHECK, UNIQUE og DEFAULT.
- Triggers: handlinger som utløses automatisk.
- Beste praksis: ER-diagram, navngivning og dokumentasjon.

Noekkelbegreper