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?

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.

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?

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.