I am using MS Sql Server and my system contains power usage data in one table as timestamp and values for KW(two columns). This data is collected at 1 hour interval.
At the end of the month I need to calculate on peak and off peak usage. The definition of on peak and off peak will depend on the contract with the utility company. So for example utility 'x' could have
- Monday to Friday – 7:00 am to 9:00 pm is on peak and 9:00 pm to 7:00 am is off peak
- Saturday – 9:00 am to 3:00 pm is on peak and 3:00 pm to 9:00 am is off peak
- Sunday – All day is off peak
- Stat Holiday – All day is off peak
Utility 'y' would have a different schedule for on peak and off peak times
I am having trouble modelling the week of the day and corresponding time in a manner that would make it easier to select data for on peak and off peak from my usage table. I am pretty sure I would need a lookup table for Stat Holidays. My parent table would be the utility table.
Option #1 : A flat table with many columns – MondayOnPeakStart, MondayOnPeakEnd, MondayOffPeakStart, MondayOffPeakEnd, TuesdayOnPeakStart …..
The select from the usage table would be pretty ugly.
Option # 2 : A normalized table structure. This is where I am stuck and cannot decide how to structure the hierarchy.
For each utility company, I have two types – onPeak and offPeak and for each of these types I have a schedule which contains days of week and start and end times. I will get multiple rows for on peak and I am not sure how will I use it in the usage table select clause.
Option # 3: Something I haven't thought of.
Best Answer
Is the evaluation of usage by period the only purpose for which you're storing these data?
If yes, then it can make sense to store usages differently for peak and off-peak time. This makes querying the things you describe trivial.
If no, it's almost certainly better to store all usage data in the obvious, non-distinguishing way, simply by time and value. You can extract patterns of use by writing boolean conditions in your queries as Robert Harvey suggests, or by hardcoding the definition of "peak" into your application logic, or even by storing them into another table. But for any other purpose than asking exactly this question, dealing with data in a format custom-tailored for another purpose would be a nightmare.