Breakfast table relations

[directorz]

Good morning experts,

I’m attempting to track various meter readings in a vehicle maintenance db. I currently have 5 tables laid out as follows:

TblVehicles
VehID – PK

TblMeter
MeterID – PK
Ref_VehID – FK to TblVehicles.VehID
Ref_MeterType – FK to TblMeterType.MeterType

TblMeterRead
ID – PK
Ref_MeterID – FK to TblMeter.MeterID
MeterRead
ReadDate

TblMeterType
MeterType – PK

TblWorkOrders
WorkOrderID – PK

I have not set a relationship yet for TblWorkOrders as I’m stuck. Here’s my logic thus far.
1. A vehicle can have many meters and can be of several types ie; hourmeter, odometer, a replacement meter for one that’s failed, etc.
2. A meter can have many readings

Here’s my problem. I need to use a work order to record services on a vehicle and be able to say, for example, “on August 25, I replaced a headlamp, changed the oil and repaired a tire. The hourmeter reading at the time was 550 AND the odometer reading at the time was 22,000.” How do I tie TblworkOrders into this picture in terms of its relationship to other table(s)? Do I need additional structure? Any assistance would be appreciated.

Thank you
Directorz

 

[willir]

DirectorZ

First, your design is pretty good.

A vehicle can have many meters and can be of several types ie; hourmeter, odometer, a replacement meter for one that’s failed

Does this mean you can bring in a vehicle, and it will have several meters, or have one meter one day and another meter another day?

If this is true, I suspect you want to focus on the meter and not the meter (for the database).

I have seen a similar situation where mobile computer devices are moved from vehicle to vehicle in a production device - swap defective device.

Consider...

TblWorkOrders
WorkOrderID – PK
VehID - fk to TblVehicles.VehID
MeterID - fk to TblMeter.MeterID
...and perhaps
StartDate
StartTime
EndDate
EndTime

...Now, if the detail work is associated with the meter, and not the "header" record, a tweak would be...

TblWorkOrders
WorkOrderID – PK
VehID - fk to TblVehicles.VehID
...and perhaps
StartDate
StartTime
EndDate
EndTime

TblWorkOrderDetail
WorkOrderDetailID - pk
WorkOrderID - fk to TblWorkOrders.WorkOrderID
MeterID - fk to TblMeter.MeterID

...Movin On

2. A meter can have many readings

If you are refering to ReadingHistory, I think you have already captured this with...

TblMeterRead
ID – PK
Ref_MeterID – FK to TblMeter.MeterID
MeterRead - not sure what this is
ReadDate

...Three other things
Roll Over.
I doubt is an odemeter will roll-over, but will some of your other meters roll over?

If so, you need to capture the RollOver value on the meter master table, TblMeter. Then when the roll-over occurs, your code will a) realize a roll over has occurred (current reading > previous reading) and adjust accordingly.

MeterID

TblMeter
MeterID – PK
Ref_VehID

...
a replacement meter for one that’s failed

Are you going to recycle meters? Track defective and new meters? You may want to handle the relationship between meter and vehicle as a Many-to-many relationship instead of having having the VehID on the Mter table.

TblVehicles
VehID – PK

TblMeter
MeterID – PK
Ref_MeterType – FK to TblMeterType.MeterType
RollOver

TblVehicleMeter
VehID - fk
MeterID - fk
EffectiveDate

Lastly, calculating consumption.
You will find this aspect may be tough. The third form of normalization suggests not to include calculated values on a table -- this makes a lot of sense -- and calculate consumption on the fly. Consumption is useful to know when entering new data, reviewing usage (on form and in a report).

I am sure you will come up with something, but I posted some code in the past using temp tables for this type of thing. Others, such as PHV and BobScriver and others have also posted other solutions.

Richard

 

[directorz]

Willir,
I've been out most of the weekend and could not followup. The way you enclose "quotes" in boxes is very clear...how is that done?

Let me address this bits at a time.
<<<Does this mean you can bring in a vehicle, and it will have several meters, or have one meter one day and another meter another day?>>> A vehicle can have several meters. Those meters are permanently mounted and unique to each vehicle.

In your first example...
TblWorkOrders
WorkOrderID – PK
VehID - fk to TblVehicles.VehID
MeterID - fk to TblMeter.MeterID
...and perhaps
StartDate
StartTime
EndDate
EndTime

Does this not imply that a work order can only have 1 meter?

Relative to tracking defective meters...I think we're on the same page, maybe the language is not correct. I don't know that I'm tracking the meter. I'm tracking the meter reading and if, in the event of a replacement, I need to 'adjust' the reading via code so we're not starting at zero again.

 

[willir]

You have to decide on how to associate a work order with the meter(s).

As an alternative solution, I presented a WorkOrderDetail table could be used, and the Meter associated with the detail record...

