1

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.

  • All you need is the data table you described in the first paragraph of your question. You can write any query against that usage table that you need, including days, days of the week months, year over year by day, and so on. – Robert Harvey Apr 20 '16 at 5:40
  • That is true. But in my where clause I would need to get the time intervals for on peak and off peak usage. So I am wondering what is the best way to model the on peak and off peak configuration so that I can easily use them in the where clause of my usage table. – scorpion05 Apr 20 '16 at 16:42
  • 4
    The columns in your configuration table should be: ConfigurationID, ContractID, DayOfWeek, PeakStartTime, PeakDuration, OffPeakStartTime, OffPeakDuration – Robert Harvey Apr 20 '16 at 16:48
  • Cool. This is much better than what I was thinking - putting start time and duration instead of putting start time and end time. – scorpion05 Apr 20 '16 at 21:41
  • I might suggest further normalising the periods by replacing PeakStartTime, PeakDuration, OffPeakStartTime and OffPeakDuration with PeriodType(Peak, Offpeak, etc), PeriodStartTime, PeriodDuration. This would allow you to add other period types to the day without having to add new columns. Will also support more than one period of each type in a day, for example an off-peak period at the beginning of the day, on-peak during the middle of the day, then another off-peak period at the end of the day. – Sean Burton Jan 11 '18 at 12:53
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.

  • Some utility companies have on peak and off peak rates and others have flat rates throughout. I cannot store the usage differently as the definition of on peak and off peak may change for the same company. Application logic is definitely one way but i can see it becoming cumbersome as I would need to comb through 1440 * 31 (per minute data) records to compute for the month. I am looking at a way to do this in the database itself via SQL query. – scorpion05 Apr 20 '16 at 16:45
  • 1
1

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...
    }
}
-1

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.

-1

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.

  • 2
    There seem to be a lot of down votes on answers to this question without any explanation. – David Aldridge Apr 13 '18 at 14:28
-1

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

protected by gnat Apr 11 '18 at 6:52

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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