How to build a data model for parking lot hours and respective parking fees

database-design

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?

Best Answer

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.

Related Solutions

Data Model for Summarizing Student Info in SQL Server

Calculate those counts on the fly.

For most data sets of this nature, I would imagine calculating the count of tardiness events would be relatively cheap. How many rows do you expect there to be in the long run in your students or attendance tables? Probably on the order of tens of thousands at most. Contrast that to calculating an account balance from its transaction history in database for a large bank with billions of transactions. In this case I would consider persisting a summary of these aggregations somewhere.

If you wanted to pre-calculate this data anyway, I would summarize the count of tardiness events per student per year in an indexed view as suggested in the top answer here.

For example:

CREATE VIEW dbo.tardiness_summary
WITH SCHEMABINDING
AS
SELECT
     student_id
   , year
   , COUNT_BIG(*) AS tardy_count
FROM dbo.attendance
GROUP BY 
     student_id
   , year
;

CREATE UNIQUE CLUSTERED INDEX IX_tardiness_summary
ON dbo.tardiness_summary (
     student_id
   , year
;

This is not a flexible approach, however, as this view is now schema-bound to the base tables and thus any modifications to either the view or the table will require rebuilding the view. Indexed views also have many restrictions on how they may be created or queried. Their advantage is that they guarantee the summary table will stay in sync with its sources because the database engine is now doing this work for you.

Mongodb – Would appreciate some suggestions on how I should model this data

NOTE to people with itchy downvote fingers: I know that OP has asked about MongoDB and the answer that follows is RDBMS. However, if you check out the comments you'll see that I did ask why MongoDB and OP's answer is a presumption of necessity due to performance. Since nobody has come forth with a Mongo-centric answer in four days, I am going to offer my suggestion, which is to let RDBMS do what it's good at.

Raladical, as I noted in my comments to your question, I think that what you are trying to build is perfectly suited to the relational data model. Here is a quick data model sketch:

Data Model Sketch

This model makes the following assumptions:

Users can have exactly one library.
Users can have many playlists.
Songs can appear in any number of playlists.
Users can only put songs in a playlist if it's already in their library (Note that there is an easy way around this if that is not your intention.)
Depending on the constraints you put on LIBRARY_ITEM and PLAYLIST_ITEM you can have the same song exactly once or several times in both the library and any given playlist.

You can also easily extend this model to normalize for things like ARTIST and possibly even location, if that makes sense in your application.

This model is normalized (3NF) and if you index it properly it will be very performant. If you really want MongoDB or if there is something else about your system that is well suited to non-relational then you can definitely do it that way. If not, you should think about RDBMS for this one.

Best Answer

Related Solutions

Data Model for Summarizing Student Info in SQL Server

Mongodb – Would appreciate some suggestions on how I should model this data

Related Question