Looking for some info/thoughts/tips/suggestions on the database layout for the energy logging. In this case, energy means electricity/gas/water.
One record will be created for every hour, and every x secs data gets updated.
Bellow is an initial draft of what I came up with:
Lemme describe the 4 tables:
Energy
id(int): auto id
EName(varchar10): Short name (ex.: ele,gas,wat)
EDescription(varchar50): Long description (ex.: Electricity, Gas, Water)
EType: Needs to be removed
EUnit(varchar10): Unit of the energy (ex.: kWh, m³, L)
EnergyDetail
id(int): auto id
EID(int): Energy id
EDDate(datetime): date and time of the counter (time always round hours ex.: 17:00)
EDValueLow(decimal): pulses received for low cost period (summed per hour)
EDValueHigh(decimal): pulses received for high cost period (summed per hour)
EDCounterLow(decimal): counter for low cost period
EDCounterHigh(decimal): counter for high cost period
EDLastValue(decimal): last pulses received
EDRate(integer): how often do these pulse come in (used to calculate current consumption)
EDDateUpdate(datetime): last time we received pulses
EnergySupplier
id(int): auto id
ESName(varchar): Name of the energy supplier
EID(int): Energy id
(other fields like address, tel etc can be added)
EnergyCost
id(int): auto id
EID(int): Energy id
ECLow(decimal): Cost of energy for low cost period
ECHigh(decimal): Cost of energy for high cost period
ESID(int): EnergySupplier id
ECActive(bit): Indicate if this cost is active
Thanks in advance,
Greetings,
EriSan500
