5.5 Datamodellering for komplekse systemer

Datamodellering for komplekse system

No som du kan SQL, NoSQL og databasedesign, er det på tide å takle verkelege utfordringar: komplekse system med mange entitetar, relasjonar og forretningsreglar.

I dette kapittelet lærer du:
- Designe mange-til-mange-relasjonar med koblingstabellar
- Handtere komplekse forretningsreglar i databasen
- Modellere hierarki og rekursive relasjonar
- Beste praksis for databasedesign i store prosjekt

Mange-til-mange-relasjonar

Ein mange-til-mange (M:N) relasjon eksisterer når:
- Éin A kan vere relatert til mange B
- Éin B kan vere relatert til mange A

Eksempel:
- Studentar ↔ Kurs (éin student tek fleire kurs, eitt kurs har fleire studentar)
- Forfattarar ↔ Bøker (éin forfattar skriv fleire bøker, éi bok kan ha fleire forfattarar)
- Skodespelarar ↔ Filmar

Problem: Kan ikkje modellerast direkte

Dette fungerer IKKJE:

-- 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øysing: Koblingstabell (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
);

-- Koblingstabell (é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)
);

Fordelar:
- Kan leggje til ekstra info (registrert_dato, karakter)
- Kan enkelt finne alle kurs for ein student
- Kan enkelt finne alle studentar i eit kurs

Eksempel: Spørjingar med koblingstabellar

Gitt tabellane 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 studentar 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 studentar som IKKJE tek nokon kurs:

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

Finn tal på studentar 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;

Hierarki og rekursive relasjonar

Nokre gonger må ein entitet referere til seg sjølv.

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 tilsette under ein leiar:

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

Finn tilsett med namnet til leiaren deira:

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

Eksempel 2: Kommentarar 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 ikkje bra på å hente heile tre (alle svar til svar til svar...). Løysing: Hent i Python/JavaScript og bygg tre der.

Forretningsreglar i databasen

Databasen kan handheve reglar 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-verdiar

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 blir køyrd 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;

Åtvaring: Triggers kan gjere databasen vanskeleg å debugge. Bruk med varsemd!

Beste praksis for databasedesign

1. Start med ER-diagram

Før du skriv éi linje SQL:
- Identifiser entitetar
- Teikn relasjonar
- Normaliser til 3NF
- Diskuter med teamet

2. Namngivingskonvensjonar

Tabellar:
- Eintal eller fleirtal? Vel éin standard (eg tilrår eintal)
- PascalCase eller snakecase? (Tilrår snakecase for SQL)

Kolonnar:
- Bruk skildrande namn: registrert_dato > reg_dat
- Primærnøkkel: {tabellnavn}ID (f.eks. brukerID)
- Framandnøklar: same namn 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. Indeksar 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

Endre aldri databaseskjema direkte i produksjon!

Bruk migrasjonar:

-- 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. Tryggleik

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

Assosiativ entitet: Koblingstabell som også har eigne attributt (f.eks. StudentKurs har karakter).

Rekursiv relasjon: Ein tabell som refererer til seg sjølv (f.eks. tilsett → leiar).

Hierarki: Tre-struktur lagra i database (organisasjon, kategoriar, kommentarar).

Constraint: Regel som databasen handhevar (CHECK, UNIQUE, NOT NULL, FOREIGN KEY).

Trigger: Kode som blir køyrd automatisk ved databasehendingar.

Migrering: Versjonert endring av databaseskjema.

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

Oppsummering

I dette kapittelet har du lært:

- Mange-til-mange: blir løyst med koblingstabell.
- Rekursive relasjonar: hierarki i same tabell.
- Forretningsreglar: CHECK, UNIQUE og DEFAULT.
- Triggers: handlingar som blir utløyste automatisk.
- Beste praksis: ER-diagram, namngiving og dokumentasjon.

Nøkkelbegrep