Say a parking lot has the following hours and associated costs...
Mon-Fri 7am - 5pm parking is $5/2hrs
Mon-Fri 5pm - 7am parking is $1/hr
Sat and Sun - 12am - 12pm parking is free
How would you model this data such that I can ask questions like "What does Parking Lot A cost right now?"
I'm not sure at the moment, but it seems like a model should accomodate multiple prices for certain times of the day. For example...
Mon-Fri 7am - 5pm parking is $5/2hrs or $7/3hrs
It should also be easy to modify if, for example, a lot's cost ever changes.
Here's a general idea I had so far...
ParkingLot -
ParkingLotId
Name
Address
ParkingLotSchedule -
ParkingLotId
DayOfTheWeek
StartTime
EndTime
Cost
Then I could basically write a query something like...
select cost from parkingLotSchedule where parkingLotId = currentLotId and dayOfTheWeek = today and currentTime between startTime and endTime
This doesn't really take into account the possibility of multiple prices for certain times (granted I'm not 100% sure that's necessary yet). It also seems like there could potentially be a lot of records for a single lot.
Is there a better way to do this? If so, what would a model look like?