Hoe kan ik een MySQL database normaliseren? Eén van de doelen van het normaliseren van een MySQL database is om dubbele gegevens te voorkomen. Gegevens worden verdeeld over meerdere, aan elkaar gerelateerde databasetabellen en komen bij voorkeur maar één keer voor. Jouw MySQL-database is hierdoor kleiner, duidelijker, eenvoudiger in het beheer én sneller toegankelijk.
Houd je MySQL database in topconditie door te normaliseren!
Met enige regelmaat ontvangen we op de klantenservice van mijn werkgever een melding van een klant, in de strekking van:
Help, mijn MySQL database is traag!
Als wij een dergelijke melding onderzoeken blijkt regelmatig dat de database al jaren in gebruik is, en door de jaren heen gestaag is gegroeid naar een behoorlijke omvang. Als een MysQL database niet, of niet juist, genormaliseerd is, doen MySQL-database operaties (zoals queries) er vaak erg lang over.
Dit geeft lange wachttijden door trage queries, of zelfs foutmeldingen doordat andere queries opgehouden worden.Hierom is normaliseren van MySQL databases een erg belangrijk onderdeel van MySQL optimalisatie.
MySQL kent operaties die verschillende benamingen hebben: query en statement. Vaak wordt simpelweg àlles een query genoemd. Het vereenvoudigde verschil is makkelijk te onthouden: Een query is een vraag aan de database en beperkt zich tot SELECT. Al het overige (UPDATE, INSERT, …) zijn operaties en heten statements.
Leer alle ins en outs van MySQL met het Head First PHP and MySQL boek
MySQL-database normaliseren – een voorbeeld
Stel, een webdesignbedrijf heeft de volgende MySQL-database (ehh… voor deze post alleen een tabel ;-) ) met fictieve klantgegevens:
+----+---------------+----------------+-----------+---------------------+----------------------+
| id | firmanaam | v_adres | v_plaats | website_adres | emailadres |
+----+---------------+----------------+-----------+---------------------+----------------------+
| 1 | Jansen & Co | Stationsweg 14 | Groningen | www.jansen-co.nl | info@jansen-co.nl |
| 2 | Pietersen | Stationsweg 17 | Groningen | www.pietersen.nl | henk@pietersen.nl |
| 3 | Jansen & Co | Stationsweg 14 | Groningen | www.jansen.info | info@jansen-co.nl |
| 4 | Jansen & Co | Stationsweg 14 | Groningen | www.janssens.nl | info@jansen-co.nl |
| 5 | Pension Kobus | Grote Markt 1 | Assen | www.pensionkobus.nl | info@pensionkobus.nl |
+----+---------------+----------------+-----------+---------------------+----------------------+
Zoals je ziet, erg veel dubbele data: De firma Jansen & Co komt drie keer voor, met drie verschillende domeinnamen en hetzelfde adres/plaats/emailadres.
We maken deze tabel aan onze MySQL-database. Hierbij gaan we niet in op het data-type van kolommen.
CREATE TABLE if not exists klantmeta (
id int not null auto_increment,
primary key(id),
firmanaam varchar(30),
v_adres varchar(30),
v_plaats varchar(30),
website_adres varchar(68),
emailadres varchar(75)
) engine = InnoDB;
en vullen de tabel met onze gegevens
insert `klantmeta` (id, firmanaam, v_adres, v_plaats, website_adres, emailadres) values(default, 'Jansen & Co', 'Stationsweg 14', 'Groningen', 'www.jansen-co.nl', 'info@jansen-co.nl');
insert `klantmeta` (id, firmanaam, v_adres, v_plaats, website_adres, emailadres) values(default, 'Pietersen', 'Stationsweg 17', 'Groningen', 'www.pietersen.nl', 'info@pietersen.nl');
insert `klantmeta` (id, firmanaam, v_adres, v_plaats, website_adres, emailadres) values(default, 'Jansen & Co', 'Stationsweg 14', 'Groningen', 'www.jansen.info', 'info@jansen-co.nl');
insert `klantmeta` (id, firmanaam, v_adres, v_plaats, website_adres, emailadres) values(default, 'Jansen & Co', 'Stationsweg 14', 'Groningen', 'www.janssens.nl', 'info@jansen-co.nl');
insert `klantmeta` (id, firmanaam, v_adres, v_plaats, website_adres, emailadres) values(default, 'Pension Kobus', 'Grote Markt 1', 'Assen', 'www.pensionkobus.nl', 'info@pensionkobus.nl');
Gegevens opvragen in de database
Gegevens opvragen uit de database kan met het SELECT
-statement. Om alle gegevens van de firma Jansen & Co op te vragen gebruiken we de volgende query:
mysql> select * \
from klantmeta \
where firmanaam = 'Jansen & Co';
en dat geeft als resultaat:
+----+-------------+----------------+-----------+------------------+-------------------+
| id | firmanaam | v_adres | v_plaats | website_adres | emailadres |
+----+-------------+----------------+-----------+------------------+-------------------+
| 1 | Jansen & Co | Stationsweg 14 | Groningen | www.jansen-co.nl | info@jansen-co.nl |
| 3 | Jansen & Co | Stationsweg 14 | Groningen | www.jansen.info | info@jansen-co.nl |
| 4 | Jansen & Co | Stationsweg 14 | Groningen | www.janssens.nl | info@jansen-co.nl |
+----+-------------+----------------+-----------+------------------+-------------------+
3 rows in set (0.00 sec)
Met drie rijen is dit nog wel te doen, maar stel je voor dat de firma Jansen & Co 300 domeinnamen zou hebben. Dan staan alle andere gegevens ook 300 keer in de tabel en dat maakt het al lastiger om alle firma’s op te vragen die gevestigd zijn aan de Stationsweg.
Hieronder geïllustreerd (omdat het huisnummer in dezelfde kolom staat als de straatnaam, moeten we een LIKE string comparison met wildcard gebruiken):
mysql> select * \
from klantmeta \
where v_adres \
like 'Stationsweg%';
+----+-------------+----------------+-----------+------------------+-------------------+
| id | firmanaam | v_adres | v_plaats | website_adres | emailadres |
+----+-------------+----------------+-----------+------------------+-------------------+
| 1 | Jansen & Co | Stationsweg 14 | Groningen | www.jansen-co.nl | info@jansen-co.nl |
| 2 | Pietersen | Stationsweg 17 | Groningen | www.pietersen.nl | info@pietersen.nl |
| 3 | Jansen & Co | Stationsweg 14 | Groningen | www.jansen.info | info@jansen-co.nl |
| 4 | Jansen & Co | Stationsweg 14 | Groningen | www.janssens.nl | info@jansen-co.nl |
+----+-------------+----------------+-----------+------------------+-------------------+
4 rows in set (0.00 sec)
Tel hier nog 297x dezelfde gegevens van Jansen & Co bij. Dat is behoorlijk onoverzichtelijk en daarom moeten we deze MySQL database normaliseren.
Verplaats dubbele gegevens (stap 1a)
De eerste stap van het normaliseren van een database tabel is kijken naar:
- welke informatie dubbel is
- welke informatie verplaatst kan worden
Voldoet een stukje informatie aan beide voorwaarden, dan maken we daarvoor een afzonderlijke tabel aan. In bovenstaand voorbeeld is dat iedere kolom.
We slaan hierbij een aantal stappen over, en maken ook direct unieke ID’s aan die we later gebruiken. Deze unieke ID’s heten Foreign Keys en duiden relaties aan tussen twee tabellen in een database. Een waarde in een tabel zal verbonden worden met een andere tabel door twee waardes altijd overeen te laten komen. Verdere uitleg hierover valt helaas buiten de scope van dit artikel.
Je vindt meer informatie in bijvoorbeeld dit artikel op Sitemasters.be
Nieuwe tabellen aanmaken (stap 1b)
Hier maken we nieuwe tabellen aan om de dubbele data in op te slaan, met Foreign Keys. Zodoende zijn we begonnen aan het normaliseren van onze MySQL database.
CREATE TABLE if not exists klantmeta_website (
id int unsigned not null auto_increment primary key,
website_adres varchar(68),
website_id int unsigned references klantmeta_firma(f_id),
index(website_id)
) engine = innodb;
CREATE TABLE if not exists klantmeta_emailadres (
id int unsigned not null auto_increment primary key,
k_id int unsigned,
emailadres varchar(75) not null,
index(k_id)
) engine = innodb;
CREATE TABLE if not exists klantmeta_firma (
id int unsigned not null auto_increment primary key,
f_id int unsigned,
firmanaam varchar(30),
adres varchar(30),
plaats varchar(30),
email_id int unsigned references klantmeta_emailadres(k_id),
index(email_id)
) engine = innodb;
Dit maakt de volgende tabellen:
mysql> show tables like 'klantmeta_%';
+----------------------------------+
| Tables_in_db (klantmeta_%) |
+----------------------------------+
| klantmeta_emailadres |
| klantmeta_firma |
| klantmeta_website |
+----------------------------------+
3 rows in set (0.00 sec)
Foreign Key CASCADE
Als een cascade is ingesteld zal er een domino-effect optreden. Zodra een bedrijf verwijderd wordt gaan alle gegevens die bij dit bedrijf horen automatisch ook weg. Dat gebruiken we hier niet.
Databasegegevens overzetten (stap 2a)
Onze voorbeeld tabel `klantmeta
` is nog klein, hierdoor kunnen we eenvoudig de gegevens overzetten naar de nieuwe tabellen.
insert `klantmeta_website` (id, website_adres, website_id) values(default, 'www.jansen-co.nl', null);
insert `klantmeta_website` (id, website_adres, website_id) values(default, 'www.pietersen.nl', null);
insert `klantmeta_website` (id, website_adres, website_id) values(default, 'www.jansen.info', null);
insert `klantmeta_website` (id, website_adres, website_id) values(default, 'www.janssens.nl', null);
insert `klantmeta_website` (id, website_adres, website_id) values(default, 'www.pensionkobus.nl', null);
insert `klantmeta_emailadres` (id, k_id, emailadres) values(default, null, 'info@jansen-co.nl');
insert `klantmeta_emailadres` (id, k_id, emailadres) values(default, null, 'info@pietersen.nl');
insert `klantmeta_emailadres` (id, k_id, emailadres) values(default, null, 'info@pensionkobus.nl');
insert `klantmeta_firma` (id, f_id, firmanaam, adres, plaats) values(default, '1', 'Jansen & Co', 'Stationsweg 14', 'Groningen');
insert `klantmeta_firma` (id, f_id, firmanaam, adres, plaats) values(default, '2', 'Pietersen', 'Stationsweg 17', 'Groningen');
insert `klantmeta_firma` (id, f_id, firmanaam, adres, plaats) values(default, '3', 'Pension Kobus', 'Grote Markt 1', 'Assen');
update `klantmeta_website` set website_id = (select id from klantmeta_firma where firmanaam = 'Jansen & Co') where website_adres = 'www.jansen-co.nl';
update `klantmeta_website` set website_id = (select id from klantmeta_firma where firmanaam = 'Jansen & Co') where website_adres = 'www.jansen.info';
update `klantmeta_website` set website_id = (select id from klantmeta_firma where firmanaam = 'Jansen & Co') where website_adres = 'www.janssens.nl';
update `klantmeta_website` set website_id = (select id from klantmeta_firma where firmanaam = 'Pietersen') where website_adres = 'www.pietersen.nl';
update `klantmeta_website` set website_id = (select id from klantmeta_firma where firmanaam = 'Pension Kobus') where website_adres = 'www.pensionkobus.nl';
update `klantmeta_emailadres` set k_id = (select id from klantmeta_firma where firmanaam = 'Jansen & Co') where emailadres = 'info@jansen-co.nl';
update `klantmeta_emailadres` set k_id = (select id from klantmeta_firma where firmanaam = 'Pietersen') where emailadres = 'info@pietersen.nl';
update `klantmeta_emailadres` set k_id = (select id from klantmeta_firma where firmanaam = 'Pension Kobus') where emailadres = 'info@pensionkobus.nl';
Gegevens opvragen in de database (stap 2b)
Als we nu gegevens op dezelfde manier willen opvragen als zojuist, dan ondervinden we een probleem:
mysql> select * \
from klantmeta_firma \
where v_adres = 'Stationsweg';
ERROR 1054 (42S22): Unknown column 'v_adres' in 'where clause'
de tabel klantmeta_firma
kent immers geen kolom v_adres
, zoals de tabel klantmeta
die wel kende. Ai, wat nu?! Gelukkig kent MySQL JOINs :-)
MySQL JOIN’s – Gecombineerde gegevens opvragen uit jouw database (stap 2c)
Een JOIN combineert gegevens uit verschillende tabellen (simplified). Als we graag alle domeinnamen van het bedrijf Jansen & Co willen weten, dan moeten we de tabellen klantmeta_firma en klantmeta_website samenvoegen in één query:
mysql> select w.website_adres \
from klantmeta_website w \
join klantmeta_firma f on w.website_id = f.id \
where f.firmanaam = 'Jansen & Co';
+------------------+
| website_adres |
+------------------+
| www.jansen-co.nl |
| www.jansen.info |
| www.janssens.nl |
+------------------+
3 rows in set (0.00 sec)
Hé! Dat klopt!
Tweede JOIN, om nog meer gegevens op te vragen (Stap 2d)
De query kan uitgebreid worden met een tweede JOIN. Je kunt hiermee ook het e-mailadres van Jansen & Co erbij opvragen:
mysql> select w.website_adres, e.emailadres, f.firmanaam \
from klantmeta_website w \
join klantmeta_firma f on w.website_id = f.id \
join klantmeta_emailadres e on e.k_id = f.id \
where f.firmanaam = 'Jansen & Co';
+------------------+-------------------+-------------+
| website_adres | emailadres | firmanaam |
+------------------+-------------------+-------------+
| www.jansen-co.nl | info@jansen-co.nl | Jansen & Co |
| www.jansen.info | info@jansen-co.nl | Jansen & Co |
| www.janssens.nl | info@jansen-co.nl | Jansen & Co |
+------------------+-------------------+-------------+
3 rows in set (0.00 sec)
Je ziet dat we de data uit verschillende tabellen aan elkaar knopen met behulp van de gemaakte Foreign Keys.
Conclusie MySQL database normaliseren
Een MySQL database normaliseren is belangrijk. Het houdt de database zoveel mogelijk vrij van dubbele informatie, en dus is de database kleiner. Hierdoor is informatie in jouw database sneller opvraagbaar en de database overzichtelijker.
Het is verstandig om in de ontwerpfase van de database al na te denken over normaliseren: dat is vaak minder werk dan achteraf een grote, jaren oude MySQL database te normaliseren.