Guide PHP- & MySQL-innføring: Kapittel 9

Databasenormalisering

Forrige gang lærte vi det mest grunnleggende om databaser. Nå skal vi sette opp databasene på best mulig måte.

Annonsør­innhold
Les hele saken »

Forrige gang lærte vi deg det aller mest grunneleggende om databaser, og hvordan de er oppbygd. I dag skal vi lære deg hvordan du bør strukturere databasetabellene dine for å gjøre de lette å vedlikeholde og bruke, gjennom en prosess kalt normalisering.

Normalisering består av en rekke såkalte normalformer, hvor hver normalform er et trinn på veien til å få en normalisert tabell. Nøyaktig på hvilken normalform man bør stoppe er noe omdiskutert, men man sier som oftest at data er normalisert om det er på tredje normalform eller høyere. En vanlig forkortelse for første normalform er 1NF, og vi kommer til å bruke slike forkortelser utover i artikkelen.

I dette kapittelet kommer vi til å presentere de tre første normalformene, som er de du kommer til å benytte til vanlig. Det finnes også flere normalformer, men disse er ikke relevante i majoriteten av tilfellene. Etter at vi har presentert de tre første normalformene, kommer vi til å gjennomgå vår løsning på dagens eksempel, og vi anbefaler at du utarbeider din egen løsning før du leser hva vi valgte å gjøre.

Dagens eksempel

Gjennom dette kapittelet bruker vi som eksempel lagring av en kontaktliste, og sammenhengen mellom dem, på en måte som et sosialt nettverk. Først og fremst ønsker vi å lagre en rekke ting om personer;

  • Navn
  • Et eller flere telefonnummer (markert jobb, hjemme, mobil)
  • En eller flere e-postadresser (markert jobb, hjemme, mobil)
  • Postadresse (hjemme)
  • Fødselsdato
  • Ektefelle
  • Barn
  • Venner og bekjente

Dette kan virke som ganske mye informasjon nå i begynnelsen. Ved å gjennomgå normaliseringen kommer vi ganske enkelt frem til en god lagringsstruktur for dataene.

I dette kapittelet kommer vi til å velge ut deler av disse dataene underveis, og gjøre normaliseringen på disse bitene av datasettet. Helt til slutt kommer vi til å presentere vår løsning på problemet, og det anbefales at du underveis forsøker å bruke teknikkene vi bruker på hele eksempelet. Da kan du sammenligne med vår løsning til slutt.

Første normalform

Første normalform (1NF) krever at hver rad i en tabell innehold er såkalt atomisk verdi; dvs. en verdi som ikke kan deles opp i flere verdier. Første normalform krever også at hver rad i tabellen unikt kan identifiseres gjennom en primærnøkkel.

Om vi setter opp en rad med noen av kolonnene fra eksempelet på forrige side direkte i en tabell;


Navn Telefon Fødselsdato
Ola Nordmann 12345678 (h),
23456789 (j),
98765432 (m)
12. januar 1978

Slik tenker nok de aller fleste når de skal lage sin første tabell, og i denne tabellen ville jeg gjerne også hatt med egne kolonner for postadresse, ektefelle, barn og venner om eksempelet skulle vært komplett.

Atomiske verdier

Første biten av første normalform sier at hver rad bør inneholde en atomisk verdi; dvs. at en verdi ikke kan representere flere verdier. Ovenfor ser du at Telefon-kolonnen har tre verdier for Olas telefonnummer, og det er ikke tillatt i 1NF, og vi er nødt til å splitte disse verdiene opp.



Navn Telefon Fødselsdato
Ola Nordmann 12345678 (h) 12. januar 1978
Ola Nordmann 23456789 (j) 12. januar 1978
Ola Nordmann 98765432 (m) 12. januar 1978

I stedet for å ha en enkelt rad som representerer Ola, har vi nå tre rader, bare for å representere telefonnummeret.

Unike rader

Sett at Ola jobber i lag med en navnebror som tilfeldigvis er født på samme dag, og jobbtelefonnummeret hans er det samme som Olas (selskapets sentralbord). Vi får nå problemer med å identifisere hvilke rader som hører til hvilken Ola;



Navn Telefon Fødselsdato
Ola Nordmann 12345678 (h) 12. januar 1978
Ola Nordmann 23456789 (j) 12. januar 1978
Ola Nordmann 98765432 (m) 12. januar 1978
Ola Nordmann 23456789 (j) 12. januar 1978
Ola Nordmann 97654321 (m) 12. januar 1978

