Vastus (22.02.2023 14:45): Surrogaatvõti on lihtvõti, mille väärtused on süsteemi genereeritud unikaalsed täisarvud. Need väärtused on kasutusel tarkvara siseselt ridade identifitseerimiseks, kuid andmebaasi kasutavatele inimestele ei tähenda need väärtused midagi ja ta ei oska nende alusel olemeid üksteisest eristada. Näide: kes on töötaja, kelle id=34583?

Sisulise tähendusega võtme väärtus on seevastu ka arusaadav inimkasutajale ja ta oskab nende väärtuste alusel olemeid üksteisest eristada. Näide: kes on töötaja, kelle meiliaadress on Erki.Eessaar@taltech.ee?

Vaatleme kahte tabelite disaini.

Disain 1 (kõikides tabelites surrogaatvõti - "id" veerg).

Riik (riik_id, riik_kood, nimetus)
Primaarvõti (riik_id)

Klient (klient_id, riik_id, e_meil, eesnimi, perenimi)
Primaarvõti (klient_id)
Välisvõti (riik_id) Viitab Riik (riik_id)

Toode (toode_id, toote_kood, hind)
Primaarvõti (toode_id)

Tellimus (tellimus_id, klient_id, reg_aeg)
Primaarvõti (tellimus_id)
Välisvõti (klient_id) Viitab Klient (klient_id)

Tellimuse_rida (tellimus_id, toode_id, kogus)
Primaarvõti (tellimus_id, toode_id)
Välisvõti (tellimus_id) Viitab Tellimus (tellimus_id)
Välisvõti (toode_id) Viitab Toode (toode_id)

Disain 2 (kõikides tabelites, kus võimalik, on sisulise tähendusega võtmed).

Riik (riik_kood, nimetus)
Primaarvõti (riik_kood)
Alternatiivvõti (nimetus)

Klient (e_meil, riik_kood, eesnimi, perenimi)
Primaarvõti (e_meil)
Välisvõti (riik_kood) Viitab Riik (riik_kood)

Toode (toote_kood, hind)
Primaarvõti (toote_kood)

Tellimus (tellimus_id, e_meil, reg_aeg)
Primaarvõti (tellimus_id)
Välisvõti (e_meil) Viitab Klient (e_meil)

Tellimuse_rida (tellimus_id, toote_kood, kogus)
Primaarvõti (tellimus_id, toote_kood)
Välisvõti (tellimus_id) Viitab Tellimus (tellimus_id)
Välisvõti (toote_kood) Viitab Toode (toote_kood)

Millised on disaini 1 probleemid võrreldes disainiga 2?

Probleem 1: Päringud on keerukamad, sest tuleb lugeda andmeid suuremast hulgast tabelitest. Päringutes tuleb ühendada rohkem tabeleid. Lause täitmine võtab rohkem aega.

Ülesanne: Leia PostgreSQL andmebaasis kordusteta selliste toodete koodid, mida on ostnud Eesti kliendid.

Disain 1

SELECT DISTINCT toote_kood
FROM Riik INNER JOIN Klient USING (riik_id)
INNER JOIN Tellimus USING (klient_id)
INNER JOIN Tellimuse_rida USING (tellimus_id)
INNER JOIN Toode USING (toode_id)
WHERE Riik.riik_kood='EST';

Disain 2

SELECT DISTINCT toote_kood
FROM Klient INNER JOIN Tellimus USING (e_meil)
INNER JOIN Tellimuse_rida USING (tellimus_id)
WHERE Klient.riik_kood='EST';

Disain 2 korral leian riigi koodi tabelist Klient ja toote koodi tabelist Tellimuse_rida. Disain 1 korral tuleb lugeda viit tabelit, disain 2 korral tuleb lugeda kolme tabelit.

Probleem 2: Surrogaatvõtmete läbival kasutamisel tekib sageli olukord, et sisulised võtmed lähevad andmebaasi arendajal täiesti meelest ära ja neid ei jõustata andmebaasis. Disain 1 korral peaksid Riik tabelis olema unikaalsed (riik_kood) ja (nimetus). Klient tabelis peaks olema unikaalne (e_meil). Toode tabelis peaks olema unikaalne (toode_kood). Enamikes andmebaasisüsteemides tekivad PRIMARY KEY ja UNIQUE kitsenduste toetuseks indeksid, seega suureneb indeksite ja nende kasutatav kettaruumi hulk.