I’ve been stuck for a while now with a problem that is more logical than technical but maybe someone in here has solved this before in any other context.
I created a TRIPS table, where users can schedule delivery trips. When scheduling a trip they select a START DATE, END DATE, a VEHICLE and a DRIVER.
What I have been trying to solve is that, once selected the START DATE and the END DATE, how could I validate that both the VEHICLE and the DRIVER appearing in the enum list of each field are just the ones that are available during the period of time selected.
At first I tried using a validation for the START TIME (should be greater than the END TIME of the last trip) and a validation for the end time (should be earlier than the START DATE of the next trip. However, this does not solve all the use cases because the user could be trying to schedule a trip in the far future or in between trips.
Any idea on how to solve this kind of problems?
Thanks in advance