For å løse dette problemet må vi lage en primærnøkkel; dvs. en nøkkel som unikt kan identifisere en rad i en tabell. Om du har et sett med verdier som du alltid vet er unike, kan du bruke dem; i Norge kunne fødselsnummeret til en person vært et godt valg når man lagrer personopplysninger. Det er derimot ikke tillatt å lagre til vanlig, og vi bruker derfor bare et unikt tall;


Id. Navn Telefon Fødselsdato
1 Ola Nordmann 12345678 (h) 12. januar 1978
2 Ola Nordmann 23456789 (j) 12. januar 1978
3 Ola Nordmann 98765432 (m) 12. januar 1978
4 Ola Nordmann 23456789 (j) 12. januar 1978
5 Ola Nordmann 97654321 (m) 12. januar 1978

Nå er det ikke lenger noe problem å finne frem til en enkelt rad i databasen, men du kan se at vi ikke kan se forskjell på hvilken Ola det er snakk om. Grunnen til dette ser vi når vi går videre i normalformene. I noen tilfeller kan primærnøkkelen også være en sammensetning av kolonner i tabellen, der du alltid vet at alle kombinasjoner er unike. Primærnøkkelen vi nå laget er en såkalt enkel primærnøkkel, mens en primærnøkkel som består av flere kolonner er en sammensatt primærnøkkel.

Andre normalform

Den andre normalformen (2NF) krever at alle data i en tabell er avhengige av hele primærnøkkelen. Om det er data i tabellen som bare delvis er avhengig av primærnøkkelen, må de splittes ut i separate tabeller.

Kravene for å nå andre normalform er altså at tabellen må være i første normalform, og at alle kolonner som ikke er nøkler er totalt avhengig av hele nøkkelen i tabellen.

Om vi ser på vår kontaktliste igjen, kunne en representasjon av en person og hans arbeidsplass vært følgende;


PersonID* ArbeidsgiverID* Navn Arbeidsgiver Kontor
1 23 Ola Nordmann Hardware Online AS 231

Vi har her oppgitt en arbeidsgiver-id, siden det gjerne er flere som jobber hos Hardware Online AS; og det er derfor naturlig at denne ID-en er en del av primærnøkkelen. En persons navn er kun avhengig av PersonID, mens navnet på arbeidsgiveren kun er avhengig ArbeidsgiverID. Kontoret er derimot avhengig av både ArbeidsgiverID og PersonID. Det gjør at det blir naturlig å dele denne opp i tre tabeller; en som angir personinformasjon, en som gir arbeidsgiverinformasjon, og en som setter opp en kobling mellom disse.


Person

PersonID* Navn
1 Ola Nordmann

Arbeidsgiver

ArbeidsgiverID* Navn
23 Hardware Online AS

Arbeidsforhold

PersonID* ArbeidsgiverID* Kontor
1 23 231

Som du kan se, har vi nå delt vår originale tabell i tre forskjellige tabeller som alle er i 2NF. Selve arbeidsforholdet mellom en person og en arbeidsgiver er nå i en separat tabell. Det gjør at vi faktisk har fått utvidede muligheter i forhold til hva vi originalt tenkte oss; vi kan nå la en person være ansatt i flere firmaer. Ved å sette inn en ekstra kolonne i Arbeidsforhold som vi kaller "Stilling", kan vi også oppgi hvilken stilling man har.

Derimot, siden Arbeidsforhold har en kombinert primærnøkkel på PersonID og ArbeidsgiverID, er det ikke mulig for en person å være ansatt med to forskjellige stillinger i en bedrift. Hvordan ville du fikset det?

Tredje normalform

Den tredje normalformen krever at alle data i en tabell ikke er avhengig av data som ikke er en del av en nøkkel.

Kravene for å nå tredje normalform er altså at tabellen må være i andre normalform, og at alle kolonner som ikke er nøkler ikke er avhengig av andre kolonner som heller ikke er nøkler.

Om vi ser på vår kontaktliste igjen, kunne en representasjon av postadressen til en person vært følgende;


PN Navn Gate Nr Postnummer Poststed
1 Ola Nordmann Blåklokkeveien 73 6150 Ørsta

