Deze blog is een onderdeel van de reeks: ‘Data structuur: de basis’

Verzamelen van de juiste data: Relationele Database

Een goede manier om je data te organiseren zodat je hier makkelijker me te kunnen werken is het opzetten van een relationele database structuur. Maar wat houdt dat eigenlijk precies in? In deze blog proberen we dat aan de hand van wat voorbeelden begrijpelijk te maken voor jou als ondernemer.

Een bedrijf heeft vaak verschillende databronnen zoals bijvoorbeeld klantdata en productdata. Het is daardoor gebruikelijk dat organisaties meerdere tabellen of Excelbestanden hebben waarin verschillende soorten gegevens worden opgeslagen. Om een meer breder beeld te krijgen van de analytische vraag die je probeert te beantwoorden, kan het nodig zijn om verschillende databronnen binnen jouw bedrijf te combineren. Een relationele database is een manier om deze verschillende databronnen op te slaan, te ordenen in tabellen en deze te koppelen op basis van onderliggende relaties. Met deze relaties kun je gemakkelijk data uit één of meer tabellen ophalen en combineren. Daarom kan een database worden gezien als een verzameling tabellen. Als je de logica achter deze relationele databases begrijpt, kan het je helpen om je eigen gegevens op de juiste manier te structureren. Even samengevat:

• Een database bevat één of meer tabellen met informatie;
• De rijen in een tabel worden records genoemd;
• De kolommen in een tabel worden velden of attributen genoemd;
• Een database die twee of meer gerelateerde tabellen bevat, wordt een relationele database genoemd.

In deze blog gaan we niet te veel in op het maken van een volledig functionerende relationele database, maar geven we eerder wat basiskennis en regels. Houd bij het verzamelen of organiseren van je data deze regels in gedachte. We zullen de regels in verschillende stappen uitleggen en eindigen met een praktijkvoorbeeld in Excel.

Stap 1. Begin met een schone en gestructureerde tabel met informatie

Stel je voor dat je verantwoordelijk bent voor het bijhouden van alle boeken die worden uitgeleend in een bibliotheek. Je vult in onderstaande Tabel 1 elke keer in wanneer iemand een boek leent om alle belangrijke informatie bij te houden.

Deze tabel voldoet aan de basisbehoefte om bij te houden wie welk boek heeft geleend, maar heeft wel een aantal ernstige tekortkomingen in termen van efficiëntie, benodigde ruimte en onderhoudstijd.

Problemen met deze tabel:

• Het steeds opnieuw in moeten voeren van bestaand informatie

Wanneer Bob na verloop van tijd meer boeken leent, moet je al zijn contactgegevens voor elk boek opnieuw invoeren. Dit is tijdverspilling, aangezien je zijn contactgegevens al eerder hebt ingevoerd. Daarnaast vergroot het ook de kans op (typ)fouten.

• Alle rijen moeten bij worden gewerkt als er een wijziging optreedt

Wanneer een update nodig is (bijv. Bob zijn telefoonnummer verandert), moeten alle records van Bob worden geüpdatet. Zodra één van de records van Bob een ander telefoonnummer heeft dan de rest, is het dan een wijziging, een record die over het hoofd is gezien tijdens de laatste update of een fout bij het invoeren van gegevens?

• Veel dubbele informatie

Voor elk boek wordt de auteur en het jaar waarin het is gepubliceerd ingevuld. Dit is dubbele informatie en vergroot opnieuw de kans op fouten.

Deze problemen kunnen worden verminderd door onze data te normaliseren – met andere woorden, de informatie in meerdere tabellen onder te verdelen met als doel “een plaats te hebben voor alles en alles op zijn plaats”. Elk stukje informatie hoeft maar één keer te verschijnen, waardoor het onderhouden van je data vereenvoudigd wordt en de benodigde opslagruimte afneemt.

Redenen voor normalisatie:

1. Minimaliseer dubbele gegevens
2. Minimaliseer of vermijd problemen met het wijzigen van gegevens
3. Vereenvoudig het extraheren van belangrijke gegevens

Stap 2. Creëer unieke rijen in je tabel, voorkom dubbele informatie

