[PHP & MySQL] Databasestructuur en manier van bewaren

[Flying]

Momenteel heb ik op vraag van een school een kleine website draaien. De opzet is vrij eenvoudig: een aantal leerlingen krijgt beschikking over een reeks hulpmiddelen afhankelijk van de achtergrond van die leerling. De maatregelen zijn verdeeld over verschillende categorieën bv. algemeen, taalkundig, ...

Het doel was om een fiche te kunnen aanmaken voor een leerling met daarin enkele basisgegevens (naam, datum opstart, datum contact ouders, ...) en uiteraard alle maatregelen van toepassing. Belangrijk hierbij is dat de maatregelen gespecificeerd zijn per onderwijsjaar. Zo wordt de fiche een aangroeifiche waarbij het steeds mogelijk is om een volgend schooljaar nieuwe maatregelen bij aan te duiden alsook terug te kunnen gaan kijken naar het verleden. Dat is een simpele tabel met alle jaren in en een kruisje per jaar.

De begeleider kan per maatregel en per jaar een vak aanvinken. De fiche moet later te bewerken zijn.

Gewone gegevens opvragen en opslaan zijn niet het probleem, wel de maatregelen. Een tweetal jaar geleden heb ik dit opgelost, maar nu blijkt dit niet de ideale manier te zijn. Ze willen maatregelen kunnen verwijderen, maar daarmee hield ik geen rekening (dom). Nu een maatregel verwijderen zal verregaande gevolgen hebben vrees ik. Een tweede probleem is dat er een bug is opgedoken (nu pas) bij het ophalen van de maatregelen doordat er achteraf maatregelen in verschillende categorieën zijn bijgevoegd.

Hier even een voorbeeld van hoe de maatregelen van het kind opgeslagen worden in de database:

code:

|1:L4,1:L5,1:L6|2:L4,2:L5,2:L6|3:L4,3:L5,3:L6|4:L4,4:L5,4:L6|31:L4|61:L4,61:L5,61:L6|62:L4,62:L5,62:L6|66:L4,66:L5,66:L6|69:L4,69:L5|

Volgende afbeelding is hoe dit er op de fiche uitziet:


Elke maatregel-id staat tussen piping-teken. Voor die maatregelen staan daar alle leerjaren op. 1:L1 betekent maatregel met id #1 voor L1. Als er meerdere leerjaren van toepassing zijn dan staan die tussen dezelfde piping-tekens.

Binnen PHP splits ik de maatregelen op in een array via de piping-tekens en later doe ik hetzelfde voor de leerjaren.

De categorieën staan in een database, de maatregelen staan in een database, de fiches van de leerlingen staan in een database. Ik heb het idee dat de manier van opslaan, ophalen, ... niet erg goed bedacht zijn, maar kan ook niet echt een alternatief verzinnen ervoor. Daarvoor zoek ik hulp. Wat zou een betere database-structuur zijn? Wat zou een goede manier zijn om de maatregelen per kind en per leerjaar bij te houden?

Ik heb beslist om helemaal van nul te beginnen met de website. Aanpassen lijkt me, hoewel ik binnen de code goed heb gedocumenteerd, meer werk dan opnieuw beginnen. Graag blijf ik bij de PHP/MySQL combinatie, maar ik zou in een later stadium alles eens willen proberen omzetten naar Django.

Disclaimer: het was een leerproject voor mij. Ik ben geen superprogrammeur en vond het wel leuk om daar wat van mijn tijd aan te besteden. Ik heb er veel uit geleerd, ook uit de fouten die ik heb gemaakt.

[RobIII]

Flying schreef op zondag 18 november 2018 @ 11:37:
Wat zou een betere database-structuur zijn? Wat zou een goede manier zijn om de maatregelen per kind en per leerjaar bij te houden?

Steekwoord hier is: Normaliseren. Je data "comma separated" (of wat voor 'creatieve' manier dan ook) in je database zetten is altijd een slecht idee. Dus begin eens met normaliseren en dan komt er vanzelf, echt waar, een goed datamodel uit.