I denne tabellen er det to avhengigheter som kan omfattes av tredje normalform; poststed er ikke avhengig av primærnøkkelen i tabellen, men av postnummeret (men postnummeret er avhengig av primærnøkkelen). Gatenummeret er avhengig av primærnøkkelen, men også av gatenavnet og postnummeret. Den er litt verre å se, men det er på grunn av at noen gater ikke har gitte gatenummer. Denne er derimot så obskur og vanskelig å implementere at den ignoreres, og det er ikke uvanlig å foreta slike forenklinger. I dette tilfellet vil det være praktisk umulig å gjøre noe med, og derfor ignorerer vi den.

Men vi er likevel nødt til å gjøre noe med koblingen mellom postnummer og poststed. Siden poststed er det eneste som er avhengig av postnummer, blir tabellen vi splitter disse to ut i svært liten;


Postnummer Poststed
6150 Ørsta

Vi kan bruke postnummeret som primærnøkkel for denne tabellen, siden postnummeret garantert er unikt. Den andre tabellen blir da seende slik ut;


PN Navn Gate Nr Postnummer
1 Ola Nordmann Blåklokkeveien 73 6150

Det eneste som har skjedd her, er at vi har fjernet poststed fra tabellen. Om vi hadde måttet introdusert en ny primærnøkkel i postnummer-tabellen vår, ville vi hatt den nye primærnøkkelen oppgitt i postnummer-feltet i denne tabellen i stedet. Og vi får også redusert datamengden det er snakk om, i alle fall om vi har mange innbyggere fra samme område i databasen. Posten Norge leverer en nedlastbar liste med postnummer og poststed som du kan bruke til å fylle den første tabellen med.

Som du kan se, har vi ikke tapt noen informasjon på å gå over fra andre til tredje normalform. Vi har faktisk spart lagringsplass i det lange løp, selv om tabellen som inneholder alle postnumrene kan bli ganske stor.

Før du går videre til neste side, anbefaler vi at du forsøker å utarbeide din egen løsning på dagens eksempel. Sett opp et databaseskjema, og legg inn litt eksempeldata for å se om databaseskjemaet gjør det du vil.

Løsning

Vår løsning inneholder ingenting utover det som er definert i eksempelet, selv om det gjerne er lett for å nettopp gå utover det som er definert i eksempelet. Eksempelet i seg selv er ikke særlig realistisk; og vi skal være de første til å innrømme at vår løsning heller ikke er perfekt. Vi skal diskutere manglene i denne løsningen etter hvert. Først til presentasjonen;


Tegningen over er et såkalt entitets-relasjonsdiagram laget i verktøyet MyDBExpert. Verktøyet er ærlig talt særdeles dårlig; men det ble skrevet av undertegnede for tre år siden, og jeg er dermed særdeles fortrolig med det. Om du skal lage tilsvarende diagrammer selv, bør du nok lete deg frem til et annet alternativ. Under finnes MySQL-koden for å opprette en slik database som over.



CREATE TABLE Epost (
  PersonID INT,
  Epost VARCHAR(255) NOT NULL,
  Sted ENUM('Hjemme', 'Jobb', 'Mobil') NOT NULL,
  PRIMARY KEY (PersonID, Epost)
); # Table Epost

CREATE TABLE Telefonummer (
  PersonID INT,
  Nummer CHAR(12),
  Sted ENUM('Hjemme', 'Jobb', 'Mobil') NOT NULL,
  PRIMARY KEY (PersonID, Nummer)
); # Table Telefonummer

CREATE TABLE Poststed (
  Postnummer INT(4),
  Poststed VARCHAR(255) NOT NULL,
  PRIMARY KEY (Postnummer)
); # Table Poststed

CREATE TABLE Person (
  PersonID INT,
  Navn VARCHAR(255) NOT NULL,
  Fodselsdato DATETIME NOT NULL,
  Gate VARCHAR(255) NOT NULL,
  Postnummer INT(4) NOT NULL,
  PRIMARY KEY (PersonID)
); # Table Person

CREATE TABLE PersonligForhold (
  PersonID1 INT NOT NULL,
  PersonID2 INT NOT NULL,
  Type ENUM('Ektefelle', 'Barn', 'Foreldre', 'Venn', 'Kollega') NOT NULL,
  PRIMARY KEY(PersonID1, PersonID2)
); # Table PersonligForhold

