Hou zouden jullie dit doen ?
ID-nummer, datum, uur, naam van de groep.
Gekoppeld aan de tabel "rondleidingen" is een "detail-tabel" waarin een of meerdere gidsen kunnen worden toegevoegd. deze tabel heeft deze velden :
ID, naam, rondleiding-nummer. Een master-detail relatie laat dan zien welke gids(en) deze rondleiding zullen doen.
Een gids kan worden geselecteerd uit een derde tabel (= tabel beschikbare gidsen ) met deze velden :
ID, naam (= naam van de gids ).
Een rondleiding duurt maximaal 2 uur. Men kan meerdere rondleidingen toevoegen per dag, maar het zou kunnen dat een volgende rondleiding slechts één later start dan de voorgaande rondleiding. Bedoeling is niet dat je een rondleiding toevoegt binnen de twee uren waaruit je uit de derde tabel (= tabel beschikbare gidsen ) de gids kan selecteren die is gekozen in de rondleiding van één uur ervoor.
3 tabellen dus, ik wil het onmogelijk maken dat je een gids kan selecteren die is ingepland in een rondleiding van één uur ervoor. Hoe zouden jullie dit fiksen ? Alle op- en aanmerkingen hierover zijn steeds welkom. Dank op voorhand.
Je hebt een tabel `rondleiding` met `id`, `datum`, `uur`, `groep` (text). Maar wat doe je met de naam van de groep? Wat is een groep, en waarom moet die een naam hebben? Is de naam van de groep het soort rondleiding dat gegeven wordt?
De tabel rondleiding mist een kolom `tot` (datetime). Nu weet je niet voor hoe lang een rondleiding wordt ingepland, of het nu voor 1 uur is, of 2 uur, of anders. En vervang dan ook meteen `datum` en `uur` voor `van` (datetime). Maak `van` en `tot` NOT NULL. En maak alle andere kolommen die iets moeten bevatten NOT NULL.
Er is een tabel `gids` met `id`, `naam` (text), dat is prima. Maar om één of meerdere gidsen te koppelen aan een rondleiding heb je een tabel `detail-tabel` met de kolommen `ID`, `naam`, `rondleiding-nummer`. Dat kan beter: hernoem de tabel naar gidsrondleiding (zodat je weet wat gekoppeld wordt). Verwijder de kolom ID, want je wilt niet vaker dan 1 keer een gids kunnen koppelen aan een rondleiding. Vervang de kolom `naam` voor `gids` (bigint) en laat deze verwijzen (maak een FOREIGN KEY CONSTRAINT) naar `gids` (`id`). Doe hetzelfde voor `rondleiding-nummer`; hernoem het naar `rondleiding` (bigint) en laat het verwijzen naar `rondleiding` (`id`). Maak in de tabel een PRIMARY KEY aan met de kolommen `gids` én `rondleiding`.
Je kunt vervolgens in de database CHECK CONSTRAINTS maken, zodat de tabellen nooit ongeldige data kunnen bevatten.
Bijvoorbeeld:
Code (php)
1
2
2
ALTER TABLE `rondleiding` ADD CONSTRAINT `rondleiding_van_tot` CHECK (`tot` > `van`);
COMMENT ON CONSTRAINT `rondleiding_van_tot` ON `rondleiding` IS 'Datumtijd moet opvolgend zijn';
COMMENT ON CONSTRAINT `rondleiding_van_tot` ON `rondleiding` IS 'Datumtijd moet opvolgend zijn';
Je kunt het commentaar in de database ophalen in PHP om een melding te tonen waarom een rondleiding niet opgeslagen kan worden.
Ander voorbeeld:
Code (php)
1
2
2
ALTER TABLE `rondleiding` ADD CONSTRAIT `rondleiding_max` CHECK ((`tot` - `van`) <= '2 HOUR'::interval);
COMMENT ON CONSTRAINT `rondleiding_max` ON `rondleiding` IS 'Een rondleiding duurt maximaal 2 uur';
COMMENT ON CONSTRAINT `rondleiding_max` ON `rondleiding` IS 'Een rondleiding duurt maximaal 2 uur';
Je kunt allerlei constraints maken:
- dat `van` en `tot` niet mogen overlappen voor hetzelfde soort rondleiding
- dat er minimaal een uur moet zitten voor de volgende rondleiding met dezelfde gids
Zorg eerst voor een goed genormaliseerd datamodel. De SQL is een mix tussen MySQL/MariaDB (wat ik denk dat je gebruikt) en PostgreSQL (wat ik gewend ben). Als het goed is kom je er wel uit.
Gewijzigd op 17/12/2023 14:09:04 door Ad Fundum
De enige reden om MySQL nog te gebruiken anno 2023 is om te bezuinigen op hosting bij een website/webapplicatie, omdat PostgreSQL minder voorkomt. Maar je betaalt met je eigen tijd. En hoe groter je programma wordt hoe meer de tijdsinvestering uit verhouding komt te staan met wat je bespaart. Dus neem PostgreSQL, het kost je geen extra moeite in PHP.
Wat zou in dit concrete geval het specifieke voordeel zijn van PostgreSQL t.o.v. MySQL/MariaDB?
Met andere woorden, wat heeft Ronny nodig dat niet met MySQL/MariaDB kan, maar wel met PostgreSQL?
Bijvoorbeeld: je kunt ook een spijker in de muur krijgen door er met een steen op te slaan, dus waarom zou je een hamer gebruiken? Misschien omdat een steen goedkoper is en sneller voorhanden? Het kan, en is ook niet onlogisch als je maar één spijker in de muur moet slaan.
Wat betreft MySQL en Postgres is het appels en peren vergelijken, gelijk de steen en de hamer. MySQL is bedoeld als simpele, ACID-compliant database engine voor simpele configuraties op een enkele plek. Omdat MySQL weinig voor je doet, is het ook een snelle database die minder resources gebruikt dan PostgreSQL. Omdat er nou eenmaal meer beginners zijn dan doorgewinterde professionals, is het voor hosters belangrijk om de kostprijs zo laag mogelijk te houden. Dus ligt MySQL binnen handbereik en moet je voor PostgreSQL net wat meer moeite doen.
PostgreSQL zit in een compleet ander segment, het is met recht de meest uitgebreide open source database ter wereld, het laat zich goed vergelijken met Oracle. De lijst van dingen die beter gaan in PostgreSQL in verhouding met MySQL is echt te lang voor in enkele post hier. MySQL kan zich niet meten met PostgreSQL of Oracle, daarom heeft Oracle MySQL ook overgekocht waarna de fork MariaDB is ontstaan. Dat maakt het ecosysteem ook niet overzichtelijker.
Uit eigen ervaring (16+ jaar MySQL, 7+ PostgreSQL) zal ik benoemen waar Ronnie meteen tegenaan kan lopen wanneer hij MySQL zou gebruiken, dat door de minimalistische opzet meer complexiteit met zich meebrengt voor een programmeur:
Neem datums. Hoe weet je vooraf zeker dat je datums op slaat die kloppen? Ga je ze zelf controleren in PHP? Of ga je toestaan dat gebruikers onzindatums in kunnen vullen als 2023-12-32? Of 2023-12-00?
Neem tijd. Hoe weet je vooraf zeker dat alle tijdrekeningen goed gaan, vooral met het oog op tijdzones? Misschien denk je dat het niet nodig is, maar in Nederland wisselen we 2x per jaar van tijdzone (wintertijd/zomertijd). MySQL kan geen tijdzones opslaan?
En als je datums en tijden in PHP controleert: gaan dat wel goed in de communicatie tussen MySQL en PHP? Heb je gecontroleerd dat ze op dezelfde server staan, met dezelfde wel op dezelfde server, met dezelfde locales? Zorg je er wel voor dat als je een backup moet restoren, dat je dezelfde locale gebruikt omdat anders de data corrupt raakt?
En neem de naam van de groep. Hoe lang mag die zijn? 20 tekens? 30? En als je dan een VARCHAR(30) aanmaakt, werkt het niet als er groepsnamen voorbij komen met diakritische tekens, omdat die meer ruimte innemen in MySQL? (MySQL rekent de lengte in bytes in plaats van in tekens). De standaardcodering in MySQL is alleen de eerste plane van Unicode, is dat genoeg?
Als je geen verdere datacorruptie wil, moet je zelf zorgen dat foreign key constraints überhaupt werken. Je moet weten dat MySQL een database engine is, en geen storage engine. En dat alleen de InnoDB storage engine dat ondersteunt. MySQL kent alleen een binary tree index, je kunt geen index maken op overlappende datums.
Als je één stapje verder bent met je applicatie, en er moeten mensen met verschillende rollen er mee werken (bijvoorbeeld verschillende groepen in plannen, verschillende dingen zien), dan heb je een rechtensysteem nodig. Je bent dan veel tijd kwijt in PHP om zoiets te verzinnen en op te zetten. Maar het wordt steeds lastiger (denk aan RLS) wanneer het programma verder zal groeien. In PostgreSQL zit het standaard, goedgekeurd door derde partijen en toepasbaar tot en met grootzakelijk gebruik.
Ben je nog wat verder, en je wilt fuzzy (= ongeveer) kunnen zoeken in de lijst van groepen, bijvoorbeeld vanwege diakritische tekens (= normaal in het Nederlands), hoe doe je dat in MySQL? In PostgreSQL kan het direct via standaardextenties die je alleen maar aan hoeft te zetten.
Natuurlijk kan je dit vast ook allemaal zelf in PHP en MySQL (met UDF's) gaan zitten bouwen.
Maar waarom zou je dat doen als diezelfde functionaliteit en garanties op gegevenskwaliteit al worden geboden door PostgreSQL?
Hoewel ik heb geprobeerd bovenstaande objectief te brengen, weet ik dat ik (net als met PHP) een een bias heb ten opzichte van MySQL. MySQL is in het verleden onderdeel geweest van meerdere frustraties. Vanwege de storage engines, vanwege onlogische ondersteuning voor Unicode in de transitie van Latin1 naar UTF-8, vanwege valkluilen van configuraties, en vanwege dat alles kan als je er zelf maar genoeg tijd in steekt om het allemaal zelf te bouwen.
Wanneer je een weloverwogen keuze wilt maken raad ik aan om ook te kijken naar wat anderen zeggen:
- IBM: https://www.ibm.com/blog/postgresql-vs-mysql-whats-the-difference
- Kinsta: https://kinsta.com/nl/blog/postgresql-vs-mysql
Ik werd na 16+ jaar gedoe met MySQL in ieder geval heel blij met PostgreSQL met het rechtensysteem (killer feature), het feit dat allerlei (voor mij) basisbehoeften geregeld waren die toen niet in MySQL geregeld waren, geen ingewikkeld technisch gedoe met meerdere engines, eigen datatypen, het hele ecosysteem, en meer. Ik heb de laatste 5 jaar dan ook niet veel meer naar MySQL omgekeken.
Gewijzigd op 19/12/2023 11:08:33 door Ad Fundum
Nadeel is namelijk dat je dan eerst een paar stappen terug moet doen om PostgreSQL te installeren. En ook inrichting is wat meer werk.
En inderdaad heb je ook een hoster nodig die dit aanbiedt. Dat staat zit vaak niet (standaard) in het pakket.
En eerlijk gezegd hoeft een persoon dit ook niet allemaal zelf te beheersen.
Je hebt mensen die
- applicaties ontwerpen,
- scripten
- databases inrichten
- servers beheren
En dat hoeft niet allemaal in 1 persoon verenigt te zijn. Ja, het is prettig als je in je eentje een applicatie bouwt dat je dat allemaal kan.
Maar dan zou ik beheren van servers (webserver en database server) al snel over willen laten aan een hoster.
En in dit geval is er geen reden om maar direct over te stappen.
De postbode komt hier ook met de bakfiets. En er is voor heb geen reden om een bus met laadklep, automatische dozen sortering en routenavigatie te nemen voor de brievenbezorging.
Qua hamer en steen: ik zou het ipv de steen vergelijking, liever houden op "hamer uit de 5-euro-bak van de Gamma" of de hamer van Bahco die je aannemer heeft van de winkel voor de profs.
Uiteindelijk moet je toch eerste leren hoe data in databases staat en hoe je het er weer uittrekt.
Create-table, insert-query en joins, aangevuld met kennis van Foreign Keys is ook met Mysql te leren.
Net als je vogelhuisje met de Gamma-hamer wel lukt.
Ga je daarna verder, dan kijk je bij je echte projecten weer of MySQL nog wel jouw gereedschap is, of dat een ander type db beter geschikt is.
---
overigens kan Mysql wel degelijk zoeken op letters met accenten etc.
Om even een voorbeeld van Stackoverflow te pakken:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
create table accenttest
(
col1 varchar(100)
);
insert into accenttest values ('instruccin');
select col1
from accenttest
where col1 = 'instruccion' collate utf8_general_ci;
select col1
from accenttest
where col1 = 'instruccin' collate utf8_general_ci;
(
col1 varchar(100)
);
insert into accenttest values ('instruccin');
select col1
from accenttest
where col1 = 'instruccion' collate utf8_general_ci;
select col1
from accenttest
where col1 = 'instruccin' collate utf8_general_ci;
vinden beide deze row.
Maar dat gedrag kun je wel beïnvloeden met de collate.
Sommige talen vinden namelijk iets anders over letters met accenten.
Waar wij in Nederland een ô gewoon een o vinden, vinden Duitsers dat een ö eignelijk een oe is. Maar Zweden hebben na de 26 letters die wij ook kennen nog 3 extra lettes in het alfabet. Na Z volgen de ä ö en a-ring.
Dat heeft gevolgen voor het sorteren. (de Duitse ö komt dus voor de p, waar de Zweedse ö na de Z en Ä komt)
voorbeeld 2:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create table accenttest
(
col1 varchar(100) collate utf8mb4_german2_ci
);
insert into accenttest values ('Blum');
insert into accenttest values ('Blm');
insert into accenttest values ('Bluem');
select col1
from accenttest
where col1 = 'blum';
select col1
from accenttest
where col1 = 'blm';
select col1
from accenttest
where col1 = 'bluem';
(
col1 varchar(100) collate utf8mb4_german2_ci
);
insert into accenttest values ('Blum');
insert into accenttest values ('Blm');
insert into accenttest values ('Bluem');
select col1
from accenttest
where col1 = 'blum';
select col1
from accenttest
where col1 = 'blm';
select col1
from accenttest
where col1 = 'bluem';
De eerste select vindt alleen maar het 1e record. Maar de 2e en 3e query vinden allebei de andere 2 records.
Maar dit soort zaken vereisen ook begrip van de taal in kwestie. En dit staat op zich los van de keuze voor MySQL / PostgreSQL.