The strength of a database is directly proportional to the power of the questions you can ask of it.
—Hans Rosling

Fysiek datamodel

In dit hoofdstuk maken we de laatste stap bij het plannen van onze databank: het creëren van ons fysiek datamodel. Hoe zet je een logisch datamodel om naar een fysiek datamodel?

Jullie moeten de volgende termen kunnen uitleggen, hoe we dit weergeven (waar relevant) en toelichten aan de hand van een voorbeeld:

Inleiding

De laatste stap in het uittekenen van ons datamodel, is de fysieke laag. Wanneer we aan deze stap komen, hebben we al een databankmodel gekozen en het conceptueel datamodel omgezet naar een structuur die voldoet aan dit databankmodel, resulterend in het logisch datamodel.

De fysieke laag van ons datamodel beantwoordt de vraag ‘Hoe moeten we de datastructuur beschrijven volgens ons gekozen DBMS?’. Daarbij vullen we ons datamodel aan met de volgende informatie:

• het datatype van een kolom
• de lengte van een waarde
• het al dan niet leeg (null) zijn van een waarde
• beperkingen (constraints) die we aan een bepaalde tabel willen opleggen (wordt niet besproken in dit vak)
• indexing (wordt niet besproken in dit vak)
• views (wordt niet besproken in dit vak)
• procedures (wordt niet besproken in dit vak)
• gebruik van diskspace (wordt niet besproken in dit vak)

De reden waarom we deze informatie toevoegen in een aparte laag, is omdat het gaat om de meer technische aspecten van het datamodel. Voor hetzelfde databankmodel, kunnen er verschillen bestaan op niveau van het DBMS. Met andere woorden, een fysiek datamodel voor het ene DBMS kan verschillen van dat van een ander DBMS systeem.

De verschillen zijn veelal beperkt, zeker voor DBMS voor databanken volgens het relationeel databankmodel, omdat er standaarden werden vastgelegd. Voor het relationeel databankmodel werden standaarden samengevat in de ANSI standaard voor SQL, voor het eerst in 1986 en meer recent in 2016. De ontwikkelaars van DBMS voor relationele databanken houden zich meestal aan deze standaarden, al voegen ze ook eigen varianten toe om zich te onderscheiden van andere producten op de markt door hun klanten net iets meer aan te bieden. De focus in dit vak ligt bij PostgreSQL, we maken ook geen vergelijking met andere systemen binnen dit vak.

Tenslotte kan een database-architect ook beslissen de data net iets anders te structureren (bijvoorbeeld denormaliseren) dan in het logisch datamodel werd aangegeven. De architect zal dit doen om de performantie of bruikbaarheid van de databank te optimaliseren, vaak met het in overweging nemen van de technische mogelijkheden van het gekozen DBMS. Deze overwegingen komen in een later vak aan bod.

Van logisch naar fysiek datamodel

Bij de vertaling van een logisch datamodel naar een fysiek datamodel, gaan we in het kader van dit vak de volgende informatie toevoegen:

• we geven de verschillende namen van tabellen en kolommen in lijn met onze afgesproken naamgevingsconventies (naming conventions), daarvoor moeten we eerst naamgevingsconventies vastleggen;
• we bepalen voor elk van de kolommen een datatype en lengte;
• we geven voor elk van de kolommen aan of de waarde in de kolom al dan niet leeg (NULL) kan zijn;
• we leggen beperkingen (constraints) aan een tabel op.

Naamgevingsconventies (Naming conventions)

In het fysiek datamodel speelt naamgeving (naming conventions) van tabellen en kolommen een grote rol. Je wil vooral naar een naamgeving streven die leesbaar, duidelijk en consistent is. Daarbij laten heel wat DBMS slechts een beperkt aantal karakters toe in de naam van tabellen en kolommen. Je wordt dus vaak gedwongen om het kort te houden.

Er zijn verschillende manieren om regels te bepalen voor naamgeving, en elk bedrijf zal zijn eigen regels gebruiken. Enkele ideeën:

• case van de namen en/of scheiden van woorden:
  • UPPER CASE,
  • lower case,
  • camelCase,
  • PascalCase,
  • snake_case,
  • kebab-case: woorden worden van elkaar gescheiden
• gebruik van meervoud of enkelvoud (tabel ‘spelers’ of ‘speler’?)
• woorden scheiden van elkaar:
  • door een underscore (_),
  • door een punt,
  • door een hoofdletter,
  • door een combinatie van bovenstaande.
• taal (in een internationale omgeving werk je best altijd in het Engels)
• gebruik van afkortingen om bijvoorbeeld tabellen, primaire of vreemde sleutels aan te duiden. Je kan ook afkorting aanduiden om bepaalde domeinen binnen het datamodel af te bakenen.
• regels om lange woorden af te korten door bijvoorbeeld alle klinkers weg te laten, tenzij het woord start met een klinker.