[AW_Bos]

Bij het maken van een goede genormaliseerde database moet je denken aan entiteiten.
Zo is een leerling een entiteit, een maatregel een entiteit, en een leerjaar of niveau een entiteit.
Elke entiteit kan precies in een één tabel beschreven worden. En met koppeltabellen kan je entiteiten aan elkaar koppelen.

De bedoeling met een genormaliseerde database is dat je de data in aantallen verticaal kan uitbreiden in records. Als je dit horizontaal doet, en dus in velden (l1,l2,l3,l4 etc...) dan ben je al fout bezig. Ook als je data geserialiseerd opslaat moet je je toch even achter de oren krabben . Want daar kan je gewoon niet eenvoudig in filteren of selecteren.

[Voor 4% gewijzigd door AW_Bos op 18-11-2018 13:07]

[Flying]

RobIII schreef op zondag 18 november 2018 @ 12:57:
[...]

Steekwoord hier is: Normaliseren. Je data "comma separated" (of wat voor 'creatieve' manier dan ook) in je database zetten is altijd een slecht idee. Dus begin eens met normaliseren en dan komt er vanzelf, echt waar, een goed datamodel uit.

Klinkt goed. Elk soort gegeven krijgt een afzonderlijke tabel en je legt een relatie tussen de tabellen. Zo ver kan ik volgen. Je moet dan toch ergens nog de koppeling maken? Ik duik vanavond best eens een aantal tutorials in. Het principe is duidelijk, de uitvoering ervan niet.

[AW_Bos]

Klopt, de koppeling maak je dan in een 'koppeltabel.'
Je kan hierin dus een LeerlingID koppelen aan bijv. een aantal MaatregelID's. Elke koppeling heeft zijn eigen record.

(Dit is een voorbeeld, geen idee of zo een koppeling ook zo in de praktijk van je applicatie hoort)

[Voor 23% gewijzigd door AW_Bos op 18-11-2018 14:15]

[Flying]

In mijn geval was er geen database met leerlingen. De fiche die wordt aangemaakt bevat alle gegevens van de leerling. Hoe ik dat concreet ga aanpakken moet ik nog bekijken.

[Josk79]

Als ik het goed begrijp heb je sowieso al deze 2 tabellen:

Leerlingen
- id
- naam, etc

Maatregelen
- id
- omschrijving

Hier zou je zo'n tabel kunnen toevoegen:

Leerlingen_maatregelen
- id
- leerling_id
- jaarnummer
- maatregel_id

Dan kun je eenvoudig de tabellen koppelen met joins. Zoiets:

code:

SELECT * FROM Leerlingen l
LEFT JOIN Leerlingen_maatregelen lm ON lm.leerling_id = l.id
LEFT JOIN Maatregelen m ON m.id = lm.maatregel_id

Misschien moet je voor jaarnummer wat anders bedenken om aan een schooljaar te koppelen en hoe zit het als een leerling blijft zitten?

[Flying]

Ik heb sowieso meerdere tabellen al:

Categorie maatregelen
Id
Naam

Maatregelen
Id
Maatregel
Categorie

Fiche
Naam leerling
Datum aanmaken fiche
Datum opstart traject
Datum contact met ouders
Naam ouder
Maatregelen +leerjaar waarop ze van toepassing zijn

Waarbij ik begrijp dat Fiche teveel gegevens bevat. Op de een of andere manier moet het mogelijk zijn om maatregelen aan een leerling te koppelen en op te geven op welke leerjaren ze van toepassing zijn. Dat is waar het nu fout loopt. Ik begrijp het verhaal van normalisatie, maar weet niet hoe ik het moet aanpakken. Er moet toch ergens in een database staan dat maatregel 1, 17, 44, 45, 63 en 87 van toepassing zijn op leerling 1 en dan per maatregel op welk leerjaar ze van toepassing zijn.

Daartoe denk ik dat volgende tabel erbij komt:
Leerjaren
Id
Leerjaar

