count function gebruiken in constraint
Ik heb een database in mysql en heb 2 tabellen.
De namen van de tabellen zijn rit en voertuig.
In tabel rit heb ik een kolom staan stoelnummer en in tabel voertuig staat een kolom aantalzitplaatsen.
Het aantal stoelnummers wil ik d.m.v. count function bij elkaar optellen.
De bedoeling is dat het aantal stoelnummers gelijk of kleiner is dan het aantalzitplaatsen. Als je voertuig 6 zitplaatsen heeft kun je niet 8 mensen meenemen.
ik wil een constraint schrijven die checkt of het aantalzitplaatsen en het aantal stoelnummers niet groter zijn.
mijn vraag is: kan ik een count function in een constraint zetten? zo ja, heeft iemand een voorbeeld?
of weet iemand een andere manier hoe ik dit kan checken?
alvast bedankt
Bij dit alles loont het de moeite om database-transacties te gebruiken. Ik hoop voor jou dat je jouw database ook zo hebt opgezet.
Wat je ook zou kunnen overwegen is dat je, zodra een rit ingeroosterd is, de zitplaatsen-tabel vult met lege zitplaatsen (geef deze dus een status). Dan hoef je niet elke keer het aantal passagiers te vergelijken met een (verder nogal ongerelateerde) waarde uit een andere tabel. Je kunt als het ware de dimensie "zitplaatsen" van een rit opspannen door gewoon een X aantal records aan te maken.
"Het meest geschikte moment om te kijken of er genoeg stoelplaatsen zijn is het moment waarop je (meer) stoelplaatsen reserveert waarbij je moet controleren of je daarmee niet over de capaciteit heengaat"
is het beter om dan een status tabel aan te maken? dus een tabel erbij?
Nee, geen spaghetti bouwen en een constraint is niet de manier om dit op te lossen.
In principe is er in het juiste datamodel niks te counten. 1 voertuig record en 1 rit record waarbij bijvoorbeeld het tijdstip van/tot de rit uniek koppelt aan het voertuig.
Je kan dit beter oplossen met (BEFORE INSERT en BEFORE UPDATE) trigger op de tabel RIT, een trigger die in de voertuig tabel kijkt, voordat de rit feitelijk wordt opgeslagen, en valideert of dit overeenkomt of kleiner is dan de beschikbare zitplaatsen in het voertuig. Anders exception en foutmelding teveel stoelen voor dit voertuig of zoiets.
In de tabel rit geen kolommen maken met stoelnummers en ook geen records aanmaken met stoel 1 t/m stoel x. In tabel rit alleen een attribuut (kolom) opnemen met aantal personen. Bij de insert of update van dit aantal personen wordt de trigger afgevuurd en die controleert of het voertuig dit toelaat. Leuke Business Logic oplossing in de database!
Het geheel kan inderdaad in één transactie (maar hoeft niet, alleen rit wordt immers maar gevuld). Op de tabel rit moet je dan wel een constraint bouwen die controleert of er geen conflict is met dit voertuig_id op dit tijdstip/periode of wellicht doe je dat interactief wanneer de rit-dispatcher de web pagina invult en een voertuig aanwijst.
Gewijzigd op 11/05/2017 21:38:07 door Aad B
De trigger heb ik nu zo:
CREATE Trigger Zitplaatsen BEFORE INSERT on Rit
FOR EACH ROW
Begin
select rit.aantalPersonen, voertuig.AantalZitPlaatsen
from Rit rit, Voertuig voertuig
where rit.VoertuigNr = voertuig.VoertuigNr
and rit.aantalPersonen <= voertuig.AantalZitPlaatsen
end;
Is dit zo de bedoeling?
Ik ga er even van uit dat het voertuig gekozen is en beschikbaar voor het tijdstip, dus VoertuigNr is al bekend en wordt aangeboden voor de insert op rit:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
CREATE TRIGGER verifyZitplaatsen BEFORE INSERT ON rit
FOR EACH ROW
BEGIN
IF NEW.aantalPersonen > (
SELECT AantalZitPlaatsen
FROM voertuig
WHERE VoertuigNr = NEW.VoertuigNr)
THEN
set msg = 'Fout: Te weinig zitplaatsen';
signal sqlstate '45000' set message_text = msg;
END IF;
END;
FOR EACH ROW
BEGIN
IF NEW.aantalPersonen > (
SELECT AantalZitPlaatsen
FROM voertuig
WHERE VoertuigNr = NEW.VoertuigNr)
THEN
set msg = 'Fout: Te weinig zitplaatsen';
signal sqlstate '45000' set message_text = msg;
END IF;
END;
Uiteraard moet je dan in php deze error afhandelen en tonen aan de dispatcher.
Als je met transacties werkt kan je het gekozen voertuig even locken gedurende de transactie met SELECT ... LOCK IN SHARE MODE.
Gewijzigd op 12/05/2017 14:43:28 door Aad B
- met een controle voor het invullen van het formulier voor het boeken van zitplaatsen,
- en nogmaals bij het daadwerkelijk verwerken van het formulier
Wanneer je met een groep reist lijkt het mij ook wel handig wanneer deze reisgenoten (zoveel mogelijk) naast elkaar kunnen zitten. Het reserveren van specifieke zitplaatsen (net zoals in een bioscoop of vliegtuig ofzo) kan een gewenste aanvulling zijn.
Gewijzigd op 13/05/2017 12:36:08 door Aad B