Handleiding: Datamodellen Ontwerpen

1

Conceptueel, logisch & fysiek model

Drie abstractieniveaus, doelgroep per niveau

Datamodelleren verloopt via drie abstractieniveaus. Elk niveau heeft een andere doelgroep en een ander detailniveau.

Niveau	Doel	Doelgroep	Bevat
Conceptueel	Begrijpen wat de business nodig heeft	Business stakeholders	Entiteiten, relaties (geen datatypes)
Logisch	Structuur onafhankelijk van database	Data architect	Attributen, datatypes, sleutels, normalisatievorm
Fysiek	Implementatie in specifiek database systeem	Data engineer / DBA	DDL statements, indexen, partitionering

Stappen in het modelleringsproces

Requirementsanalyse: Welke vragen moet het model kunnen beantwoorden?
Conceptueel model: Entiteiten en relaties in een ERD
Logisch model: Attributen, sleutels en normalisatie toevoegen
Fysiek model: DDL schrijven voor het gekozen platform
Review: Met business en engineering valideren

✅ Gebruik dbdiagram.io (gratis) of ERDPlus voor het tekenen van ERD's. Voor enterprise modellering: erwin Data Modeler of PowerDesigner.

2

Entiteiten, attributen & relaties

ERD lezen, kardinaliteit, primaire en vreemde sleutels

Een Entity Relationship Diagram (ERD) is de visuele weergave van een datamodel. Begrijpen hoe je een ERD leest is de basis voor alle verdere modellering.

Kernconcepten

Concept	Definitie	Voorbeeld
Entiteit	Een ding waarover je data opslaat	Klant, Order, Product
Attribuut	Eigenschap van een entiteit	klant_naam, order_datum
Primaire sleutel (PK)	Uniek identificerend attribuut	klant_id, order_id
Vreemde sleutel (FK)	Verwijzing naar PK van andere entiteit	klant_id in Order tabel
Relatie	Verband tussen twee entiteiten	Klant plaatst Order

Kardinaliteit

Een-op-een (1:1): Één klant heeft één paspoort
Een-op-veel (1:N): Één klant plaatst meerdere orders
Veel-op-veel (M:N): Een order bevat meerdere producten; een product staat in meerdere orders

sql — relaties als ddl

>-- Een-op-veel: klant → orders
CREATE TABLE klant (
  klant_id    INTEGER     PRIMARY KEY,
  naam        VARCHAR(200) NOT NULL
);

CREATE TABLE order_hoofd (
  order_id    INTEGER     PRIMARY KEY,
  klant_id    INTEGER     NOT NULL REFERENCES klant(klant_id),
  order_datum DATE        NOT NULL
);