Onderlinge koppeling nodig tussen leerjaar, maatregelen, leerling zoals werd aangegeven.

Edit: volgens mij heb ik 'm door
Categoriën
- id
- categorie

Maatregelen
- id
- categorie_id
- maatregel

Leerling
- id
- voornaam
- naam
- geboortedatum

Leerling_maatregelen
- id
- leerling_id
- maatregel_id
- categorie_id

Fiche
- id
- datum
- leerling_id

Dat zou betekenen dat voor elke maatregel die van toepassing is op een leerling er een record wordt toegevoegd aan de tabel Leerling_maatregelen. Dan is er de tabel Fiche met enkel een id, datum en leerling_id. De fiche zelf wordt dan opgebouwd door een combinatie te maken van verschillende tabellen op de pagina.

Dit zou de maatregelen, leerlingen en fiches afzonderlijk doorzoekbaar moeten maken. Bovendien is elke maatregel die van toepassing is nu niet langer data gescheiden door komma's, horizontaal opgeslagen, maar een afzonderlijke record (verticaal opgeslagen).

Het duurde even, maar ik denk dat ik 'm nu helemaal snap. Op- of aanmerkingen zijn uiteraard nog steeds welkom!

[Voor 34% gewijzigd door Flying op 18-11-2018 18:41]

[Josk79]

categorie_id heb je nu in zowel Leerling_maatregelen als Maatregelen, dat is dubbelop. In 1 van de 2 moet hij weg. Je mis ook het leerjaar, volgens mij?

[CH4OS]

Josk79 schreef op zondag 18 november 2018 @ 14:54:
Als ik het goed begrijp heb je sowieso al deze 2 tabellen:

Leerlingen
- id
- naam, etc

Maatregelen
- id
- omschrijving

Hier zou je zo'n tabel kunnen toevoegen:

Leerlingen_maatregelen
- id
- leerling_id
- jaarnummer
- maatregel_id

Dan kun je eenvoudig de tabellen koppelen met joins. Zoiets:

code:

SELECT * FROM Leerlingen l
LEFT JOIN Leerlingen_maatregelen lm ON lm.leerling_id = l.id
LEFT JOIN Maatregelen m ON m.id = lm.maatregel_id

Misschien moet je voor jaarnummer wat anders bedenken om aan een schooljaar te koppelen en hoe zit het als een leerling blijft zitten?

leerlingen_maatregelen zou ik dan eerder leerling_maatregel noemen, maar dat is een detail.

An sich heeft deze koppeltabel geen kolom ID nodig (al snap ik ook niet waarom mensen alles maar per se een ID willen meegeven in koppeltabellen). Dan beperk je jezelf enorm in het aantal mogelijke relaties (terwijl die oneindig zijn in principe). Het leerling ID en het maatregel ID is wat de koppeling uniek maakt, immers.

Echter moet er nog een (school)jaar bij, om te diffirentieren tussen (school)jaren. Als die drie kolommen tezamen dan nog altijd meer dan 1 resultaat teruggeven is er wederom een fout, of moet er nog een kolom bij om de relatie uniek te maken.

[Voor 5% gewijzigd door CH4OS op 22-11-2018 17:48]

[Josk79]

CH4OS schreef op donderdag 22 november 2018 @ 17:45:
[...]
An sich heeft deze koppeltabel geen kolom ID nodig (al snap ik ook niet waarom mensen alles maar per se een ID willen meegeven in koppeltabellen). Dan beperk je jezelf enorm in het aantal mogelijke relaties (terwijl die oneindig zijn in principe). Het leerling ID en het maatregel ID is wat de koppeling uniek maakt, immers.

Klopt, id is niet nodig maar ik ben er wel eens tegenaangelopen met database tools dat je bijv. geen record kan verwijderen als er geen PK is die uit 1 kolom bestaat.

Tweede deel van je alinea begrijp ik niet wat je bedoeld.

[CH4OS]

Josk79 schreef op donderdag 22 november 2018 @ 18:08:
[...]


