Relatie voorwaarde
Nog steeds ben ik bezig met de opzet van mijn database en ik loop tegen een probleem aan waarvan ik niet weet hoe ik het moet oplossen, of naar welke termen ik moet zoeken op internet om tot een oplossing te komen.
Mijn database bestaat uit 3 tabellen:
- gebruiker
- school
- school_afdeling
Het idee is dat een gebruiker zich kan aanmelden bij 1 of meerdere scholen. Elke school heeft afdelingen;
- School 1 heeft bijvoorbeeld afdeling A en B,
- School 2 heeft bijvoorbeeld afdeling C en D
Naast dat een gebruiker zich bij een school kan inschrijven kan diegene zich ook eventueel(!) inschrijven bij 1 afdeling die gekoppeld is aan de school.
Wanneer een gebruiker is ingeschreven bij school 1, kan hij zich dus optioneel inschrijven bij afdeling A en B, maar niet bij afdeling C en D, omdat die behoren aan school 2.
Het probleem is nu, hoe leg ik deze koppeling?
Op dit moment heb ik een tabel gemaakt genaamd gebruiker_school, met daarin de volgende velden:
- gebruiker_id
- school_id
- school_afeling_id
De rijen die ik in school_afdeling_id kan kiezen zouden dus afhankelijk zijn van de waarde in school_id;
- Staat in school_id 1? Dan zou ik in school_afdeling_id A of B kunnen kiezen.
- Staat in school_id 2? Dan zou ik in school_afdeling_id C of D kunnen kiezen.
Ik hoop dat mijn vraag een beetje duidelijk is.
Alvast bedankt!
id
school_id
afdeling_id
Gewijzigd op 06/09/2016 16:08:12 door - SanThe -
Edit:
Ik heb even een voorbeeldje gemaakt:
Hoe zorg ik er dus voor dat een INSERT query of UPDATE query in GEBRUIKER_SCHOOL wordt geweigerd wanneer de combinatie school_id en school_afdeling_id niet voortkomt in SCHOOL_AFDELING
Gewijzigd op 06/09/2016 16:23:56 door Lord Gaga
Code (php)
1
2
3
4
5
6
2
3
4
5
6
+------------------+
| afdelingen |
+------------------+
| afdeling_id (PK) |
| school_id (FK) |
+------------------+
| afdelingen |
+------------------+
| afdeling_id (PK) |
| school_id (FK) |
+------------------+
Gewijzigd op 06/09/2016 17:09:50 door Lord Gaga
Je zou ze (als in logistieke systemen) namelijk samen kunnen opslaan in één tabel met een self-referencing key. Laten we de combinatie van scholen plus schoolafdelingen bijvoorbeeld 'locaties' noemen:
Code (php)
1
2
3
4
5
6
2
3
4
5
6
+-----------------+
| locaties |
+-----------------+
| locatie_id (PK) |
| parent_id (FK) |
+-----------------+
| locaties |
+-----------------+
| locatie_id (PK) |
| parent_id (FK) |
+-----------------+
De parent_id is self-referencing en verwijst naar een locatie_id in dezelfde tabel. Bij een school zit je op het hoogste niveau in de hiërarchie en is de parent_id NULL. Bij een schoolafdeling verwijst de parent_id naar de locatie_id van een school. Volgens dit principe kun je eigenlijk alles rubriceren dat hiërarchisch bestaat uit hoofdcategorie > categorie > subcategorie > enzovoort.
Gewijzigd op 06/09/2016 17:17:19 door Ward van der Put
Misschien een makkelijk voorbeeldje; denk aan Zweinstein. Zweinstein is de school en Griffoendor, Huffelpuf, Ravenklauw en Zwadderich zijn de afdelingen.
Een gebruiker kan zich inschrijven bij de school, maar is nog in geen enkele afdeling gesorteerd. Na het sorteren behoort een gebruiker dus tot maximaal 1 afdeling.
Gewijzigd op 06/09/2016 17:20:16 door Lord Gaga
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
school
- id
- name
- etc. etc.
department
- id
- school_id
- title
department_link
- id
- department_id
- student_id
student
- id
- school_id
- name
- age
- etc
- id
- name
- etc. etc.
department
- id
- school_id
- title
department_link
- id
- department_id
- student_id
student
- id
- school_id
- name
- age
- etc
Een school, kan meerdere afdelingen hebben, maar een afdeling niet meer scholen. Vervolgens kan een student maar bij 1 school ingeschreven zijn, en hoeft niet perse al bij een afdeling geplaatst te zijn (verse student). Afdelingen kunnen echter meerdere studenten bevatten dus vandaar de koppeltabel.
De afdelingen zou je op kunnen halen vanuit de school d.m.v het school_id. Welke leerlingen er bij welke afdelingen zitten haal je uit de koppeltabel.
Gewijzigd op 06/09/2016 17:50:26 door Milo S
Je zou wellicht een if statement op kunnen nemen in je create of update query.
Gewijzigd op 06/09/2016 18:14:08 door Milo S
Dit lijkt mij in ieder geval de meest genormaliseerde en functionele versie van wat jij zou willen bewerkstelligen.
Gewijzigd op 06/09/2016 18:16:55 door Milo S
- gebruiker_id (PK)
- school_id (FK)
scholen:
- school_id (PK)
afdelingen:
- afdeling_id (PK)
- school_id (FK)
gebruikers2afdelingen:
- gebruiker_id (PK+FK)
- afdeling_id (FK)
De gebruiker moet lid kunnen zijn van meerdere scholen en per school lid van 1 afdeling.
Maar zoals ik begrijp is het niet mogelijk deze restricties op te leggen in de database zelf?
Edit:
Op dit moment heb ik het opgelost met een trigger die kijkt of de relatie bestaat, zo niet; wordt er een signal verstuurd.
Gewijzigd op 06/09/2016 20:26:28 door Lord Gaga