Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
If a parking lot can have different price tiers then you'd need a ParkingSpaceGroup field in ParkingLotSchedule and ParkingLotId+ParkingSpaceGroup would be the unique key. Seems like it might also be useful to track the number of parking spaces in each group/lot. –  Brad Mace Oct 20 '12 at 3:47
1  
To add to this, you may want to have an intermediate mapping table which would contain the ID of the lot and the ID of the schedule and you'd have 1 to many lots to schedules. This way you can be flexible and still be able to query how you want. –  Alf47 Oct 20 '12 at 5:23
    
If 2 hours costs $5 but 3 hours costs $7, then never mind the database, what is the answer to 'what does parking lot A cost right now?', other than 'it depends how long you're staying' ? –  AakashM Oct 22 '12 at 9:18

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.