Klopt, id is niet nodig maar ik ben er wel eens tegenaangelopen met database tools dat je bijv. geen record kan verwijderen als er geen PK is die uit 1 kolom bestaat.

Tweede deel van je alinea begrijp ik niet wat je bedoeld.

Je moet iets hebben wat het record uniek maakt, omdat het delete statement alles verwijderd en de where clause een filter maakt (en mocht je heel zeker willen zijn, kun je geloof ik LIMIT 1 er achteraan doen en hopen dat de juiste verwijderd is). Maar dan is je (primary) key toch de combinatie van de drie kolommen? Dat hoeft niet in een apart ID veld.

[Voor 8% gewijzigd door CH4OS op 22-11-2018 18:10]

[Flying]

Ik begrijp dat sentiment. Een id koppelen eraan lijkt me dan technisch gezien weer iets eenvoudiger. Ik bekijk nog wat ik doe. Momenteel zit het toch nog in de opstartfase en moet ik eerst een ander platform koppelen aan mijn platform met OAuth. Wanneer dat gelukt is, start ik met de opbouw van de databases.

Het klopt trouwens helemaal dat er een nog een leerjaar_id toegevoegd moet worden aan de koppeltabel. Anders kan een maatregel_id en een leerling_id inderdaad meerdere keren voorkomen. Dat was fout in mijn structuur in de post, maar staat inmiddels wel goed in mijn notitieblok.

[Josk79]

@CH4OS Ik zeg net waarom ik zelf dat id veld om andere redenen nog wel eens bij plaats.

Wat bedoel je met "Dan beperk je jezelf enorm in het aantal mogelijke relaties (terwijl die oneindig zijn in principe)"?

[CH4OS]

Een ID is 9 van de 10 keer een getal, op zich prima, maar ook dat heeft een maximum.
Het aantal relaties is oneindig, waarom lijkt me duidelijk naar mate er meer en meer kinderen bijvoorbeeld in het systeem komen of maatregelen, you name it.

[Voor 48% gewijzigd door CH4OS op 22-11-2018 18:16]

[Flying]

CH4OS schreef op donderdag 22 november 2018 @ 18:14:
Een ID is 9 van de 10 keer een getal, op zich prima, maar ook dat heeft een maximum.

Ik begrijp volledig wat je bedoelt. De kans dat ik in mijn project aan de eindigheid van dat getal kom is nihil, maar qua good practice volg ik misschien toch je redenering.

Moet je dan, behalve het laten wegvallen van het id-veld, nog iets wijzigen binnen de MySQL/phpMyAdmin?

[CH4OS]

In een koppeltabel zet je de ID's oid van de items (leerling, maatregel, (school)jaar) die de koppeling vormen, eventueel aangevuld met wat andere eigenschappen. Denk dan bijvoorbeeld aan een created_at, updated_at of zo, zodat je kan zien wanneer de maatregel is toegevoegd of bewerkt voor die betreffende leerling.

Voor de rest zou ik het zo clean mogelijk houden en alleen het broodnodige in de koppeltabel zetten.

[Voor 16% gewijzigd door CH4OS op 22-11-2018 18:21]

[Josk79]

Zolang je niet meer dan 9223372036854775807 combinaties hebt kom je met een bigint een heel eind

Maar zoals gezegd, id in koppeltabel is niet persé nodig.

[incaz]

Ik zou de datum contact ouders naar een aparte tabel halen, zodat je meerdere contacten kunt bijhouden en ook een geschiedenis daarvan hebt.

(Van niet-technische orde: hou je ook een oogje op de privacywetgeving?)

[eric.1]

Wellicht een kleinigheidje:

Flying schreef op zondag 18 november 2018 @ 15:24:
Edit: volgens mij heb ik 'm door
Categoriën
- id
- categorie

Maatregelen
- id
- categorie_id
- maatregel

Leerling
- id
- voornaam
- naam
- geboortedatum

Leerling_maatregelen
- id
- leerling_id
- maatregel_id
- categorie_id

Fiche
- id
- datum
- leerling_id

