design database for electrical meter data?

Question

Hi just wondering how to I have .csv "Time 'datetime' Voltage 'double'"file that record Voltage usage from electrical meter each every minutes and database to record the data just want to normalize the data should be in database ? should I have Meters table,Times table, usage or meters table Voltages table (double, time stamp)

csv file data

 meterID  timeDate         Voltages  
    M1    1/12/2014 5:58:00  117.5
    M1    1/12/2014 5:59:00  117.6
    M1    1/12/2014 6:00:00  117.5
    M1    1/12/2014 6:01:00  117.3

Answer 1

The data in your CSV file gives enough information about which meter shows what reading at what time. This table, I believe, should not be decomposed as the information that every row contains is comprehensive in itself. There is no point in creating a Times table here, as there are going to be a lot of times when readings will be taken, hence storing times in a lookup table would be waste of disk space.

If I was developing a real time system for your requirements, the table you have given would have been a part of a larger schema. I would create something like the following:

TABLE customer
( customerID                     INTEGER        PRIMARY KEY
, cust_name                      VARCHAR(100)   NOT NULL
, customer_billing_address       VARCHAR(200)   NOT NULL
, registration_date              DATETIME
, ...
, ... -- Any other columns that you may think appropriate
, ...)

TABLE meter
( meterID                        INTEGER        PRIMARY KEY
, customerID                     INTEGER
, meter_address                  VARCHAR(200)   NOT NULL
, start_date                     DATETIME
, ...
, ... -- Any other columns as per your requirements.
, ...
, FOREIGN KEY (customerID) REFERENCES customer(customerID)
-- ^^ This is because one customer may have multiple meters.
)

Now I will have a meterReading table taken from your question.

TABLE meterReading
( meterReadingID                 INTEGER        PRIMARY KEY
, meterID                        INTEGER        NOT NULL
, readingTime                    DATETIME       NOT NULL
, voltage                        NUMBER(18,2)   NOT NULL
, ... -- any other columns
)

Now if I want to see the total voltage eaten up by a meter in a month, as meter readings are incremental, i.e., they don't reset, I would simply write a query like the following:

SELECT meterID, MAX(voltage) - MIN(voltage) as consumption
  FROM meterReading
 WHERE readingTime BETWEEN TO_DATE('01-JAN-2014)
                       AND TO_DATE('31-JAN-2014) + 86399/86400;

The last + 86399/86400 was because of the following:

1. When we use only date values and not time information, most databases treat it as beginning of the day, e.g., 31-JAN-2014 00:00:00.

2. There are 86400 seconds in a day. So, + 86399/86400 adds one whole day minus one second. So, it is just one second before 01-FEB-2014.

Next, if I want to see the list of customers and how much power they consume in a month, I would simply write the following:

  SELECT c.cust_name, SUM(mr.consumption) AS total_consumption
    FROM customer c INNER JOIN meter m
         ON c.customerID = m.customerID
         INNER JOIN (SELECT meterID
                              , MAX(voltage) - MIN(voltage) as consumption
                       FROM meterReading
                      WHERE readingTime BETWEEN TO_DATE('01-JAN-2014)
                                            AND TO_DATE('31-JAN-2014) 
                                                + 86399/86400) mr
         ON mr.meterID = m.meterID
GROUP BY c.cust_name
ORDER BY SUM(mr.consumption);