Open source .Net domotica application
Open source .Net domotica application
Hi all,
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
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
Last edited by erisan500 on Fri Aug 27, 2010 9:29 pm, edited 1 time in total.
Re: Open source .Net domotica application - Database Layout
Hi erisan, here are some things that I noticed studying your Database diagram.
The table EnergyCost doesn't seem to me to become extremely large. Therefor I would expect to see the EnergySupplier data in the same table.
n the other hand, the table EnergyDetail will be the table in which you gather a lot of data over the course of time. If so, it is vital to keep it as lean and compact as possible. The EDrate does not fit in this philosophy because it will be the same for every record of the same energyID. I would personally switch this to the Energy table.
Hope that I understood your post the right way and that you can use my input.
Kind regards,
Tiz
The table EnergyCost doesn't seem to me to become extremely large. Therefor I would expect to see the EnergySupplier data in the same table.
n the other hand, the table EnergyDetail will be the table in which you gather a lot of data over the course of time. If so, it is vital to keep it as lean and compact as possible. The EDrate does not fit in this philosophy because it will be the same for every record of the same energyID. I would personally switch this to the Energy table.
Hope that I understood your post the right way and that you can use my input.
Kind regards,
Tiz
Mathijs
Re: Open source .Net domotica application - Database Layout
Tiz,
many thanks for your very interesting comments. I agree with all the improvements you suggested and will implement them.
Maybe I'll also add a EnergyStatistics table in order to avoid continuously calculating various cost.
Greetings,
EriSan500
many thanks for your very interesting comments. I agree with all the improvements you suggested and will implement them.
Maybe I'll also add a EnergyStatistics table in order to avoid continuously calculating various cost.
Greetings,
EriSan500
Re: Open source .Net domotica application - Database Layout
A bit of topic but what are you working on? (URL?)
Re: Open source .Net domotica application - Database Layout
I'm building a .Net domotica application.
EriSan500
EriSan500
Re: Open source .Net domotica application - Database Layout
So this topic is regarding to this?
http://www.domoticaforum.eu/viewtopic.php?f=32&t=5273
http://www.domoticaforum.eu/viewtopic.php?f=32&t=5273
Re: Open source .Net domotica application - Database Layout
Correct.So this topic is regarding to this?
viewtopic.php?f=32&t=5273
Just to give you guys a preview of my web interface.
There still allot of work to do, but wanted to get some people's attention
Re: Open source .Net domotica application - Database Layout
OK tell us some more about it
VB.net/C# engine with ASP.net
supporting hardware hardware,
when available
etc.
VB.net/C# engine with ASP.net
supporting hardware hardware,
when available
etc.
http://www.bwired.nl Online Home, Domotica, Home Automation. Weblog. http://blog.bwired.nl
-
- Member
- Posts: 52
- Joined: Fri Oct 24, 2008 9:03 am
- Location: Netherlands
Re: Open source .Net domotica application - Database Layout
I think the initiative is good. For many of us it is interesting to know in what environment (computer/software/domotica/hardware interfacing etc...) your are developing. This gives us a good idea of possible help and tips. Any way I encourage all developements in this area.
Re: Open source .Net domotica application - Database Layout
Website is written in ASP.Net MVC 2 using VB.NetBwired wrote:OK tell us some more about it
VB.net/C# engine with ASP.net
Server application written in VB.Net
Database server MS SQL 2008
GUI frontend in VB.Net using WPF
Currently only Arduino, but others can be implemented depending on the help i get.Bwired wrote: supporting hardware hardware,
Again, depends on the help i can get...if this is getting a solo job, it will take a long time...Bwired wrote:when available
etc.
Greetings,
EriSan500
Re: Open source .Net domotica application - Database Layout
Just a heads up.
I completed the "weather box" for my home page:
Greetings,
EriSan500
I completed the "weather box" for my home page:
Greetings,
EriSan500
Re: Open source .Net domotica application
Ok,
for those of you wanting to see it live in action: http://vdbnet.net:8088 (spare me, this is only 10 days coding)
PS, sorry about the 8088 port-number, my ISP doesn't allow me to host sites from home
PS2, don't be shocked or disappointed if the site doesn't come up, I'm constantly building new stuff and publishing it.
Greetings,
EriSan500
for those of you wanting to see it live in action: http://vdbnet.net:8088 (spare me, this is only 10 days coding)
PS, sorry about the 8088 port-number, my ISP doesn't allow me to host sites from home
PS2, don't be shocked or disappointed if the site doesn't come up, I'm constantly building new stuff and publishing it.
Greetings,
EriSan500
Re: Open source .Net domotica application - Database Layout
Looks good, but am I missing Ajax?
Re: Open source .Net domotica application
Small steps at a time, want to have the basics working, then we'll pass at the beauty farm(spare me, this is only 10 days coding)