Luku 5

Tieto sarakkeissa

Periaate: Tietokannan taulun jokaisessa sarakkeessa on yksittäinen (atominen) tieto, kuten yksi luku tai yksi merkkijono. Sarakkeessa ei saa olla esimerkiksi listaa tiedoista.

Tämä periaate helpottaa tietokannan käsittelyä SQL-komentojen avulla: kun jokainen tieto on omassa sarakkeessaan, niin pystymme viittaamaan tietoon kätevästi komennoissa. Mutta mitä tehdä, jos haluaisimme tallentaa listan?

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:

sqlite> SELECT * FROM Tulokset;
id          opiskelija_id  pisteet   
----------  -------------  ----------
1           1              6,5,1,4   
2           2              3,6,6,6   
3           3              6,4,0,6  

Ideana on, että sarakkeessa pisteet 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 ne ovat merkkijonon sisällä. Esimerkiksi jos haluamme laskea jokaisen opiskelijan yhteispisteet, tarvitsemme seuraavan tapaisen kyselyn:

SELECT opiskelija_id, SUBSTR(pisteet,1,1)+
                      SUBSTR(pisteet,3,1)+
                      SUBSTR(pisteet,5,1)+
                      SUBSTR(pisteet,7,1) FROM Tulokset;

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.

Esimerkki: Vaihe 2

Seuraavassa taulussa pisteille on neljä saraketta, jolloin voimme käsitellä niitä yksitellen:

sqlite> SELECT * FROM Tulokset;
id          opiskelija_id  pisteet1    pisteet2    pisteet3    pisteet4  
----------  -------------  ----------  ----------  ----------  ----------
1           1              6           5           1           4         
2           2              3           6           6           6         
3           3              6           4           0           6     

Tämän ansiosta saamme jo toteutettua kyselyn mukavammin:

SELECT opiskelija_id, pisteet1+pisteet2+pisteet3+pisteet4 FROM Tulokset;

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.

Esimerkki: 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ä:

sqlite> SELECT * FROM Tulokset;
id          opiskelija_id  tehtava_id  pisteet   
----------  -------------  ----------  ----------
1           1              1           6         
2           1              2           5         
3           1              3           1         
4           1              4           4         
5           2              1           3         
6           2              2           6         
7           2              3           6         
8           2              4           6         
9           3              1           6         
10          3              2           4         
11          3              3           0         
12          3              4           6

Nyt voimme hakea kunkin opiskelijan yhteispisteet näin:

SELECT opiskelija_id, SUM(pisteet) FROM Tulokset GROUP BY opiskelija_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 olisi paljon rivejä.

Jatka tästä seuraavaan osaan: