Come modellare le tabelle del database per calcolare i dati di utilizzo di picco e fuori picco

La valutazione dell'uso è in funzione dello scopo solo per il quale stai memorizzando questi dati?

Se sì, allora può avere senso memorizzare gli usi in modo diverso per il tempo di punta e non di punta. Questo rende l'interrogazione delle cose che descrivi banale.

Se no, è quasi certamente meglio memorizzare tutti i dati di utilizzo in modo ovvio e non distintivo, semplicemente in base al tempo e al valore. È possibile estrarre i pattern di utilizzo scrivendo le condizioni booleane nelle query come suggerito da Robert Harvey, oppure codificando con hardcoding la definizione di "peak" nella logica dell'applicazione, o anche memorizzandoli in un'altra tabella. Ma per qualsiasi altro scopo che chiedere esattamente questa domanda, trattare i dati in un formato personalizzato su misura per un altro scopo sarebbe un incubo.

Qualcosa che mi colpisce subito è che stai modellando logica aziendale nel tuo database transazionale. Dovresti riconsiderare il tuo approccio immediatamente; questo non causerà nient'altro che problemi a lungo termine. Il fatto è che il tuo database non dovrebbe sapere o preoccuparsi di quale piano viene addebitato; il lavoro di un database consiste nel memorizzare dati .

La tua tabella dovrebbe apparire in questo modo:

PowerUsageEntryID | ID cliente | Ora | WattsUsed

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

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

Un'altra tabella tiene traccia dei diversi piani di fatturazione. Potresti aver bisogno di diversi di questi per i diversi tipi di programmi di utilità, va bene. Potrebbe anche avere senso solo dare a ciascuna utility la propria classe di pianificazione e ottenere particolari da una configurazione di una tabella di database.

PlanId | StartTime | EndTime | BillingRate | UtilityID

Un'altra tabella per i clienti ...

ID cliente | UtilityId | CustomerName

L'applicazione applica le diverse percentuali di fatturazione alle voci di utilizzo per determinare il costo.

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

Il numero di tabelle delle regole che ti serviranno per definire cosa è attivo o non attivo dipende dal numero di diversi tipi di regole che hai. Vuoi considerare quali saranno le tue chiavi primarie delle regole. Ogni distinto set di attributi della chiave primaria dovrebbe essere nella sua tabella ( questa è la normalizzazione )

Nel tuo esempio, hai due tipi di regole:

1. Giorno della settimana + Ora del giorno (copre le situazioni da 1 a 3)
2. Data (copre la situazione 4)

Dato che hai più di un tipo di regola, devi anche pensare a come le regole interagiscono, vale a dire che ha la precedenza. Pertanto, le tue tabelle di regole assumeranno entrambe questo modulo:

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
)

Il numero e la natura dei tuoi attributi PK in ogni tabella di regole dipenderanno dal modo in cui le tue regole funzionano. L'attributo priority ti dice quale regola "vince" nel caso ci sia una disputa tra regole sovrapposte. Nel tuo esempio, le festività statali superano il giorno della settimana più l'orario del giorno.

La struttura di cui sopra potrebbe essere eccessivamente semplificata. Potrebbe essere necessario aggiungere un intervallo di date valido per la regola, ad esempio, soprattutto se è necessario tornare ai dati storici per ricalcolare l'utilizzo massimo.

Potresti considerare un approccio più generale, quando una di queste aziende pensa a tariffe a tre livelli: giorno della settimana (giorno), giorno della settimana (notte), fine settimana.

Se sei sicuro che il pattern sarà sempre settimanale e la granularità migliore sarà al minuto, puoi considerare di memorizzare:

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

Per quanto riguarda la velocità, è necessario unirsi per trovare il più recente start_minute_of_week nella tabella per le tariffe sul contratto del cliente.

Per le query di massa, potrebbe essere più efficiente materializzare una tabella di:

company
minute_of_week
tariff

... unico per azienda e minuto_di_settimana, che consente un equijoin.

Suggerirei di dover modellare la logica nel livello aziendale (Suggerimento: deve essere astratto e a livello di implementazione le regole possono essere implementate come stored procedure o quali sono le vostre esigenze) e DB modello come tabella separata per regole, tariffe, e quindi utilizzare la registrazione.

Questa tabella sarà simile a:

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} 

Esempio di dati dalle regole:

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

e così via