Person-tabellen er ganske enkel, og består av ting som kun er avhengig av denne personen. Den gir også en person en unik ID som man kan bruke for å referere til denne personen i etterkant. Vi har valgt å ha to enkle tabeller for Epost og Telefonnummer; det gjør det enklere for oss å søke etter et gitt telefonnummer. Et annet alternativ ville vært å splitte Telefonnummer-tabellen i tre ulike tabeller; HjemmeTelefon, JobbTelefon og MobilTelefon. Om vi da hadde et ukjent nummer ville vi da vært nødt til å søke i tre forskjellige tabeller. I vår løsning benytter vi i stedet oss av en spesiell datatype i MySQL; ENUM, som lar deg spesifisere nøyaktig hva som kan stå i den kolonnen.

Poststed og postnummer har vi også delt ut til en egen tabell; som vi beskrev når vi snakket om tredje normalform. Vi har tatt og sett på alle typer personlige forhold i en egen tabell. Vi angir to personer på hver rad, og sier hvilket forhold disse to har. Det gir oss veldig lett alle kontaktene til en gitt person; f.eks. om vi ønsker å slå opp alle personer som begge kjenner.

Svakheter

PersonligForhold-tabellen er også vår løsnings største svakhet. Her er det vi betrakter som de to største feilene;

  • Forhold går alltid to veier, så vi må enten legge inn to rader for hvert personlige forhold (med person 1 og 2 reversert i rad 2), eller gjøre at alle spørringer tar hensyn til at person 1 og 2 kan være byttet om.

    En normalt enkel spørring for å finne ektefellen til en person går dermed fra å være;


    SELECT p.navn 
      FROM Person AS p, PersonligForhold AS f 
      WHERE f.PersonID2 = p.PersonID AND f.PersonID1 = 23 
        AND f.Type = 'Ektefelle';

    ... til å bli;


    SELECT p.Navn 
      FROM Person AS p, PersonligForhold AS f 
      WHERE f.Type = 'Ektefelle' 
        AND (
          (f.PersonID2 = p.PersonID AND f.PersonID1 = 23) 
          OR (f.PersonID1 = p.PersonID AND f.PersonID2 = 23)
        );
  • En person kan bli gift med seg selv eller være sin egen beste venn. Tabellen forhindrer oss ikke å si at en person er sitt eget barn eller at en person har flere ektefeller. Dette kunne vært delvis forhindret ved å flytte ektefelleID som et felt i Person-tabellen, og si at alle personer har et ForeldreID som peker til en annen person. Likevel stopper ikke dette tilfeller der man er sin egen beste venn, eller å si at man er gift med seg selv. Dette er mulig å løse på databasenivået ved hjelp av såkalte restriksjoner, men dette er langt utenfor hva vi kommer til å dekke i denne artikkelserien.

Oppsummering

Normalisering av en database gjør dataene enklere å jobbe med, og forhindrer dobbellagring av data. Første normalform krever at alle rader ikke kan deles opp mer; og at hver rad kan identifiseres ved hjelp av en primærnøkkel.

Andre normalform krever at alle ikke-nøkler i en tabell er avhengig av hele nøkkelen, og ikke bare deler av den, og at tabellen er på første normalform. Tredje normalform krever at alle data i en tabell ikke er avhengig av data som ikke er en del av en nøkkel.

Om du normaliserer dine data før du lager databasekode, vil du spare mye tid og gjøre datasettet ditt enklere å behandle.

Neste kapittel

Neste kapittel handler om hvordan man kobler PHP sammen med MySQL, og lærer deg å hente ut, oppdatere og sette inn data i databasen fra PHP.

Alle kapitlene som PDF!

Vi har kombinert alle kapitlene som finnes til en e-bok, som du kan laste ned herfra i PDF-format. E-boken inneholder en lettere modifisert versjon av de ulike kapitlene, med noen få omskrivinger for å passe bedre i trykt form. Du kan laste den ned gratis, og skrive den ut, eller bare lese den på skjerm om du ønsker det.

PDF-versjonen er på hele 87 sider, og er en 1,4 MB stor nedlasting. Vi gjør oppmerksom på at dette er et prøveprosjekt, og du er velkommen til å kommentere hva du mener om PDF-versjonen ovenfor artikkelforfatter.

Norges beste mobilabonnement

Desember 2018

Kåret av Tek-redaksjonen

Jeg bruker lite data:

Komplett MiniFlex 1 GB


Jeg bruker middels mye data:

Komplett MedioFlex+ 6GB


Jeg bruker mye data:

Chili 25 GB


Jeg er superbruker:

Chili Fri Data


Finn billigste abonnement i vår mobilkalkulator

Forsiden akkurat nå

Til toppen