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