Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2006
    Posts
    5

    Design for Energy Meters

    Hello All. I need suggestions/comments on an efficient design of a database that will be in MySQL and will capture/monitor energy meters. Here is my current idea:

    Building Table:
    BID
    Name
    Desc
    Type

    WaterMeter Table:
    WMID
    BID
    Reading
    TimeStamp

    ElectricMeter Table:
    same as water w/ EMID instead

    and then here is where I get confused because these readings will happen every 5min by a cron/perl script parsing xml from the meter and dumping into DB. I need to do weekly monthly yearly reports blablabla. So:

    WeeklyWM Table:
    WMID
    Mon
    Tue
    Wed
    ...
    Sun

    MonthlyWM Table:
    Week 1
    Week 2
    Week 3
    Week 4

    YearlyWM Table
    Jan
    Feb
    ...
    Dec

    Hist
    Inserts Year every Year

    This seems very inefficient. DOes anyone have better sugestions?

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Since the Water Meter and the Electric Meter are essentially identical, why not use ONE Meter table instead of two with a flag to identify the meter type ('W' or 'E' for instance) for a simpler design?

    This way, if you ever needed to add a different meter type, you could easily do so.


    And, it will probably make for simpler queries and reports.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Jul 2006
    Posts
    5

    Duh

    Thats for pointing that out. I should have thought of that. As far as capturing data to do perform weekly/monthly/yearly reports, is the only way to do this what i put above? Different tables for different time periods?

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    There are a couple of basic approaches you can follow.

    The first is to simply store your data with a timestamp field, and then summarize it as needed for your reports, using aggregate queries.

    The second approach is as you've defined - where you add tables to store summarized data.

    How you approach this issue depends to a great extent, how you will be running your reports.

    Your approach works best for situations where you will be running your reports often. It isolates your data storage from your reporting functions; you update the reporting data periodically, and it is accessed as a "snapshot."

    It is NOT current data, however.

    If you generate your reports not that often, it can be more effecient to simply generate the aggregate (summed/grouped) data as needed, when needed. However, summarizing data IS more time consuming (and server processor intensive) than simply viewing pre-summarized data.

    Really, your chosen design will be a trade-off between storage size and processing time. If you think you may be storage constrained, you may wish to summarize the data on an Ad-Hoc basis. If you have plenty of disk storage, and you don't need to view "live" summarized data often, it may be better to summarize your data during off hours and update summary tables for later reporting.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •