How would I build a data model for parking lot hours and respective parking fees?

Question

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?

Answer 1

Depending on how many parking lots you're operating, I wouldn't be too worried about how many records you have to manage. It's not like this data would be fast changing, since you'd probably have to get signs printed up with the prices marked and so on. Still, assuming that you have many lots (many of which have the same pricing) and that you want to minimize data maintenance, you could build a price schedule and map lots to the schedule.

Consider something like this:

ParkingLot -
    ParkingLotId
    Name
    Address

PriceSchedule -
    PriceScheduleId
    Description

PriceScheduleDetail - 
    PriceScheduleDetailId
    PriceScheduleId
    DayOfTheWeek
    StartTime
    EndTime
    DollarsPerUnit
    MinutesPerUnit
    MinutesMinimumStay

ParkingLotPrice -
    ParkingLotId
    PriceScheduleId
    EffectiveDate
    ExpiryDate

Note that the PriceScheduleDetail table allows you to keep two or more versions of a price for a given time of day range.

Note also that the ParkingLotPrice table uses effective and expiry dates to link parking lots to a price table by date range. This allows you to set up a price change in advance so you don't need to load new data in the middle of the night when a price change takes effect.

You're queries for pricing are going to have to find the version of the price that applies based on the minimum stay length, which is much more complicated than just "What is the price right now?" since it will depend on how long a stay is and which is the best price that the customer qualifies for. Also, the price a customer pays will depend on how long their stay is and it may cross one or more price point boundaries.