Oppaat: MySQL ja PHP: Osa 8 - Tietokannan suunnittelu

Hyvä tavoite tietokannan suunnittelussa on, että tietoa on helppoa lisätä, hakea ja muuttaa. Käytettävissä olevat SQL-kyselyt vaikuttavat oleellisesti siihen, miten tiedot kannattaa järjestää. Seuraavassa on kolme sääntöä, joiden pitäminen mielessä voi auttaa tietokannan suunnittelussa.

Tietokannan muutokset

Sääntö: Tietokannan taulut ja niiden kentät ovat pysyvät.

Tietokannan suunnittelussa lyödään lukkoon, mitä tauluja siinä on ja mitä kenttiä tauluissa on. Tämän jälkeen ainoa muuttuva asia ovat tietokannan taulujen rivit. Kun tietokantaan lisätään tietoa, siihen ei saa siis tulla uusia tauluja eikä tauluihin saa tulla uusia kenttiä.

Esimerkiksi jos tietokanta sisältää keskustelupalstan viestit, ei ole järkevää perustaa jokaiselle viestiketjulle omaa taulua. Tällöin jos tietokannasta haluttaisiin hakea kaikki tietyn henkilön kirjoittamat viestit, jokaista viestiketjua kohden tarvittaisiin erillinen SELECT-kysely. Järkevä ratkaisu on tallentaa kaikki viestit samaan tauluun, jonka yksi kenttä kertoo, mihin ketjuun viesti kuuluu.

Kenttien sisältö

Sääntö: Yksi tietokannan kenttä saa sisältää vain yhden tiedon.

Tietokannan kenttä saa sisältää yksittäisen tiedon, kuten henkilön nimen, tuotteen hinnan, viestin sisällön tai päivämäärän. Sen sijaan kentässä ei saa olla listaa henkilöistä, tuotteista, viesteistä, päivämääristä tai muista erillisistä asioista.

Esimerkiksi jos tietokanta sisältää verkkokaupan asiakkaiden ostoskorien sisällöt, ei ole järkevää tallentaa asiakkaan kaikkia ostoksia yhdelle riville, joka sisältää merkkijonokentän muotoa "lanttu,purjo,selleri". Tällaisen kentän käsittely SQL-kyselyillä olisi vaivalloista. Järkevä ratkaisu on antaa jokaiselle asiakas-tuote-parille oma rivi, jolloin yhdessä kentässä on vain yksi tuote.

Toistuvat tiedot

Sääntö: Sama tieto saa esiintyä vain yhdessä paikassa tietokannassa.

Tietokannassa ei saa olla toistuvaa ja päällekkäistä tietoa. Jos samaa tietoa tarvitaan monessa paikassa, tieto on tallennettu vain yhteen paikkaan ja muualla on viittaus siihen.

Esimerkiksi keskustelupalstan tietokantaa ei ole järkevää toteuttaa niin, että jokaisen viestin yhteyteen tallennetaan käyttäjän nimimerkki. Tällöin jos käyttäjän nimimerkki vaihtuu, kaikki tietokannan rivit, joissa nimimerkki esiintyy, täytyy päivittää erikseen. Järkevä ratkaisu on tallentaa nimimerkit yhteen tauluun ja viitata siihen muualta, jolloin yhden rivin päivitys riittää.

Taulujen viittaukset

Tietokannan taulut ovat usein yhteydessä toisiinsa. Esimerkiksi keskustelupalstan tietokannassa voi olla taulut viesteille ja käyttäjille, minkä lisäksi kukin viesti täytyy yhdistää sen kirjoittajaan. Verkkokaupassa taas tuotteet ja asiakkaat voivat olla omissa tauluissaan, minkä lisäksi kolmas taulu voi sisältää tiedot asiakkaiden ostoskoreissa olevista tuotteista.

Taulun rivin viittaus toisen taulun riviin toteutetaan lisäämällä tauluun kenttä, johon tallennetaan avain, joka ilmoittaa toisen taulun rivin. Avain on taulun rivin yksilöivä tieto, eli jokaisella rivillä on eri avain. Käytännössä avain on yleensä kokonaislukumuotoinen tunnusnumero (kenttä id).

Seuraavaksi tarkastellaan kahta yleistä taulujen viittaussuhdetta. Opassarjan seuraavassa osassa nähdään, miten taulut laaditaan käytännössä ja millaisia SQL-kyselyjä niihin liittyy.

Yksi moneen -suhde

Tilanne: Verkkokaupassa on joka viikko tarjouksessa tietty tuote, jota esitellään nimellä "viikon tuote". Esimerkiksi viikolla 26 tarjouksessa on selleri ja viikolla 27 tarjouksessa on kaali. Sama tuote voi olla tarjouksessa monena eri viikkona.

Tarjouksia varten luodaan taulu tarjoukset, joka sisältää yhden rivin jokaista viikkoa kohden. Taulussa on kaksi kentää: viikon numero ja viittaus tauluun tuotteet eli jokin taulun tuotteet tunnusnumeroista. Viittaus sopii luontevasti tauluun tarjoukset, koska joka viikko vain yksi tuote on tarjouksessa.

Yllä olevassa tilanteessa viikolla 26 tarjouksessa on selleri, viikolla 27 tarjouksessa on kaali, viikolla 28 tarjouksessa on peruna, viikolla 29 tarjouksessa on selleri (toistamiseen) ja viikolla 30 tarjouksessa on nauris.

Monta moneen -suhde

Tilanne: Verkkokaupan tuotteet muodostavat tuotepaketteja. Esimerkiksi pakettiin "Isoäidin herkut" kuuluvat nauris, porkkana ja retiisi. Paitsi että paketissa voi olla monta tuotetta, sama tuote voi kuulua moneen pakettiin.

Tuotepaketteja varten luodaan taulu paketit, joka sisältää niiden nimet ja hinnat. Kuitenkaan taulujen tuotteet ja paketit välisille viittauksille ei tunnu löytyvän sopivaa paikkaa. Taulusta tuotteet ei voi viitata tauluun paketit, koska tuote voi kuulua moneen pakettiin. Taulusta paketit ei taas voi viitata tauluun tuotteet, koska pakettiin voi kuulua monta tuotetta.

Ratkaisu ongelmaan on luoda uusi taulu, joka sisältää vain taulujen tuotteet ja paketit viittaussuhteet. Tähän tauluun tulee kaksi kenttää, joista toisessa on tuotteen tunnusnumero ja toisessa on paketin tunnusnumero.

Yllä olevassa tilanteessa paketti "Isoäidin herkut" sisältää porkkanan, retiisin ja nauriin ja paketti "Voimapari" sisältää retiisin ja sellerin.