Om te voorkomen dat dubbele gegevens worden verzameld en er minder ruimte is voor fouten, zijn er enkele basisrichtlijnen die je kunt volgen:

Basisrichtlijnen

• Elke tabel heeft een primary key (primaire sleutel)

De primary key is een combinatie van één of meer kolommen waarvan de waarde uniek is voor iedere rij, en kunnen daardoor worden gebruikt om verschillende rijen te identificeren, bijv. bestelnummer, personeelsnummer, factuurnummer. De primary key is de kolom waarmee je verschillende informatietabellen correct kunt verbinden.

• Tabellen mogen geen subkolommen bevatten

Je kunt niet meerdere steden in één kolom weergeven en ze scheiden met een puntkomma. De waarde “Chicago” is bijvoorbeeld gewenst in één cel; terwijl “Chicago; Los Angeles; New York” dat niet is.

• Een tabel mag geen herhalende kolom-groepen bevatten

Voorbeelden zijn Customer1Name, Customer2Name en Customer3Name. Hierin wordt de kolom groep ‘klantnaam’ herhaald.

• Als een kolom dubbele informatie bevat, moet deze informatie in een nieuwe tabel worden opgenomen

Als het adres van een persoon meerdere keren in een kolom wordt herhaald, is het gevoelig voor fouten en moet het in een aparte tabel worden geregistreerd (klantgegevens).

Voorbeeld

Volgens de bovenstaande regels kunnen we de data uit Tabel 1 in twee tabellen op delen. In plaats van alles wat we weten over een klant te herhalen wanneer hij/zij een boek leent, geven we elke klant een uniek ID en alleen het ID wordt herhaald wanneer we die persoon willen linken aan een record in een andere tabel. Dit helpt om te laten zien welke records in de tabel Klant overeenkomen met welke records in de tabel Uitleen, met andere woorden, wie heeft welk boek geleend.

Nu kunnen de twee tabellen aan elkaar worden gelinkt op basis van de Klant ID. In de tabel Klant is het veld Klant ID de primary key en daarom moeten de waardes in deze kolom uniek blijven. De waarde “2” kan bijvoorbeeld slechts bij één rij voorkomen – die van Alicia – en Alicia kan slechts één klant-ID hebben – “2”.

Klant ID kan niet de primary key van de Uitleen tabel zijn, omdat Klant ID “1” meer dan eens voorkomt en dus niet uniek is. Logisch, want we willen dat de klanten meerdere boeken kunnen lenen. Als Klant ID de primary key was voor de Uitleen tabel, zou elke persoon slechts één boek mogen lenen en daarna mag die persoon nooit meer boeken worden uitgeleend.

We kunnen de titel van het boek niet de primary key maken, anders zouden we een soortgelijk probleem hebben: elk boek kan maar één keer worden geleend en daarna mag niemand het ooit nog eens lenen. We kunnen de inleverdatum ook niet als primary key gebruiken, anders kan er elke dag maar één boek geleend worden. Aangezien geen van de bestaande velden als primary key gebruikt kan worden, wordt een nieuw veld toegevoegd om elke record met de naam Uitleen ID te identificeren.

Stap 3. Kolommen met betrekking tot primaire sleutels

De primary key wordt gebruikt om elke rij in een tabel uniek te identificeren. Als we het hebben over kolommen die afhankelijk zijn van de primary key, bedoelen we dat iedere kolom in die tabel iets omschrijft over de primaire functie van die tabel. Denk hierbij bijvoorbeeld aan een Tabel genaamd “Werknemer” met primary key “Werknemer ID” het is belangrijk dat iedere kolom in die tabel iets zegt over die werknemer. Bijvoorbeeld de leeftijd van die werknemer, of het woonadres van die werknemer.

Zodra je het doel van een tabel hebt vastgesteld, kijkt je naar elk van de kolommen van de tabel en vraag je je af: “Beschrijft deze kolom wat de primary key identificeert?”

• Als je ‘ja’ antwoordt, is de kolom afhankelijk van de primary key en hoort deze in de tabel;
• Als je “nee” antwoordt, moet de kolom naar een andere tabel worden verplaatst.

Voorbeeld