Daarnaast kunnen we zeker al de volgende tips meegeven:

• gebruik geen spaties;
• gebruik geen vreemde karakters;
• gebruik geen SQL of DBMS gereserveerde key-words.

Zelf passen we de volgende naamgevingsconventies toe:

• tabel: we gebruiken een naam in enkelvoud die in lowercase wordt gezet. Verschillende woorden worden van elkaar gescheiden door een ‘_’.
• kolom: de kolomnaam wordt in lowercase beschreven, met verschillende woorden van elkaar gescheiden door een ‘_’. Probeer de kolomnaam zo kort mogelijk te houden, maar tegelijk wel zo dat het nog mogelijk is om te achterhalen wat het betekent.
• primaire sleutel: indien mogelijk, geven we de naam van de sleutel ‘id’. Zo is het altijd makkelijk voor de gebruikers om de primaire sleutel te identificeren.
• vreemde sleutel: vreemde sleutels zijn verwijzingen naar andere tabellen, dus trachten we de naam van de oorspronkelijke tabel mee in de benaming op te nemen. Bijvoorbeeld de primaire sleutel ‘id’ uit de tabel ‘lector’ zou in andere tabellen als vreemde sleutel ‘lector_id’ genoemd kunnen worden.

Zoals gezegd, je kan zelf een naamgeving van tabellen bepalen, en in jouw toekomstige werkomgeving zal dit deels al gedaan zijn. Het belangrijkste is dat je consistent blijft!

Datatype

In het fysiek datamodel gaan we voor elke kolom het datatype vastleggen. We houden hier rekening met de aard van de data die we in deze kolom willen toevoegen. In de eerste stappen die jullie gezet hebben met SQL, binnen het hoofdstuk ‘Intro SQL’, zijn jullie al in aanraking gekomen met de meest gebruikte datatypes: char(), varchar(), integer en date.

Naast deze datatypes biedt PostgreSQL nog een brede waaier aan bijkomende datatypes. Elk datatype heeft een bepaalde rol en biedt via het gebruik van functies ook nog eens bijkomende mogelijkheden.

Bij het bepalen van het datatype moet je dan ook bij sommige kolommen de lengte aangeven. Datatypes kunnen een vaste lengte hebben (bijvoorbeeld integer 4 bytes, boolean 1 bit, real 4 bytes, …) of een variabele lengte hebben (bijvoorbeeld char, varchar, numeric, …).

Wanneer je de lengte van een kolom aangeeft, moet je rekening houden met de maximale lengte die een waarde zou kunnen hebben. Een datatype met een variabele lengte laat toe om voor bepaalde kolommen toch heel wat ruimte beschikbaar te stellen, zonder dat die ruimte ook telkens volledig ingenomen wordt. Een goede richtlijn is om dit realistisch te houden.

NULL of NOT NULL

Bij het fysiek ontwerp moeten we aangeven of de waarde in een kolom al dan niet leeg kan zijn. Voor de kolommen die nooit leeg mogen zijn, kunnen we ‘NOT NULL’ specificeren. Voor de tabellen waar we dit niet hebben aangegeven, kan de waarde dus wel leeg zijn.

De waarde die we hier moeten invullen op basis van het logisch datamodel, wordt door twee elementen bepaald:

• Als eerste element hebben we de vreemde sleutels. Afhankelijk van de relatie die er bestaat tussen twee tabellen door middel van een primaire en een vreemde sleutel, moeten we op basis van de minimumkardinaliteit evalueren of de waarde NULL kan zijn. Indien de relatie afgedwongen wordt, zoals bijvoorbeeld bij het gebruik van een tussentabel, moeten we aangeven dat de vreemde sleutel ‘NOT NULL’ is.
• Als tweede element moeten we voor elk van de kolommen (buiten de primaire en vreemde sleutels) evalueren of het vanuit de logica van de klant toegelaten is dat een kolom leeg is . Bijvoorbeeld, de naam en voornaam van een werknemer worden niet gebruikt als sleutel, aangezien deze niet noodzakelijk uniek zijn. Maar een werknemer waarvan voor- en achternaam niet ingevuld zijn, is mogelijk niet erg bruikbaar. Vandaar kunnen we bijkomend aangeven dat deze niet leeg mogen zijn.

De waarde NULL kan twee dingen betekenen: enerzijds kan het betekenen dat er een waarde ingevuld zou kunnen zijn, maar dat we deze niet kennen. De waarde zou dan op een later tijdstip ingevuld kunnen worden. Anderzijds zou NULL kunnen aangeven dat voor dit specifiek geval er geen waarde mogelijk is, de kolom is niet van toepassing. Een vergelijking met een NULL waarde geeft zelf ook een NULL waarde, omdat een waarde vergelijken met iets onbekend, zelf ook onbekend is.