...Now, if the detail work is associated with the meter, and not the "header" record, a tweak would be...

TblWorkOrders
WorkOrderID – PK
VehID - fk to TblVehicles.VehID
...and perhaps
StartDate
StartTime
EndDate
EndTime

- note, no MeterID on this table

TblWorkOrderDetail
WorkOrderDetailID - pk
WorkOrderID - fk to TblWorkOrders.WorkOrderID
MeterID - fk to TblMeter.MeterID

- note, MeterID located on the detail table


You can also setup a many-to-many relationship if you want for WorkOrder and Meter.

The real issue is how do you want to report on work orders regarding meters??
- If you just record meter readings at the time of work orders, then you do not have to even have a WorkOrder associated with a meter - just capture the reading history.
- If you want to capture the reading history, and the work order, then you will want the WorkOrder on the Reading history file.
- If the WorkOrder is to repair / do something on the Meter, then you do want to capture the MeterID with the WorkOrderID or WorkOrderDeailID.

How to use a quotation box?
- See Process TGML link below (above and to the right of the Submit Post box (link was not working this AM)
- Each command is encapsulated with open square bracket [ and close square bracket ]
- Turn on command, just use basic command
-- code for code "box"
-- quote for quotation box
-- b for bold, i for italic, u for underline
- Colour using command color red or color blue, etc
- Turn off formatting with the forward slash / option
-- /b turns off bold, /u turns off underline, /quote turns off quote
- Must turn formatting on and off is same sequence
-- colour on + bold on ... bold off + colour off

So, substituting "{" for "[" and "}" for "]", to do the quote thing...
{quote}Now is the time ...{/quote}

Richard

 

[directorz]

Willir
This gets quite complex.. I do want the meter reading on work orders. When it comes to replacement meters, how is this handled. For example. if car 1 has 5000 hours and the hourmeter fails, it gets replaced. Now, I can't include a calculated value in the table. So, after a period of time I want to look up a service that was done at 5500 hours. But, the table only shows the values of both meters. The first one at 5000 hours with a max value of 5000 and the second one with a max value of 500 hours. Do I use a query totalling both columns.

I am sure you will come up with something, but I posted some code in the past using temp tables for this type of thing. Others, such as PHV and BobScriver and others have also posted other solutions.

Could you direct me to some sample code

Thank you
Directorz

 

[willir]

It sounds like you need a many-to-many relationship between meter and vehicle. This adds a bit of complexity to the design but not much, and it gives you more functionality.

To repeat...
TblVehicles
VehID – PK

TblMeter
MeterID – PK
Ref_MeterType – FK to TblMeterType.MeterType
RollOver - long interger
PurchaseDate
Active - yes / no

TblVehicleMeter
VehID - foriegn key to TblVehicles.VehID
MeterID - foriegn key to TblMeter.MeterID
EffectiveDate - Effective date meter was updated
Comments - Memo field

Include this table as a subform in your meter and vehicle forms. In the Meter form, hide the MeterID for TblVehicleMeter subform so you just see the vehicle info. In the Vehicle form, hide the VehID so you see the Meter info.

This way, you can track the history of a meter and vehicle as required.

Note that I added a field to the Meter table - Active - to be set when active or inactive.

The first one at 5000 hours with a max value of 5000 and the second one with a max value of 500 hours. Do I use a query totalling both columns

With the aforementioned solution, since you have the VehID associated with the meters, you really dont care which meter or meters have the reading, you just need to know the VehID reference.

I have to go now, but will update the post, hopefully later tonight.

Richard

 

[directorz]

Willir,

The more I see, the more I get confused. I’ve uploaded a sample at geocities.com. Username is directorz2002. Password is ticket. Access the file from file manager, look for [highlight]metersample[/highlight]. The arrangement shows the following.

1. A vehicle can have many meters and a specific meter can belong to only one vehicle.

2. Although there are many types of meters, a specific meter can be only of 1 type.

3. Each meter can have many readings and a reading can only be from a specific meter.

4. A vehicle can have many work orders and a specific work order can only be associated with 1 vehicle.

Now, in tying together this train of thought…

Problem 1: relationship of the work order table
A vehicle can have 1 or more meters and when a service is performed on a vehicle I need to record the reading(s) of any / all meters that are on the vehicle at the time of service on a work order. The service can include replacing the meter itself. So , in my mind the question is how does the work order tie into this picture. First, I think that a work order can have many meter readings from many different meters ( I want to be able to record them all). But then I think, it’s not the work order that has the readings, it’s the meter that has the readings and I just want to record it…I’m going in circles…

Problem 2: True meter read
If a meter is replaced, I need to know the “true” reading. That is, the sum of the old meter and the new one. Yet the sum is not stored in a table