How to model the database tables for calculating on peak and off peak usage data

Question

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

1. Monday to Friday - 7:00 am to 9:00 pm is on peak and 9:00 pm to 7:00 am is off peak
2. Saturday - 9:00 am to 3:00 pm is on peak and 3:00 pm to 9:00 am is off peak
3. Sunday - All day is off peak
4. 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.

Answer 1

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.

Answer 2

Something that strike me as off right away is that you are modeling business logic in your transactional database. You should rethink your approach right away; this will cause nothing but problems for you in the long run. The fact is, your database should not know or care what plan this is being billed under; a database's job is to store data.

Your table should look something like this:

PowerUsageEntryID | CustomerID | Time | WattsUsed

1 | 55 | 5/5/2017 11:00 | 5000

2 | 76 | 5/5/2017 11:00 | 2155

Another table keeps track of the different billing plans. You may need several of these for the different kind of utilities schedules, that's okay. It might even make sense just to give each utility it's own schedule class, and get particulars from a config in lue of a database table.

PlanId | StartTime | EndTime | BillingRate | UtilityID

Another table for the customers...

CustomerID | UtilityId | CustomerName

The application applies the different billing rates to the usage entries to determine the cost.

class BillingClerk
{
    public decimal GetPowerBill(customerId, startDate, endDate)
    {
        decimal total = 0;
        var usageEntries = _repository.GetEntries(customerId, startDate, endDate); // Do the sql query to get the entries for the customer and translate them into a class

        foreach (entry in usageEntries) 
        {
              total += GetRate(entry) * entry.WattsUsed;
        }
        return total;
    }

    decimal GetRate(entry)
    {
        Customer customer = _customerRepository.GetCustomer(entry.customerId);
        IRateSchedule schedule = _scheduleActivator.GetSchedule(customer.UtilityId);             
        return schedule.GetRate(entry); // compare against time ect...
    }
}

Answer 3

The number of rule tables you're going to need for defining what is on or off peak will depend on the number of different types of rules you have. You want to consider what your rule primary keys will be. Every distinct set of primary key attributes should be in its own table (that's normalization)

In your example, you have two kinds of rules:

1. Day of Week + Time of Day (covers situations 1 through 3)
2. Date (covers situation 4)

Since you have more than one kind of rule, you also need to think about how the rules interact, i.e. which takes precedence. Therefore, your rule tables will each take on this form:

CREATE TABLE dbo.PEAK_RULE_X (
  [First_PK_Attribute]
, [Subsequent_PK_Attribute]... -- (As required)
, UtilityID INT -- FK to Utility whose rule this is.
, Priority  INT
, Peak      BIT
)

The number and nature of your PK attributes in each rule table will depend on how your rules work. The priority attribute tells you which rule "wins" in case there is a dispute between overlapping rules. In your example, stat holiday trumps day of week plus time of day.

The above structure may be oversimplified. You may need to add a rule effective date range, for example, especially if you have to be able to go back through historical data to recalculate peak usage.

Answer 4

You might consider a more general approach, for when one of these companies thinks up three-tier rates -- weekday(day), weekday(night), weekend.

If you are sure that the pattern is always going to be weekly, and the finest granularity is going to be by minute, then you might consider storing:

tariff (a key to the utility company's rate)
start_minute_of_week

Looking up the rate is a matter of joining to find the most recent start_minute_of_week in the table for the tariffs on the customer's contract.

For bulk queries, it might be more efficient to materialize a table of:

company
minute_of_week
tariff

... unique on company and minute_of_week, which allows an equijoin.

Answer 5

I would suggest that you must model the logic in business layer (Tip: must be abstract and at implementation level rules can be implemented as stored procedures or what ever suites your need) and DB model as separate table for rules, tariff, and then usage recording.

These table would look like:

Rule_Table(rule_id,start_time,end_time,day_id)
{Tip: day_id for defining day and give you flexibility to define complex rule) 
Tarrif(supplier_id,rule_id,rate) 
Usage(consumer_id,supplier_id, usage_start_time,usage_end_time)
{Tips: think if the recording interval changes for any supplier let's say every 30 minutes vs every hour} 

Example of data from rules:

For Monday 07 AM - 5PM:
(1, 0700,1700,MON)   
For every public holiday flat rate:
(2,ANY,ANY,ANY,PUBLIC)  

and so on