6. Tietokannan suunnittelu
Suunnittelun periaatteet
Tietokannan suunnittelussa tulee päättää tietokannan rakenne: mitä tauluja tietokannassa on sekä mitä sarakkeita kussakin taulussa on. Tähän on sinänsä suuri määrä mahdollisuuksia, mutta tuntemalla muutaman periaatteen pääsee pitkälle.
Hyvä tavoite suunnittelussa on, että tuloksena olevaa tietokantaa on mukavaa käyttää SQL-kielen avulla. Tietokannan rakenteen tulisi olla sellainen, että tietoa pystyy hakemaan ja muuttamaan näppärästi SQL-komennoilla.
Tietokannan suunnittelun periaatteet ovat hyödyllisiä ja johtavat usein toimiviin ratkaisuihin. Kuitenkin kannattaa miettiä, mikä periaatteissa on taustalla ja milloin kannattaa mahdollisesti tehdä toisin. Hyvä tavoite on luoda käyttötarkoitukseen sopiva tietokanta eikä noudattaa periaatteita ilman omaa ajattelua.
Taulu vs. luokka
Tietokannan taulun määrittely kuvaa, minkä tyyppistä tietoa tauluun voidaan tallentaa. Esimerkiksi seuraavassa taulussa Movies jokainen taulun rivi sisältää elokuvan nimen ja julkaisuvuoden:
CREATE TABLE Movies (
id INTEGER PRIMARY KEY,
name TEXT,
release_year INTEGER
);
INSERT INTO Movies (name, release_year) VALUES ('Lumikki', 1937);
INSERT INTO Movies (name, release_year) VALUES ('Fantasia' 1940);
INSERT INTO Movies (name, release_year) VALUES ('Pinocchio', 1940);
Monissa ohjelmointikielissä luokan määrittely kuvaa, minkä tyyppistä tietoa oliot sisältävät. Esimerkiksi seuraava Python-koodi määrittelee luokan Movie, joka sisältää elokuvan nimen ja julkaisuvuoden. Tämän jälkeen koodi lisää olioita listaan.
@dataclass
class Movie:
name: str
release_year: int
movies = []
movies.append(Movie("Lumikki", 1937))
movies.append(Movie("Fantasia", 1940))
movies.append(Movie("Pinocchio", 1940))
Tietokannan taulun määrittely muistuttaa siis ohjelmoinnin luokkaa, ja yksittäinen taulun rivi on lähellä luokasta luotua oliota.
Yksi vai useita tauluja?
Ohjelmoinnissa kaikki saman tyyppiset oliot perustuvat samaan luokkaan, ja vastaavasti tietokannassa kaikki saman tyyppiset rivit ovat yhdessä taulussa. Tämän ansiosta voimme käsitellä rivejä kätevästi SQL-komennoilla.
Esimerkiksi jos tietokannassa on elokuvia, hyvä ratkaisu on tallentaa kaikki elokuvat samaan tauluun Movies:
id name release_year
-- --------- ------------
1 Lumikki 1937
2 Fantasia 1940
3 Pinocchio 1940
4 Dumbo 1941
5 Bambi 1942
Tästä taulusta voimme hakea esimerkiksi vuoden 1940 elokuvat näin:
SELECT name FROM Movies WHERE release_year = 1940;
Mutta mitä kävisi, jos jakaisimmekin elokuvat moneen tauluun? Esimerkiksi voisimme jakaa elokuvat tauluihin vuosien mukaan. Tällöin taulussa Movies1940 olisi vuoden 1940 elokuvat, ja voisimme hakea ne näin:
SELECT name FROM Movies1940;
Tällainen ratkaisu toimii niin kauan, kuin haluamme hakea vain tietyn vuoden elokuvia. Kuitenkin tietokanta muuttuu vaikeakäyttöiseksi heti, jos haluamme tehdä jotain muita hakuja. Esimerkiksi jos haluamme hakea kaikki elokuvat vuosilta 1940–1950, tarvitsemme useita kyselyjä:
SELECT name FROM Movies1940;
SELECT name FROM Movies1941;
SELECT name FROM Movies1942;
...
SELECT name FROM Movies1950;
Kuitenkin kun elokuvat ovat samassa taulussa, niin selviämme yhdellä kyselyllä:
SELECT name FROM Movies WHERE release_year BETWEEN 1940 AND 1950;
Kun elokuvat ovat yhdessä taulussa, pystymme käsittelemään niitä monipuolisesti yksittäisillä SQL-komennoilla, mikä ei olisi mahdollista, jos tauluja olisi useita.
Viittausten toteutus
Yksi moneen -suhde
Tarkastellaan tietokantaa, johon tallennetaan kursseja ja opettajia. Taulujen välillä on yksi moneen -suhde: jokaisella kurssilla on yksi opettaja, kun taas yhdellä opettajalla voi olla monta kurssia. Voimme luoda tietokannan taulut näin:
CREATE TABLE Teachers (
id INTEGER PRIMARY KEY,
name TEXT
);
CREATE TABLE Courses (
id INTEGER PRIMARY KEY,
name TEXT,
teacher_id INTEGER REFERENCES Teachers
);
Taulussa Courses sarake teacher_id viittaa tauluun Teachers, eli siinä on jonkun opettajan id-numero. Viittaus ilmaistaan REFERENCES-määreellä, joka kertoo, että sarakkeessa oleva kokonaisluku viittaa tauluun Teachers.
Voisimme laittaa tauluihin tietoa vaikkapa seuraavasti:
INSERT INTO Teachers (name) VALUES ('Kaila');
INSERT INTO Teachers (name) VALUES ('Kivinen');
INSERT INTO Teachers (name) VALUES ('Laaksonen');
INSERT INTO Courses (name, teacher_id) VALUES ('Tietoverkot', 1);
INSERT INTO Courses (name, teacher_id) VALUES ('Graduseminaari', 1);
INSERT INTO Courses (name, teacher_id) VALUES ('PHP-ohjelmointi', 3);
INSERT INTO Courses (name, teacher_id) VALUES ('Neuroverkot', 2);
Monta moneen -suhde
Tarkastellaan sitten tilannetta, jossa useampi opettaja voi järjestää kurssin yhteisesti. Tällöin kyseessä on monta moneen -suhde, koska kurssilla voi olla monta opettajaa ja opettajalla voi olla monta kurssia.
Nyt taulun Teachers rivi voi liittyä useaan taulun Courses riviin, ja vastaavasti taulun Courses rivi voi liittyä useaan taulun Teachers riviin. Koska tietokannan rivi ei voi sisältää listaa viittauksista, emme voi lisätä viittauksia suoraan kumpaankaan tauluun, vaan meidän täytyy luoda uusi taulu viittauksille:
CREATE TABLE Teachers (
id INTEGER PRIMARY KEY,
name TEXT
);
CREATE TABLE Courses (
id INTEGER PRIMARY KEY,
name TEXT
);
CREATE TABLE CourseTeachers (
course_id INTEGER REFERENCES Courses,
teacher_id INTEGER REFERENCES Teachers
);
Muutoksena aiempaan on, että taulussa Courses ei ole enää viittausta tauluun Teachers, mutta sen sijaan tietokannassa on uusi taulu CourseTeachers, joka viittaa kumpaankin tauluun. Jokainen rivi tässä taulussa kuvaa yhden suhteen muotoa “kurssilla id opettaa opettaja id”.
Esimerkiksi voisimme ilmaista näin, että kurssilla on kaksi opettajaa:
INSERT INTO Teachers (name) VALUES ('Laaksonen');
INSERT INTO Teachers (name) VALUES ('Luukkainen');
INSERT INTO Courses (name) VALUES ('PHP-ohjelmointi');
INSERT INTO Courses (name) VALUES ('Neuroverkot');
INSERT INTO CourseTeachers VALUES (1, 1);
INSERT INTO CourseTeachers VALUES (1, 2);
INSERT INTO CourseTeachers VALUES (2, 1);
Tämä tarkoittaa, että Laaksonen ja Luukkainen opettavat kurssilla PHP-ohjelmointi. Lisäksi Laaksonen opettaa kurssilla Neuroverkot.
Huomaa, että voisimme käyttää tätä ratkaisua myös aiemmassa tilanteessa, jossa kurssilla on aina tasan yksi opettaja. Tässä tapauksessa tietokannassa olisi kuitenkin tavallaan turha taulu.
Tiedon atomisuus
Periaate: Tietokannan taulun jokaisessa sarakkeessa on yksittäinen eli atominen tieto, kuten yksi luku tai yksi merkkijono. Sarakkeessa ei saa olla listaa tiedoista.
Tämä periaate helpottaa tietokannan käsittelyä SQL-komentojen avulla: kun jokainen tieto on omassa sarakkeessaan, tietoon pystyy viittaamaan kätevästi.
Tietokantaan voidaan tallentaa lista luomalla taulu, jonka jokainen rivi on listan yksittäinen alkio, kuten äskeinen taulu CourseTeachers. Mutta miksi emme voisi vain tallentaa listaa yhteen sarakkeeseen? Seuraava esimerkki selventää asiaa.
Esimerkki
Vaihe 1
Haluamme tallentaa tietokantaan opiskelijoiden tenttituloksia. Tentissä on neljä tehtävää, joista voi saada 0–6 pistettä. Voisimme koettaa tallentaa pisteet näin:
student_id points
---------- -------
1 6,5,1,4
2 3,6,6,6
3 6,4,0,6
Ideana on, että sarakkeessa points on merkkijono, jossa on lista pisteistä pilkuilla erotettuina. Tämä ratkaisu kuitenkin rikkoo periaatetta, että jokaisessa sarakkeessa on yksittäinen tieto. Mitä vikaa ratkaisussa on?
Ratkaisun ongelmana on, että meidän on vaivalloista koettaa päästä pisteisiin käsiksi SQL-komennoissa, koska pisteet ovat merkkijonon sisällä. Esimerkiksi jos haluamme laskea jokaisen opiskelijan yhteispisteet, tarvitsemme seuraavan tapaisen kyselyn:
SELECT student_id,
SUBSTR(points, 1, 1) + SUBSTR(points, 3, 1) +
SUBSTR(points, 5, 1) + SUBSTR(points, 7, 1) AS total_points
FROM Results;
Tässä funktio SUBSTR erottaa merkkijonosta tietyssä kohdassa olevan osajonon. Kysely on kuitenkin hankala ja lisäksi toimii vain, kun pisteitä on tasan neljä ja ne ovat yksinumeroisia. Tarvitsemme paremman tavan tallentaa pisteet.
Vaihe 2
Seuraavassa taulussa pisteille on neljä saraketta, jolloin voimme käsitellä niitä yksitellen:
student_id points1 points2 points3 points4
---------- ------- ------- ------- -------
1 6 5 1 4
2 3 6 6 6
3 6 4 0 6
Tämän ansiosta saamme jo toteutettua kyselyn mukavammin:
SELECT student_id,
points1 + points2 + points3 + points4 AS total_points
FROM Results;
Tämä ratkaisu on selkeästi parempaan suuntaan, mutta siinä on edelleen ongelmia. Vaikka pisteet ovat eri sarakkeissa, oletuksena on edelleen, että tehtäviä on tasan neljä. Jos tehtävien määrä muuttuu, joudumme muuttamaan taulun rakennetta ja kaikkia pisteisiin liittyviä SQL-komentoja, mikä ei ole hyvä tilanne.
Vaihe 3
Kun haluamme tallentaa listan tietokantaan, hyvä ratkaisu on tallentaa jokainen listan alkio omalle rivilleen. Tämän esimerkin tapauksessa voimme luoda taulun, jonka jokainen rivi ilmaisee tietyn opiskelijan pisteet tietyssä tehtävässä:
student_id task_id points
---------- ------- ------
1 1 6
1 2 5
1 3 1
1 4 4
2 1 3
2 2 6
2 3 6
2 4 6
3 1 6
3 2 4
3 3 0
3 4 6
Nyt voimme hakea kunkin opiskelijan yhteispisteet näin:
SELECT student_id, SUM(points) AS total_points
FROM Results
GROUP BY student_id;
Tämä on yleiskäyttöinen kysely eli se toimii yhtä hyvin riippumatta tehtävien määrästä. Pystymme hyödyntämään summan laskemisessa funktiota SUM sen sijaan, että meidän tulisi luetella kaikki tehtävät itse.
Huomaa, että muutoksen seurauksena taulun rivien määrä kasvoi selvästi. Tätä ei kannata kuitenkaan hätkähtää: tietokantajärjestelmät on toteutettu niin, että ne toimivat hyvin, vaikka taulussa olisi paljon rivejä.
Mikä on atomista tietoa?
Atomisen tiedon käsite ei ole hyvin määritelty. Selkeästi lista ei ole atominen tieto, mutta onko sitten vaikka merkkijonokaan, jossa on useita sanoja?
Tarkastellaan esimerkkinä tilannetta, jossa taulun sarakkeessa on käyttäjän nimi. Onko tämä huonoa suunnittelua, koska samassa sarakkeessa on etu- ja sukunimi?
id name
-- --------------
1 Anna Virtanen
2 Maija Korhonen
3 Pasi Lahtinen
Voisimme myös tallentaa etu- ja sukunimen erikseen näin:
id first_name last_name
-- ---------- ---------
1 Anna Virtanen
2 Maija Korhonen
3 Pasi Lahtinen
Riippuu tilanteesta, kumpi taulu on parempi. Jos järjestelmässä on erityisesti tarvetta etsiä tietoa etu- tai sukunimen perusteella (esimerkiksi etsiä kaikki käyttäjät, joiden etunimi on Anna), jälkimmäinen taulu on parempi. Kuitenkaan usein ei ole näin eikä ole mitään pahaa tallentaa samaan sarakkeeseen etu- ja sukunimi.
Vastaavasti jos tietokantaan tallennetaan käyttäjän lähettämä viesti, siinä voi olla monia sanoja eli tavallaan viesti on lista sanoja, mutta on silti hyvä ratkaisu tallentaa koko viesti yhteen sarakkeeseen, koska viestiä käsitellään tietokannassa yhtenä kokonaisuutena. Olisi hyvin huono ratkaisu jakaa “atomisesti” viestin sanat omiin sarakkeisiin.
Kannattaakin ajatella asiaa niin, että jos jotain tietoa on tarvetta käsitellä erillisenä SQL-komennoissa, niin se on atominen tieto, jonka tulee olla omassa sarakkeessa. Jos taas tietoon ei viitata SQL-komennoissa, se voi olla sarakkeessa osana laajempaa kokonaisuutta.
Toisteinen tieto
Periaate: Jokainen tieto on tasan yhdessä paikassa tietokannassa. Tietokannassa ei ole tietoa, jonka voi laskea tai päätellä tietokannan muun sisällön perusteella.
Tätä periaatetta seuraamalla tietokannan sisällön päivittäminen on helppoa, koska päivitys riittää tehdä yhteen paikkaan eikä se vaikuta tietokannan muihin osiin.
Esimerkki 1
Tallennamme järjestelmään käyttäjien lähettämiä viestejä seuraavasti tauluun Messages:
id user message
-- ---------- --------------
1 Anna123 Missä olet?
2 Joulupukki Bussissa vielä
3 Anna123 Meneekö kauan?
4 Joulupukki 5 min
Tämä on muuten toimiva ratkaisu, mutta tietokannan sisältöä on hankalaa päivittää, jos käyttäjä päättää vaihtaa nimeään. Esimerkiksi jos Anna123 haluaa muuttaa nimeään, muutos täytyy tehdä jokaiseen viestiin, jonka hän on lähettänyt.
Parempi ratkaisu on toteuttaa tietokanta niin, että käyttäjän nimi on vain yhdessä paikassa. Luonteva paikka tälle on taulu Users, joka sisältää käyttäjät:
id name
-- ----------
1 Anna123
2 Joulupukki
Muissa tauluissa on vain viitteenä käyttäjän id-numero, joka on muuttumaton tieto. Esimerkiksi taulu Messages näyttää nyt tältä:
id user_id message
-- ------- --------------
1 1 Missä olet?
2 2 Bussissa vielä
3 1 Meneekö kauan?
4 2 5 min
Tämän jälkeen käyttäjän nimen muuttaminen on helppoa, koska muutos riittää tehdä taulun Users yhteen riviin ja muutos päivittyy heti kaikkialle, koska muissa tauluissa viitataan edelleen oikeaan riviin.
Tämä monimutkaistaa kyselyjä, koska tietoa täytyy hakea useista tauluista, mutta ratkaisu on kuitenkin kokonaisuuden kannalta hyvä.
Vieläkin toisteisuutta?
Äskeisestä muutoksesta huolimatta tietokannassa saattaa esiintyä edelleen toisteisuutta. Esimerkiksi seuraavassa tilanteessa käyttäjät lähettävät samanlaisen viestin “Hei!”. Pitäisikö tietokannan rakennetta parantaa?
id user_id message
-- ------- -------
1 1 Hei!
2 2 Hei!
Tässä tapauksessa ei olisi hyvä idea toteuttaa tietokantaa niin, että jos kaksi käyttäjää lähettää saman sisältöisen viestin, viestin sisältö tallennetaan vain yhteen paikkaan.
Vaikka viesteissä on sama sisältö, ne ovat erillisiä viestejä, joiden ei ole tarkoitus viitata samaan asiaan. Jos käyttäjä 1 muuttaa viestin sisältöä, muutoksen ei tule heijastua käyttäjän 2 viestiin, vaikka siinä sattuu olemaan tällä hetkellä sama sisältö.
Esimerkki 2
Tallennamme tietokantaan tietoa opiskelijoiden suorituksista. Tietokannasta voidaan kysyä, montako opintopistettä opiskelija on suorittanut.
Seuraavassa tietokannassa jokaisen opiskelijan yhteyteen on tallennettu tieto, montako opintopistettä hän on suorittanut. Taulun Students sisältönä on:
id name total_credits
-- ------ -------------
1 Maija 20
2 Uolevi 10
Taulussa Completions puolestaan on seuraavat rivit:
id student_id course_id credits
-- ---------- --------- -------
1 1 1 5
2 1 2 5
3 1 4 10
4 2 1 5
5 2 3 5
Voimme hakea helposti opiskelijan opintopisteiden yhteismäärän näin:
SELECT total_credits FROM Students WHERE name = 'Maija';
Kuitenkin tietokannassa on toisteista tietoa: taulun Students sarakkeen total_credits sisältö voidaan laskea taulun Completions avulla. Esimerkiksi Maijan opintopisteiden määrä 20 taulussa Students voidaan myös laskea summana 5 + 5 + 10 taulusta Completions.
Ongelmana on, että suoritusta lisätessä täytyy sekä lisätä uusi rivi tauluun Completions että päivittää opintopisteiden yhteismäärä tauluun Students. Jos päivitys unohtuu tehdä tai epäonnistuu, tietokantaan tulee ristiriitaista tietoa.
Pääsemme eroon toisteisesta tiedosta poistamalla sarakkeen total_credits taulusta Students:
id name
-- ------
1 Maija
2 Uolevi
Tämän muutoksen seurauksena on vaikeampaa selvittää opiskelijan opintopisteiden yhteismäärä, koska tieto täytyy laskea suorituksista lähtien:
SELECT SUM(Completions.credits) AS total_credits
FROM Completions, Students
WHERE Completions.student_id = Students.id AND Students.name = 'Maija';
Tämä on kuitenkin kokonaisuutena hyvä muutos, koska nyt voimme huoletta muuttaa suorituksia taulussa Completions ja luottaa siihen, että saamme aina laskettua ajantasaisen tiedon opiskelijan opintopisteistä.
Muutokset vs. kyselyt
Vaikka ihanteena on, että tietokannassa ei ole toisteista tietoa, joskus kuitenkin toisteista tietoa tarvitaan hakujen tehostamiseksi. Toisteinen tieto vaikeuttaa tietokannan muuttamista mutta helpottaa kyselyjen tekemistä.
Usein esiintyvä ilmiö tietojenkäsittelytieteessä on, että joudumme tasapainoilemaan sen kanssa, haluammeko muuttaa vai hakea tehokkaasti tietoa ja paljonko tilaa voimme käyttää. Tämä tulee tietokantojen lisäksi vastaan esimerkiksi algoritmien suunnittelussa.
Jos tietokannassa ei ole toisteista tietoa, muutokset ovat helppoja, koska jokainen tieto on vain yhdessä paikassa eli riittää muuttaa vain yhden taulun yhtä riviä. Toinen hyvä puoli on, että toisteinen tieto ei vie tilaa tietokannassa. Toisaalta kyselyt voivat olla monimutkaisia ja hitaita, koska halutut tiedot pitää kerätä kasaan eri puolilta tietokantaa.
Toisteista tietoa lisäämällä pystymme nopeuttamaan kyselyjä mutta toisaalta muutokset hankaloituvat, koska muutettu tieto pitää päivittää useaan paikkaan. Samaan aikaan myös tietokannan tilankäyttö kasvaa toisteisen tiedon takia.
Ei ole mitään yleistä sääntöä, paljonko toisteista tietoa kannattaa lisätä, vaan tämä riippuu tietokannan sisällöstä ja halutuista kyselyistä. Yksi hyvä tapa on aloittaa tilanteesta, jossa toisteista tietoa ei ole, ja lisätä sitten toisteista tietoa tarvittaessa, jos kyselyt eivät muuten ole riittävän tehokkaita.
Suunnitteluesimerkki
Tarkastellaan lopuksi laajempaa esimerkkiä, jossa tavoitteemme on suunnitella tietokanta yliopiston luentosalien varaamista varten. Tietokannan tulee mahdollistaa seuraavat toiminnot:
- Käyttäjä voi kirjautua järjestelmään antamalla tunnuksen ja salasanan. Järjestelmässä on peruskäyttäjiä ja ylläpitäjiä.
- Käyttäjä näkee varattavan salin tunnuksen, rakennuksen, istumapaikkojen määrän ja hintaluokan. Esimerkki: A111, Exactum, 280, A10
- Varauksen tuntihinta riippuu hintaluokasta. Käyttäjä näkee kokonaishinnan varauksen yhteydessä.
- Rakennuksilla on tietyt aukioloajat. Rakennuksen salit ovat varattavissa rakennuksen ollessa avoinna.
- Käyttäjä pystyy etsimään sopivaa salia ilmoittamalla rakennuksen ja istumapaikkojen minimimäärän.
- Käyttäjä pystyy tekemään salista varauspyynnön, jossa on tietty aikaväli tiettynä päivänä sekä kommentti. Ylläpitäjä vahvistaa varauksen varauspyynnön perusteella.
Suunnittelun vaiheet
Tietokannan suunnittelu etenee yleensä pikkuhiljaa niin, että tietokantaan lisätään uusia tauluja ja sarakkeita aina, kun uusissa toiminnoissa on tarvetta niille.
Seuraavaksi näemme, miten esimerkkitietokanta rakentuu vaihe vaiheelta vaadittujen toimintojen perusteella.
Kirjautuminen järjestelmään
- Käyttäjä voi kirjautua järjestelmään antamalla tunnuksen ja salasanan. Järjestelmässä on peruskäyttäjiä ja ylläpitäjiä.
Tämä on tavallinen toiminto, josta on hyvä aloittaa tietokannan suunnittelu. Tarvitsemme taulun, jossa on käyttäjän tunnus ja salasana:
CREATE TABLE Users (
id INTEGER PRIMARY KEY,
username TEXT,
password TEXT
);
Koska järjestelmässä on kahdenlaisia käyttäjiä (peruskäyttäjät ja ylläpitäjät), tämä tieto tulee tallentaa myös tietokantaan. Kaksi mahdollista ratkaisua ovat:
- Ratkaisu 1: Tauluun
Userslisätään sarake, joka ilmaisee käyttäjän roolin. - Ratkaisu 2: Taulussa
Userson peruskäyttäjät ja luodaan toinen tauluAdmins, jossa on ylläpitäjät.
Kokemus on osoittanut, että ratkaisu 1 on yleensä parempi tietokannan käyttämisen kannalta. Lisätään tauluun Users uusi sarake user_role, joka ilmaisee käyttäjän roolin (esim. 1 = peruskäyttäjä, 2 = ylläpitäjä):
CREATE TABLE Users (
id INTEGER PRIMARY KEY,
username TEXT,
password TEXT,
user_role INTEGER
);
Varattavan salin tiedot
- Käyttäjä näkee varattavan salin tunnuksen, rakennuksen, istumapaikkojen määrän ja hintaluokan. Esimerkki: A111, Exactum, 280, A10
Hyvä ratkaisu on luoda erilliset taulut Buildings ja Categories, joihin tallennetaan tiedot rakennuksista ja hintaluokista:
CREATE TABLE Buildings (
id INTEGER PRIMARY KEY,
name TEXT
);
CREATE TABLE Categories (
id INTEGER PRIMARY KEY,
name TEXT,
);
Tämän jälkeen voimme luoda vielä taulun Rooms, joka sisältää tiedot saleista. Tämä taulu viittaa tauluihin Buildings ja Categories.
CREATE TABLE Rooms (
id INTEGER PRIMARY KEY,
name TEXT,
building_id INTEGER REFERENCES Buildings,
seat_count INTEGER,
category_id INTEGER REFERENCES Categories
);
Nyt voisimme lisätä järjestelmään seuraavasti tiedot salista A111:
INSERT INTO Buildings (name) VALUES ('Exactum');
INSERT INTO Categories (name) VALUES ('A10');
INSERT INTO Rooms (name, building_id, seat_count, category_id)
VALUES ('A111', 1, 280, 1);
Varauksen kokonaishinta
- Varauksen tuntihinta riippuu hintaluokasta. Käyttäjä näkee kokonaishinnan varauksen yhteydessä.
Tätä toimintoa varten voimme lisätä tauluun Categories sarakkeen price_per_hour, joka ilmaisee hintaluokan tuntihinnan:
CREATE TABLE Categories (
id INTEGER PRIMARY KEY,
name TEXT,
price_per_hour INTEGER
);
Tämän jälkeen voisimme laskea seuraavasti, paljonko maksaa neljän tunnin varaus saliin A111:
SELECT Categories.price_per_hour * 4 AS total_price
FROM Rooms, Categories
WHERE Rooms.category_id = Categories.id AND Rooms.name = 'A111';
Rakennusten aukioloajat
- Rakennuksilla on tietyt aukioloajat. Rakennuksen salit ovat varattavissa rakennuksen ollessa avoinna.
Rakennukset ovat yleensä avoinna arkipäivinä mutta eivät viikonloppuisin ja pyhäpäivinä. Lisäksi aukioloajat voivat muuttua esimerkiksi kesän ajaksi.
Käytännössä voisi olla vaikeaa määritellä kaikissa tapauksissa toimivaa säännöstöä, joka kertoisi, milloin tietty rakennus on auki. Sen sijaan hyvä ratkaisu on luoda taulu OpeningHours, joka sisältää rivin jokaisen rakennuksen jokaiselle aukiolopäivälle:
CREATE TABLE OpeningHours (
id INTEGER PRIMARY KEY,
building_id INTEGER REFERENCES Buildings,
start_time TIMESTAMP,
end_time TIMESTAMP
);
Esimerkiksi seuraavat komennot lisäävät tiedot, miten Exactum-rakennus on auki syyskuun ensimmäisellä viikolla vuonna 2025:
INSERT INTO OpeningHours (building_id, start_time, end_time)
VALUES (1, '2025-09-01 08:00:00', '2025-09-01 20:00:00');
INSERT INTO OpeningHours (building_id, start_time, end_time)
VALUES (1, '2025-09-02 08:00:00', '2025-09-02 20:00:00');
INSERT INTO OpeningHours (building_id, start_time, end_time)
VALUES (1, '2025-09-03 08:00:00', '2025-09-03 20:00:00');
INSERT INTO OpeningHours (building_id, start_time, end_time)
VALUES (1, '2025-09-04 08:00:00', '2025-09-04 20:00:00');
INSERT INTO OpeningHours (building_id, start_time, end_time)
VALUES (1, '2025-09-05 08:00:00', '2025-09-05 18:00:00');
Sopivan salin etsiminen
- Käyttäjä pystyy etsimään sopivaa salia ilmoittamalla rakennuksen ja istumapaikkojen minimimäärän.
Tämä toiminto onnistuu olemassa olevien taulujen avulla. Esimerkiksi seuraava komento etsii Exactumista salia, johon mahtuu ainakin 100 henkilöä:
SELECT Rooms.name
FROM Rooms, Buildings
WHERE Rooms.building_id = Buildings.id AND
Rooms.seat_count >= 100 AND Buildings.name = 'Exactum';
Varauspyyntö ja vahvistus
- Käyttäjä pystyy tekemään salista varauspyynnön, jossa on tietty aikaväli tiettynä päivänä sekä kommentti. Ylläpitäjä vahvistaa varauksen varauspyynnön perusteella.
Voimme tehdä seuraavan taulun Requests, johon tallennetaan varauspyynnöt. Jokaiseen pyyntöön liittyy käyttäjä, sali, varauksen alku- ja loppuaika sekä kommentti.
CREATE TABLE Requests (
id INTEGER PRIMARY KEY
user_id INTEGER REFERENCES Users,
room_id INTEGER REFERENCES Rooms,
start_time TIMESTAMP,
end_time TIMESTAMP,
comment TEXT
);
Mitä tapahtuu sitten, kun ylläpitäjä hyväksyy varauksen? Yksi ratkaisu olisi tehdä toinen taulu Reservations, joka sisältää hyväksytyt varaukset:
CREATE TABLE Reservations (
id INTEGER PRIMARY KEY,
user_id INTEGER REFERENCES Users,
room_id INTEGER REFERENCES Rooms,
start_time TIMESTAMP,
end_time TIMESTAMP
);
Vaikka varauspyyntö ja vahvistettu varaus ovat sinänsä eri asioita, tässä tulisi kuitenkin ongelmaksi, että tauluissa Requests ja Reservations on hyvin samanlaista tietoa. Parempi ratkaisu voisi olla yhdistää nämä taulut esimerkiksi seuraavasti:
CREATE TABLE Reservations (
id INTEGER PRIMARY KEY,
user_id INTEGER REFERENCES Users,
room_id INTEGER REFERENCES Rooms,
start_time TIMESTAMP,
end_time TIMESTAMP,
status INTEGER
);
Tässä sarake status ilmaisee varauksen tilan. Voisimme esimerkiksi päättää, että tila 1 tarkoittaa varauspyyntöä ja tila 2 tarkoittaa vahvistettua varausta.
Tietokannan kuvaaminen
Tietokannan rakenteen kuvaamiseen on kaksi tavallista tapaa: graafinen tietokantakaavio, joka esittää taulujen suhteet, sekä SQL-skeema, jossa on taulujen luontikomennot.
Tietokantakaavio
Tietokantakaavio on tietokannan graafinen esitys, jossa jokainen tietokannan taulu on laatikko, joka sisältää taulun nimen ja sarakkeet listana. Rivien viittaukset toisiinsa esitetään laatikoiden välisinä yhteyksinä.
Tietokantakaavion piirtämiseen on monia vähän erilaisia tapoja. Seuraava kaavio on luotu netissä olevalla työkalulla dbdiagram.io:
SQL-skeema
SQL-skeema sisältää CREATE TABLE -komennot, joiden avulla tietokanta voidaan muodostaa. Seuraava SQL-skeema vastaa tietokantaamme:
CREATE TABLE Users (
id INTEGER PRIMARY KEY,
username TEXT,
password TEXT,
user_role INTEGER
);
CREATE TABLE Buildings (
id INTEGER PRIMARY KEY,
name TEXT
);
CREATE TABLE Categories (
id INTEGER PRIMARY KEY,
name TEXT,
price_per_hour INTEGER
);
CREATE TABLE Rooms (
id INTEGER PRIMARY KEY,
name TEXT,
building_id INTEGER REFERENCES Buildings,
seat_count INTEGER,
category_id INTEGER REFERENCES Categories
);
CREATE TABLE OpeningHours (
id INTEGER PRIMARY KEY,
building_id INTEGER REFERENCES Buildings,
start_time TIMESTAMP,
end_time TIMESTAMP
);
CREATE TABLE Reservations (
id INTEGER PRIMARY KEY,
user_id INTEGER REFERENCES Users,
room_id INTEGER REFERENCES Rooms,
start_time TIMESTAMP,
end_time TIMESTAMP,
status INTEGER
);