Return to Revisions

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