Page 1 of 3

Open source .Net domotica application

Posted: Tue Aug 24, 2010 6:50 pm
by erisan500
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:
Image

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

Re: Open source .Net domotica application - Database Layout

Posted: Tue Aug 24, 2010 8:11 pm
by Tiz
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

Re: Open source .Net domotica application - Database Layout

Posted: Tue Aug 24, 2010 8:47 pm
by erisan500
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

Re: Open source .Net domotica application - Database Layout

Posted: Wed Aug 25, 2010 1:34 am
by Mdamen
A bit of topic but what are you working on? (URL?)

Re: Open source .Net domotica application - Database Layout

Posted: Wed Aug 25, 2010 12:21 pm
by erisan500
I'm building a .Net domotica application.

EriSan500

Re: Open source .Net domotica application - Database Layout

Posted: Wed Aug 25, 2010 8:01 pm
by Bwired
So this topic is regarding to this?
http://www.domoticaforum.eu/viewtopic.php?f=32&t=5273

Re: Open source .Net domotica application - Database Layout

Posted: Wed Aug 25, 2010 10:07 pm
by erisan500
So this topic is regarding to this?
viewtopic.php?f=32&t=5273
Correct.

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 8)

Image

Re: Open source .Net domotica application - Database Layout

Posted: Wed Aug 25, 2010 10:10 pm
by drmacchi
Good job !

Re: Open source .Net domotica application - Database Layout

Posted: Wed Aug 25, 2010 11:38 pm
by Bwired
OK tell us some more about it
VB.net/C# engine with ASP.net
supporting hardware hardware,
when available
etc.

Re: Open source .Net domotica application - Database Layout

Posted: Thu Aug 26, 2010 9:04 am
by dutchnomad
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

Posted: Thu Aug 26, 2010 9:49 am
by erisan500
Bwired wrote:OK tell us some more about it
VB.net/C# engine with ASP.net
Website is written in ASP.Net MVC 2 using VB.Net
Server application written in VB.Net
Database server MS SQL 2008
GUI frontend in VB.Net using WPF
Bwired wrote: supporting hardware hardware,
Currently only Arduino, but others can be implemented depending on the help i get.
Bwired wrote:when available
etc.
Again, depends on the help i can get...if this is getting a solo job, it will take a long time...

Greetings,
EriSan500

Re: Open source .Net domotica application - Database Layout

Posted: Fri Aug 27, 2010 2:20 pm
by erisan500
Just a heads up.

I completed the "weather box" for my home page:
Image

Greetings,
EriSan500

Re: Open source .Net domotica application

Posted: Fri Aug 27, 2010 9:03 pm
by erisan500
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 :roll:
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

Posted: Fri Aug 27, 2010 9:39 pm
by Bwired
Looks good, but am I missing Ajax?

Re: Open source .Net domotica application

Posted: Fri Aug 27, 2010 10:03 pm
by erisan500
(spare me, this is only 10 days coding)
Small steps at a time, want to have the basics working, then we'll pass at the beauty farm :wink: