Open source .Net domotica application

Show or discuss your existing Home automation project here, so a detailed explanation!.....

Open source .Net domotica application

Postby erisan500 » Tue Aug 24, 2010 5:50 pm

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 8:29 pm, edited 1 time in total.
erisan500
Member
Member
 
Posts: 72
Joined: June 2009
Location: Belgium, Oppuurs

Re: Open source .Net domotica application - Database Layout

Postby Tiz » Tue Aug 24, 2010 7:11 pm

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
Tiz
Member
Member
 
Posts: 137
Joined: May 2009
Location: Netherlands

Re: Open source .Net domotica application - Database Layout

Postby erisan500 » Tue Aug 24, 2010 7:47 pm

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
erisan500
Member
Member
 
Posts: 72
Joined: June 2009
Location: Belgium, Oppuurs

Re: Open source .Net domotica application - Database Layout

Postby Mdamen » Wed Aug 25, 2010 12:34 am

A bit of topic but what are you working on? (URL?)
Mdamen
Forum Moderator
Forum Moderator
 
Posts: 409
Joined: November 2008
Location: Netherlands

Re: Open source .Net domotica application - Database Layout

Postby erisan500 » Wed Aug 25, 2010 11:21 am

I'm building a .Net domotica application.

EriSan500
erisan500
Member
Member
 
Posts: 72
Joined: June 2009
Location: Belgium, Oppuurs

Re: Open source .Net domotica application - Database Layout

Postby Bwired » Wed Aug 25, 2010 7:01 pm

So this topic is regarding to this?
viewtopic.php?f=32&t=5273
User avatar
Bwired
Administrator
Administrator
 
Posts: 4943
Joined: March 2006
Location: Netherlands

Re: Open source .Net domotica application - Database Layout

Postby erisan500 » Wed Aug 25, 2010 9:07 pm

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
erisan500
Member
Member
 
Posts: 72
Joined: June 2009
Location: Belgium, Oppuurs

Re: Open source .Net domotica application - Database Layout

Postby drmacchi » Wed Aug 25, 2010 9:10 pm

Good job !
drmacchi
Advanced Member
Advanced Member
 
Posts: 827
Joined: August 2007
Location: Italy

Re: Open source .Net domotica application - Database Layout

Postby Bwired » Wed Aug 25, 2010 10:38 pm

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
User avatar
Bwired
Administrator
Administrator
 
Posts: 4943
Joined: March 2006
Location: Netherlands

Re: Open source .Net domotica application - Database Layout

Postby dutchnomad » Thu Aug 26, 2010 8:04 am

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.
dutchnomad
Member
Member
 
Posts: 52
Joined: October 2008
Location: Netherlands

Re: Open source .Net domotica application - Database Layout

Postby erisan500 » Thu Aug 26, 2010 8:49 am

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: June 2009
Location: Belgium, Oppuurs

Re: Open source .Net domotica application - Database Layout

Postby erisan500 » Fri Aug 27, 2010 1:20 pm

Just a heads up.

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

Greetings,
EriSan500
erisan500
Member
Member
 
Posts: 72
Joined: June 2009
Location: Belgium, Oppuurs

Re: Open source .Net domotica application

Postby erisan500 » Fri Aug 27, 2010 8:03 pm

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
erisan500
Member
Member
 
Posts: 72
Joined: June 2009
Location: Belgium, Oppuurs

Re: Open source .Net domotica application - Database Layout

Postby Bwired » Fri Aug 27, 2010 8:39 pm

Looks good, but am I missing Ajax?
User avatar
Bwired
Administrator
Administrator
 
Posts: 4943
Joined: March 2006
Location: Netherlands

Re: Open source .Net domotica application

Postby erisan500 » Fri Aug 27, 2010 9:03 pm

(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:
erisan500
Member
Member
 
Posts: 72
Joined: June 2009
Location: Belgium, Oppuurs

Next

Return to Home Automation Projects

Who is online

Users browsing this forum: No registered users and 2 guests