Die categorie_id zit al in de maatregel, dus dat lijkt me op het eerste gezicht redelijk overbodig om die nog een maal mee te geven.

[Flying]

Dit heb ik in mijn notitieblok staan overigens: https://i.imgur.com/Cbkw7Wu.png

[Flying]

incaz schreef op donderdag 22 november 2018 @ 18:25:
(Van niet-technische orde: hou je ook een oogje op de privacywetgeving?)

Uiteraard. Enkel de directie, begeleider en leerkrachten hebben toegang tot het platform. De maatregelen worden enkel opgesteld indien de ouders akkoord gaan. De ouders krijgen directe inzage in de fiche (elke fiche wordt afgedrukt, overlopen met de ouders[voor akkoord] en bewaard in een map). Enkel gegevens die nodig zijn voor het opstellen van de fiche worden gebruikt en opgeslagen in de database.

Ouders zijn dus steeds op de hoogte alvorens er een fiche aangemaakt wordt. Op het moment dat de leerling de school verlaat wordt de fiche ook door de begeleider verwijderd. Bovendien wordt er van hogerop controle uitgevoerd.

Met het vernieuwen van het platform wordt aanmelden enkel nog mogelijk met het overkoepelende platform. Het overkoepelende platform verplicht elke gebruiker tot twee-staps authenticatie.

Edit: ik zit overigens in de daarvoor opgestelde AVG-werkgroep samen met de zorgbegeleider van de school.

[Voor 5% gewijzigd door Flying op 22-11-2018 18:35]

[Flying]

Zonder een nieuw topic te willen openen.

De Oauth werd toegevoegd en werkt. Alle databases werden aangemaakt. Verschillende onderdelen heb ik aangemaakt en werken (met cascade bij de relation).

Het moeilijkste komt voor mij nu pas. Onderstaande afbeelding is een onderdeel van het toevoegen van een fiche. De nadruk ligt op gebruiksvriendelijkheid dus worden meteen alle maatregelen ingeladen met zeven mogelijke vinkjes per maatregel.


De elke maatregel die van toepassing is wordt toegevoegd in de database als volgt:
- Leerling ID
- Maatregel ID
- Leerjaar ID

Waarbij die drie velden samen ervoor zorgen dat elke rij uniek is. Een maatregel voor een bepaald leerjaar kan immers slechts van één keer van toepassing zijn op die leerling. Dit alles zit in een formulier gegoten waarbij iedere checkbox een naam krijgt als volgt: maatregel_id:leerjaar_id. Ik vul de fiche in, selecteer tien maatregelen en bij elke maatregel twee leerjaar. Er moeten 20 records worden toegevoegd aan de database.

De hamvraag: hoe ga ik van mijn formulier naar het opslaan van 20 records of zo'n correct/efficiënt mogelijke manier?

[incaz]

Niet te druk maken om het zo efficient mogelijk te doen maar gewoon stuk voor stuk, zodat het goed leesbaar en testbaar is. Dit gaat niet om aantallen waar het nut heeft om dit extreem te optimaliseren. Alleen het hergebruiken van je db-connectie is nuttig.

Edit: hou vooral ook rekening met de mogelijkheid of maatregelen ook weer gedeselecteerd kunnen worden, vanuit dezelfde interface, en dat records niet meerdere keren worden toegevoegd als je vaker submit. (Vinkjes zijn wat dat betreft niet altijd een ideaal systeem omdat je eigenlijk een 'changed'-property wilt hebben, maar die wordt niet automatisch gesubmit.)

[Voor 41% gewijzigd door incaz op 14-12-2018 13:06]

[Flying]

De maatregel moeten absoluut terug afgevinkt kunnen worden. Is er een alternatief voor vinkjes?

[mcDavid]

waarom je checkboxes niet gewoon maatregel_id[] als naam, en leerjaar_id als value geven? Dan heb je per maatregel een array met leerjaren en daar kun je vast een mooie query voor bouwen.

Andersom kan natuurlijk ook, net wat je handig vindt.