Als we naar onze Uitleen tabel in Tabel 2 hierboven kijken, zien we dat er nog wat aanvullende informatie (auteur, jaar) wordt verzameld over een boek, terwijl dit niet het doel van de tabel Uitleen is. Het doel van de Uitleen tabel is puur en alleen het loggen van wie welk boek heeft geleend. Daarom maken we in Tabel 3 hieronder een nieuwe tabel met de naam Boek. Nu hebben we de optimale databasestructuur voor de gegeven dataset die aan alle richtlijnen voldoet. Deze structuur maakt snelle analyses mogelijk en minimaliseert de kans op fouten.

Stap 4. Hoe krijg je toch een duidelijk overzicht van al je data?

Diagram

Stel je voor dat er veel meer dan 3 tabellen zoals hierboven in je database staan, dan wil je waarschijnlijk een duidelijk overzicht krijgen van al deze tabellen en hoe ze gerelateerd zijn. We kunnen een diagram maken dat deze relaties illustreert en de primary (**) en foreign (*) keys toont. Foreign keys zijn primary keys van een andere tabel, zoals Klant ID in de Uitleen tabel. Dit is vooral handig als je veel kolommen in veel verschillende tabellen verzamelt.

Overzichtstabel

Hoewel Tabel 3 de beste databasestructuur visualiseert, is het niet mogelijk om in één oogopslag te zien welke klant welk boek heeft geleend in de Uitleen tabel. Mocht je dit belangrijk vinden,  is het niet per se een slechte zaak om één tabel te hebben die alle relevante informatie bevat. In plaats van deze informatie handmatig in te vullen, is het echter mogelijk om deze zodanig te koppelen aan de tabel Boek of Klant dat deze automatisch wordt bijgewerkt in alle huidige en eerdere velden wanneer er iets verandert in een van de onderliggende tabellen.

Excel Voorbeeld

Stel je voor dat de Klant tabel, de Boek tabel en de Uitleen tabel allemaal verschillende tabbladen zijn in Excel en dat je graag een leesbaar overzicht van de Uitleen tabel wilt hebben.

Zoals je kunt zien, wordt er een formule gebruikt om de naam van de klant en de boektitel te weergeven. Deze handige Excel formule wordt hieronder uitgelegd. Door de formule te gebruiken, kunnen we wijzigingen aanbrengen in de tabel Boek en de tabel Klant (in een apart tabblad in dezelfde file opgeslagen) die automatisch worden bijgewerkt in deze overzichtslijst. Als je echter veel gegevens hebt, kan dit een zeer tijdrovende werkwijze zijn. Je kunt dit dus het beste gebruiken als het geen invloed heeft op je kerntaken in Excel. Als het wel invloed heeft op je taken en je beschikt over een enorme hoeveelheid gegevens, is het misschien een idee om verder te kijken naar database beheersystemen zoals MySQl, PostgreSQL of MongoDB.

• Formule die wordt gebruikt: = VLOOKUP(B2; ’Klant Tabel’!$A$2:$E$4; 2; FALSE)
• Syntax: =VLOOKUP(lookup waarde, tabel, col_ kolomindex, [range_lookup])
  • lookup waarde – de waarde waarnaar moet worden gezocht in de eerste kolom (primary key) van een tabel
  • tabel – de tabel waaruit een waarde moet worden opgehaald
  • kolom_index – de kolom in de tabel waaruit een waarde moet worden opgehaald. In het bovenstaande voorbeeld geldt kolom A is 1, kolom B is 2, etc.
  • range_lookup – [optioneel] TRUE = geschatte match (standaard). FALSE = exacte match.

Wil jij eindelijk 1) je data op orde brengen om meer data gedreven te kunnen gaan werken en 2) minder tijd kwijt te zijn aan administratieve handelingen?

Wij helpen jou graag op weg, door samen het gesprek aan de gaan en te kijken wat een logische eerste stap voor jouw bedrijf naar een meer data gedreven organisatie. Stuur ons vrij blijvend een bericht via de contact pagina en dan plannen wij zo snel mogelijk een verkennend gesprek in met jullie om te kijken hoe we jullie kunnen helpen.

Meer weten over hoe je data kunt voorbereiden om te kunnen gaan analyseren? Lees dan deze blog.