Review - Table Design - Meter Reading / Adjustments (1 Viewer)

Cosmos75

Registered User.
Local time
Yesterday, 20:23
Joined
Apr 22, 2002
Messages
1,281
I am working on a database to keep track of meter readings from several meters. Am at the table design stage and have come up with two seperate table designs but am wondering what would be best.

Each individual 'Meter' tracks more than one 'Fuel'. All of the regular meter readings should occur on the last day of the the month. The difference between the current month's reading and the reading from the previous month is the usage for the current month.

Though rare, there are occasions when there could be adjustments to the meter. A meter may be reset to 0 or to any other number due to a number of issues (replacement, maintenance).

The usage for a Meter-Fuel combination calculated by the current meter reading & previuos meter reading is assigned to current readings' date, As readings should onlt be taken at the end of the month, all other readings should be adjustments ONLY!

Here is my one version;

tblMeter
MeterID (PK)
strMeterDesc

tblFuel
FuelID(PK)
strFuelDesc

tblMeterReadings
MeterReadingID (PK)
dtMeterReading
MeterID (FK)
FuelID (FK)
dblMeterReading

tblMeterReadings
MeterReadingID (PK)
dtMeterReading
MeterID (FK)
FuelID (FK)
dblMeterReading

tblMeterAdjustments
MeterAdjustmentID (PK)
dtMeterAdjustment
MeterID (FK)
FuelID (FK)
dblMeterReadingOLD
dblMeterReadingNEW

My other idea was to incorporate meter adjustments into tblMeterReadings and not have a sepereate table for meter adjustments

tblMeterReadings
MeterReadingID (PK)
dtMeterReading
MeterID (FK)
FuelID (FK)
dblMeterReading
blnAdjustment

The I would create a multi-field index of dbMeterReading, MeterID, FuelID, blnAdjustment.

So data with an adjustment may look like this
Code:
[b]dtMeterReading, MeterID, FuelID, dblMeterReading, blnAdjustment[/b]
01/31/2005,     1,       1,      900,             False
[color=red]02/15/2005,     1,       1,      1000,            False
02/15/2005,     1,       1,      0,            	  [b]True[/b][/color]
02/31/2005,     1,       1,      100 ,            False
I think that the second version is the way to go, since I would still have to create a query to join data from both the reading and adjustment tables in the first design that would look like the data above.

Here what I consider to be the drawbacks to either table structure

Version 1
  • The first version requires more complicated queries to calculate the difference between meter readings.
Version 2
  • The second version will require some coding/querying to make sure that there are always two adjustment records for one date and determining which is the pre-adjustment and post-adjustment meter reading since it may not be true that the meter is reset to zero; it may even be set at number higher than the pre-adjectment reading.

Any thoughts on which design is better? Or any problems I could run into later with designing forms/queries/reports that perhaps I have not thought through?

THANKS!
 
Last edited:

Cosmos75

Registered User.
Local time
Yesterday, 20:23
Joined
Apr 22, 2002
Messages
1,281
Thought it might help to show the SQL for the queries to clarify what I am after.

Version 1
Code:
[i][B][COLOR=Blue]qryPrevReadings[/COLOR][/B][/i]
SELECT MeterReadingID, dtMeterReading, MeterID, FuelID, dblMeterReading
FROM tblMeterReadings;

UNION SELECT MeterAdjustmentID, dtMeterAdjustment, MeterID, 
FuelID, dblMeterReadingOLD
FROM tblMeterAdjustments;

UNION SELECT MeterAdjustmentID, dtMeterAdjustment, MeterID, 
FuelID, dblMeterReadingNEW
FROM tblMeterAdjustments
ORDER BY dtMeterReading, MeterID, FuelID, dblMeterReading;

[i][B][COLOR=Blue]qryMeterDiff[/COLOR][/B][/i]
SELECT MeterID, FuelID, dtMeterReading, dblMeterReading, 
[COLOR=Red]Nz((Select Max(dblMeterReading) FROM qryPrevReadings 
WHERE MeterID = qryMeterReadings.MeterID 
And FuelID = qryMeterReadings.FuelID 
And dtMeterReading < qryMeterReadings.dtMeterReading 
And qryMeterReadings.dblMeterReading > dblMeterReading),[dblMeterReading]) 
AS [B]dblMeterReadingPREV[/B][/COLOR], [dblMeterReading]-[MeterReadingPREV] AS [Usage]
FROM qryMeterReadings;

Version 2
Code:
[i][B][COLOR=Blue]qryMeterDiff[/COLOR][/B][/i]
SELECT MeterID, FuelID, dtMeterReading, blnAdjustment, dblMeterReading, 
[COLOR=Red]Nz((Select Max(dblMeterReading) FROM tblMeterReadings 
WHERE MeterID = tblMeterReadings.MeterID 
And FuelID = tblMeterReadings.FuelID 
And dtMeterReading < tblMeterReadings.dtMeterReading 
And tblMeterReadings.dblMeterReading > dblMeterReading),[dblMeterReading])
AS [B]dblMeterReadingPREV[/B][/COLOR], [dblMeterReading]-[MeterReadingPREV] AS [Usage]
FROM tblMeterReadings
ORDER BY MeterID, FuelID, dtMeterReading, blnAdjustment DESC;

The calculated field, Usage, in qryMeterDiff is what I am after. There is probably a better way to do this... :eek:

The results should look like this (with the adjustments in red);
Code:
[b]dtMeterReading, MeterID, FuelID, dblMeterReading, dlbMeterReading[B][COLOR=Red]Prev[/COLOR][/B], [COLOR=Blue]Usage[/COLOR][/b]
01/31/2005,     1,       1,      900,             900,                 [COLOR=Blue]0[/COLOR]
02/31/2005,     1,       1,      1000 ,           900,                 [COLOR=Blue]100[/COLOR]
[color=red]03/15/2005,     1,       1,      1010,            1000,                10
03/15/2005,     1,       1,      500,             500,                 0[/color]
03/31/2005,     1,       1,      900,             500,                 [COLOR=Blue]400[/COLOR]
04/31/2005,     1,       1,      1100 ,           900,                 [COLOR=Blue]200[/COLOR]
04/31/2005,     1,       1,      1375 ,           1100,                [COLOR=Blue]275[/COLOR]
 
Last edited:

Users who are viewing this thread

Top Bottom