-- Veel-op-veel: order ↔ product via koppeltabel
CREATE TABLE order_regel (
  order_id    INTEGER     REFERENCES order_hoofd(order_id),
  product_id  INTEGER     REFERENCES product(product_id),
  aantal      INTEGER     NOT NULL CHECK (aantal > 0),
  stukprijs   DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

3

Normalisatie (1NF, 2NF, 3NF)

Redundantie verwijderen, anomalieën voorkomen

Normalisatie is het systematisch organiseren van data om redundantie en update-anomalieën te voorkomen. Voor OLTP systemen streef je naar minimaal de derde normaalvorm (3NF).

1NF — Eerste Normaalvorm

Elke cel bevat één waarde (atomaire waarden). Geen herhalende groepen.

sql — schending en fix van 1nf

>-- SLECHT: meerdere telefoonnummers in één kolom
CREATE TABLE klant_slecht (
  klant_id  INTEGER,
  naam      VARCHAR(200),
  telefoons VARCHAR(500)  -- "0612345678, 0698765432" ← niet atomair!
);

-- GOED: aparte tabel voor telefoonnummers (1NF)
CREATE TABLE klant (
  klant_id  INTEGER PRIMARY KEY,
  naam      VARCHAR(200) NOT NULL
);

CREATE TABLE klant_telefoon (
  klant_id  INTEGER REFERENCES klant(klant_id),
  telefoon  VARCHAR(20) NOT NULL,
  type      VARCHAR(10),  -- 'mobiel', 'thuis', 'werk'
  PRIMARY KEY (klant_id, telefoon)
);

2NF — Tweede Normaalvorm

Voldoet aan 1NF + alle niet-sleutelattributen zijn volledig afhankelijk van de volledige primaire sleutel (geen partiële afhankelijkheden).

sql — schending en fix van 2nf

>-- SLECHT: product_naam hangt alleen af van product_id (niet van order_id)
CREATE TABLE order_regel_slecht (
  order_id      INTEGER,
  product_id    INTEGER,
  product_naam  VARCHAR(200),   -- ← partiële afhankelijkheid!
  aantal        INTEGER,
  PRIMARY KEY (order_id, product_id)
);

-- GOED: product_naam naar eigen tabel (2NF)
CREATE TABLE product (
  product_id    INTEGER PRIMARY KEY,
  product_naam  VARCHAR(200) NOT NULL
);

CREATE TABLE order_regel (
  order_id      INTEGER REFERENCES order_hoofd(order_id),
  product_id    INTEGER REFERENCES product(product_id),
  aantal        INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

3NF — Derde Normaalvorm

Voldoet aan 2NF + geen transitieve afhankelijkheden (niet-sleutelattribuut mag niet afhangen van een ander niet-sleutelattribuut).

sql — schending en fix van 3nf

>-- SLECHT: postcode bepaalt stad (transitieve afhankelijkheid)
CREATE TABLE klant_slecht (
  klant_id  INTEGER PRIMARY KEY,
  naam      VARCHAR(200),
  postcode  VARCHAR(10),
  stad      VARCHAR(100)   -- hangt af van postcode, niet klant_id!
);

-- GOED: postcode tabel (3NF)
CREATE TABLE postcode (
  postcode  VARCHAR(10)  PRIMARY KEY,
  stad      VARCHAR(100) NOT NULL
);

CREATE TABLE klant (
  klant_id  INTEGER PRIMARY KEY,
  naam      VARCHAR(200) NOT NULL,
  postcode  VARCHAR(10)  REFERENCES postcode(postcode)
);

4

Ster-schema (Kimball methodologie)

Feitentabellen, dimensies, surrogate keys, conformed dimensions

Het ster-schema (Kimball) is de standaard voor datawarehouse modellering. Het is bewust gedenormaliseerd voor queryperformance en begrijpelijkheid.

sql — compleet ster-schema voorbeeld

>-- Datum dimensie (conformed: gedeeld door alle feiten)
CREATE TABLE dim_datum (
  datum_sk        INTEGER PRIMARY KEY,      -- surrogate key: YYYYMMDD
  datum           DATE NOT NULL UNIQUE,
  dag_van_maand   SMALLINT NOT NULL,
  dag_naam        VARCHAR(20) NOT NULL,
  week_nummer     SMALLINT NOT NULL,
  maand_nummer    SMALLINT NOT NULL,
  maand_naam      VARCHAR(20) NOT NULL,
  kwartaal        SMALLINT NOT NULL,
  jaar            SMALLINT NOT NULL,
  is_weekend      BOOLEAN NOT NULL,
  is_feestdag     BOOLEAN NOT NULL DEFAULT FALSE
);

-- Klant dimensie
CREATE TABLE dim_klant (
  klant_sk        INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  klant_id        VARCHAR(50) NOT NULL,   -- business key
  klant_naam      VARCHAR(200) NOT NULL,
  segment         VARCHAR(50),
  land            CHAR(2),
  stad            VARCHAR(100),
  is_actief       BOOLEAN DEFAULT TRUE
);

-- Product dimensie
CREATE TABLE dim_product (
  product_sk      INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  product_id      VARCHAR(50) NOT NULL,
  product_naam    VARCHAR(200) NOT NULL,
  categorie       VARCHAR(100),
  subcategorie    VARCHAR(100),
  merk            VARCHAR(100),
  kostprijs       DECIMAL(18,2)
);

-- Omzet feitentabel
CREATE TABLE fct_omzet (
  datum_sk        INTEGER NOT NULL REFERENCES dim_datum(datum_sk),
  klant_sk        INTEGER NOT NULL REFERENCES dim_klant(klant_sk),
  product_sk      INTEGER NOT NULL REFERENCES dim_product(product_sk),

  -- Business key voor traceerbaarheid
  order_id        VARCHAR(50) NOT NULL,

  -- Feiten (meetbare waarden)
  aantal          INTEGER NOT NULL,
  verkoopprijs    DECIMAL(18,2) NOT NULL,
  korting         DECIMAL(18,2) DEFAULT 0,
  omzet           DECIMAL(18,2) NOT NULL,
  marge           DECIMAL(18,2),

  CONSTRAINT pk_fct_omzet PRIMARY KEY (order_id, product_sk)
);

✅ Surrogate keys: Gebruik altijd integer surrogate keys (gegenereerd door de database) als primaire sleutel in dimensietabellen, nooit de business key. Dit maakt SCD Type 2 mogelijk en isoleert het warehouse van bronwijzigingen.

5

Sneeuwvlokschema

Genormaliseerde dimensies, voor- en nadelen

Het sneeuwvlokschema is een variant van het ster-schema waarbij dimensies verder zijn genormaliseerd. Een productdimensie kan bijvoorbeeld worden opgesplitst in product, categorie en merk.

sql — sneeuwvlok: product opgesplitst

>-- Categorie als aparte tabel
CREATE TABLE dim_categorie (
  categorie_sk    INTEGER PRIMARY KEY,
  categorie_naam  VARCHAR(100) NOT NULL,
  afdeling        VARCHAR(100)
);

-- Product verwijst naar categorie
CREATE TABLE dim_product_sneeuwvlok (
  product_sk    INTEGER PRIMARY KEY,
  product_id    VARCHAR(50),
  product_naam  VARCHAR(200),
  categorie_sk  INTEGER REFERENCES dim_categorie(categorie_sk),
  merk          VARCHAR(100),
  kostprijs     DECIMAL(18,2)
);

-- Query vereist nu extra join
SELECT c.categorie_naam, SUM(f.omzet)
FROM fct_omzet f
JOIN dim_product_sneeuwvlok p ON f.product_sk = p.product_sk
JOIN dim_categorie c          ON p.categorie_sk = c.categorie_sk
GROUP BY c.categorie_naam;

⚠️ Gebruik sneeuwvlokschema alleen als dimensies erg groot zijn (miljoenen rijen) of als categoriehiërachieën diep zijn (meer dan 3 niveaus). De extra joins vertragen queries. Voor BI-rapportage is het ster-schema vrijwel altijd beter.

6

Slowly Changing Dimensions (SCD)

Type 1 (overschrijven), Type 2 (historiek), Type 3 (kolom)

Dimensiedata verandert over tijd (klant verhuist, product krijgt nieuwe categorie). SCD types bepalen hoe je omgaat met deze wijzigingen.

SCD Type 1 — Overschrijven (geen historiek)

sql — scd type 1: update

>-- Eenvoudig: update de bestaande rij, geen historiek bewaard
UPDATE dim_klant
SET
  stad       = 'Amsterdam',
  postcode   = '1012AB',
  updated_at = CURRENT_TIMESTAMP
WHERE
  klant_id = 'K001';

SCD Type 2 — Historiek bewaren (nieuwe rij)

sql — scd type 2: structuur en merge

>-- Type 2 kolommen toevoegen aan dim_klant
ALTER TABLE dim_klant
  ADD COLUMN valid_from  DATE NOT NULL DEFAULT CURRENT_DATE,
  ADD COLUMN valid_to    DATE,                -- NULL = huidig actief record
  ADD COLUMN is_current  BOOLEAN DEFAULT TRUE;

-- Type 2 update: sluit oud record + voeg nieuw toe
BEGIN;

  -- 1. Sluit het huidige record
  UPDATE dim_klant
  SET
    valid_to   = CURRENT_DATE - 1,
    is_current = FALSE
  WHERE
    klant_id   = 'K001'
    AND is_current = TRUE;

  -- 2. Voeg nieuwe versie toe
  INSERT INTO dim_klant
    (klant_id, klant_naam, stad, segment, valid_from, valid_to, is_current)
  VALUES
    ('K001', 'Jan de Vries', 'Amsterdam', 'Gold',
     CURRENT_DATE, NULL, TRUE);

COMMIT;

-- Actieve records opvragen:
SELECT *
FROM   dim_klant
WHERE  is_current = TRUE;

SCD Type 3 — Vorige waarde bewaren (extra kolom)

sql — scd type 3

>-- Vorige waarde als aparte kolom opslaan
CREATE TABLE dim_klant_type3 (
  klant_sk            INTEGER PRIMARY KEY,
  klant_id            VARCHAR(50),
  huidig_segment      VARCHAR(50),
  vorig_segment       VARCHAR(50),     -- vorige waarde
  segment_gewijzigd   DATE             -- datum laatste wijziging
);

-- Update Type 3:
UPDATE dim_klant_type3
SET
  vorig_segment     = huidig_segment,
  huidig_segment    = 'Gold',
  segment_gewijzigd = CURRENT_DATE
WHERE
  klant_id = 'K001';

ℹ️ Keuze: Type 1 voor correcties van fouten (geen historiek nodig). Type 2 voor business-relevante wijzigingen (segment, adres) waar historische analyse vereist is. Type 3 alleen als je exact één vorige versie nodig hebt.

7

Data Vault 2.0

Hubs, Links, Satellites — agiel en auditeerbaar

Data Vault 2.0 is een alternatief modelleringsparadigma voor de Raw Vault (historische opslag). Het is zeer flexibel bij bronwijzigingen en volledig auditeerbaar.

Component	Doel	Bevat
Hub	Business sleutels opslaan	hash_key, business_key, load_date, record_source
Link	Relaties tussen Hubs	hash_key, hub_key_A, hub_key_B, load_date, record_source
Satellite	Beschrijvende attributen + historiek	hub_key, load_date, attributes, hash_diff

sql — data vault 2.0 structuur

>-- Hub: unieke business sleutels voor Klant
CREATE TABLE hub_klant (
  klant_hk        CHAR(32)     PRIMARY KEY,    -- MD5/SHA1 hash van business key
  klant_bk        VARCHAR(50)  NOT NULL,        -- business key
  load_date       TIMESTAMP    NOT NULL,
  record_source   VARCHAR(100) NOT NULL
);

-- Hub: unieke business sleutels voor Order
CREATE TABLE hub_order (
  order_hk        CHAR(32)     PRIMARY KEY,
  order_bk        VARCHAR(50)  NOT NULL,
  load_date       TIMESTAMP    NOT NULL,
  record_source   VARCHAR(100) NOT NULL
);

-- Link: relatie klant ↔ order
CREATE TABLE lnk_klant_order (
  klant_order_hk  CHAR(32)     PRIMARY KEY,    -- hash van klant_hk + order_hk
  klant_hk        CHAR(32)     REFERENCES hub_klant(klant_hk),
  order_hk        CHAR(32)     REFERENCES hub_order(order_hk),
  load_date       TIMESTAMP    NOT NULL,
  record_source   VARCHAR(100) NOT NULL
);

-- Satellite: attributen van Klant (met historiek)
CREATE TABLE sat_klant (
  klant_hk        CHAR(32)     REFERENCES hub_klant(klant_hk),
  load_date       TIMESTAMP    NOT NULL,
  load_end_date   TIMESTAMP,                   -- NULL = huidig
  hash_diff       CHAR(32)     NOT NULL,        -- hash van alle attributen
  record_source   VARCHAR(100) NOT NULL,
  klant_naam      VARCHAR(200),
  email           VARCHAR(254),
  segment         VARCHAR(50),
  stad            VARCHAR(100),
  PRIMARY KEY     (klant_hk, load_date)
);

✅ Data Vault is het meest waardevol in grote enterprise omgevingen met veel bronnen, frequente bronwijzigingen en strikte auditverplichtingen. Voor kleinere omgevingen is het Kimball ster-schema eenvoudiger en snel genoeg.

8

Naming conventions & best practices

Consistentie, leesbaarheid, documentatie

Consistente naamgeving is essentieel voor een onderhoudbaar datamodel. Stel afspraken vast voordat het project begint en leg ze vast in een Data Dictionary.

Element	Conventie	Voorbeeld
Tabelnaam	snake_case, enkelvoud	dim_klant, fct_omzet
Kolomnaam	snake_case, beschrijvend	klant_naam, order_datum
Primary Key	[tabel]_sk (surrogate) of [tabel]_id (business)	klant_sk, order_id
Foreign Key	[referentietabel]_sk	klant_sk, product_sk
Boolean	is_ of has_ prefix	is_actief, has_korting
Datum (date)	_datum of _date suffix	order_datum, geboorte_datum
Timestamp	_at suffix	created_at, updated_at
Bedrag	_bedrag of _amount suffix	omzet_bedrag, korting_amount

Best practices samengevat

Documenteer elk model in een Data Dictionary (tabel, kolom, beschrijving, eigenaar)
Gebruik constraint namen (pk_, fk_, uq_, chk_)
Voeg altijd created_at en updated_at toe voor auditing
Voer een peer review uit op elk nieuw datamodel met zowel business als engineering
Versiebeheer: sla DDL scripts op in Git, gebruik database migratie tools (Flyway, Liquibase)

ℹ️ Gebruik Flyway of Liquibase voor schema migraties. Zo is elke schemawijziging traceerbaar, herhaalbaar en kan worden teruggedraaid. Sla migratiescripts op in dezelfde Git repository als je applicatiecode.

9

Checklist

Controleer je datamodel voor implementatie

Model Ontwerp

Conceptueel model goedgekeurd door business stakeholders
Logisch model doorloopt minimaal 3NF (OLTP) of ster-schema (OLAP)
Kardinaliteit van alle relaties gedocumenteerd
SCD strategie bepaald per dimensietabel

SQL & DDL

Alle tabellen hebben een primaire sleutel
Foreign key constraints aangemaakt (of gedocumenteerd als logisch)
Geen FLOAT voor geldbedragen (gebruik DECIMAL)
created_at en updated_at aanwezig op alle tabellen

Naamgeving

Consistent snake_case voor alle namen
Prefix conventies gevolgd (fct_, dim_, stg_)
Constraint namen zijn beschrijvend (pk_, fk_, uq_)
Data Dictionary bijgewerkt voor alle nieuwe tabellen

Versiebeheer

DDL scripts opgeslagen in Git
Migratie tool geconfigureerd (Flyway/Liquibase)
Model gereviewed door minstens één collega
Rollback plan klaar voor elke schemawijziging

Datamodellen Ontwerpen

Inhoud