Open source .Net domotica application

Show or discuss your existing Home automation project here, so a detailed explanation!.....
erisan500
Member
Member
Posts: 72
Joined: Thu Jun 18, 2009 10:20 pm
Location: Belgium, Oppuurs
Contact:

Open source .Net domotica application

Post 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
Last edited by erisan500 on Fri Aug 27, 2010 9:29 pm, edited 1 time in total.
Tiz
Member
Member
Posts: 146
Joined: Tue May 19, 2009 12:21 pm
Location: Netherlands

Re: Open source .Net domotica application - Database Layout

Post 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
Mathijs
erisan500
Member
Member
Posts: 72
Joined: Thu Jun 18, 2009 10:20 pm
Location: Belgium, Oppuurs
Contact:

Re: Open source .Net domotica application - Database Layout

Post 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
Mdamen
Forum Moderator
Forum Moderator
Posts: 390
Joined: Sat Nov 22, 2008 6:58 pm
Location: Netherlands
Contact:

Re: Open source .Net domotica application - Database Layout

Post by Mdamen »

A bit of topic but what are you working on? (URL?)
erisan500
Member
Member
Posts: 72
Joined: Thu Jun 18, 2009 10:20 pm
Location: Belgium, Oppuurs
Contact:

Re: Open source .Net domotica application - Database Layout

Post by erisan500 »

I'm building a .Net domotica application.

EriSan500
Bwired
Administrator
Administrator
Posts: 4704
Joined: Sat Mar 25, 2006 1:07 am
Location: Netherlands
Contact:

Re: Open source .Net domotica application - Database Layout

Post by Bwired »

So this topic is regarding to this?
http://www.domoticaforum.eu/viewtopic.php?f=32&t=5273
erisan500
Member
Member
Posts: 72
Joined: Thu Jun 18, 2009 10:20 pm
Location: Belgium, Oppuurs
Contact:

Re: Open source .Net domotica application - Database Layout

Post 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
drmacchi
Advanced Member
Advanced Member
Posts: 827
Joined: Wed Aug 08, 2007 5:48 pm
Location: Italy

Re: Open source .Net domotica application - Database Layout

Post by drmacchi »

Good job !
Bwired
Administrator
Administrator
Posts: 4704
Joined: Sat Mar 25, 2006 1:07 am
Location: Netherlands
Contact:

Re: Open source .Net domotica application - Database Layout

Post by Bwired »

OK tell us some more about it
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
dutchnomad
Member
Member
Posts: 52
Joined: Fri Oct 24, 2008 9:03 am
Location: Netherlands

Re: Open source .Net domotica application - Database Layout

Post 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.
erisan500
Member
Member
Posts: 72
Joined: Thu Jun 18, 2009 10:20 pm
Location: Belgium, Oppuurs
Contact:

Re: Open source .Net domotica application - Database Layout

Post 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
erisan500
Member
Member
Posts: 72
Joined: Thu Jun 18, 2009 10:20 pm
Location: Belgium, Oppuurs
Contact:

Re: Open source .Net domotica application - Database Layout

Post by erisan500 »

Just a heads up.

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

Greetings,
EriSan500
erisan500
Member
Member
Posts: 72
Joined: Thu Jun 18, 2009 10:20 pm
Location: Belgium, Oppuurs
Contact:

Re: Open source .Net domotica application

Post 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
Bwired
Administrator
Administrator
Posts: 4704
Joined: Sat Mar 25, 2006 1:07 am
Location: Netherlands
Contact:

Re: Open source .Net domotica application - Database Layout

Post by Bwired »

Looks good, but am I missing Ajax?
erisan500
Member
Member
Posts: 72
Joined: Thu Jun 18, 2009 10:20 pm
Location: Belgium, Oppuurs
Contact:

Re: Open source .Net domotica application

Post 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:
Post Reply

Return to “Home Automation Projects”