Please Help Relationship If its okay

[hamish]

have a table with three fuel tanks,(example diesel,ULP LPR) then i have a table for the Pumps example (pump1,pump2,pump3 and so on,they extract the fuel from the tanks.The tanks have a daily open and close reading,And the pumps also have a open and close reading.I have attached a sample

 

[stopher]

Hi
You seem to be relating Pumps and Tanks using GasType. I think this is wrong. Personally I would have the tanks identified as TankID then have TankID as the foreign key in the Pump table. You can always work out which type of gas a pump is using by which Tank it is related to.

You have a field called LitresUsed in PumpTrans. It looks like you are intending to calculate and store this from the OpenReading and CloseReading. In fact you should not store it but calculate it on demand only when you need it.

hth
Chris

 

[hamish]

Hi
You seem to be relating Pumps and Tanks using GasType. I think this is wrong. Personally I would have the tanks identified as TankID then have TankID as the foreign key in the Pump table. You can always work out which type of gas a pump is using by which Tank it is related to.

You have a field called LitresUsed in PumpTrans. It looks like you are intending to calculate and store this from the OpenReading and CloseReading. In fact you should not store it but calculate it on demand only when you need it.

hth
Chris

Hi ChrisThanks for your response ,i been working on this project for a while and cant get the relasionship right,,I have attached a sample can you change it for me and i will upload it from you and check it out,,My mind just went blank

 

[stopher]

I have attached a sample can you change it for me and i will upload it from you and check it out,

Sorry, I've got to go to work and earn some money. You're nearly there with your design.

Just get away from the idea that a tank is identified by the gas type. A tank is just a tank and you need to uniquely identify each tank. Suppose you have another tank installed with the same fuel as in another tank. By identifying the tank by say a number, you just give the new tank a new number. And I assume there are several pumps connected to each tank (one-to-many relationship). What's in the tank is just information about the tank.

Tank table:
TankID (Primary Key)
GasType (or you could use GasID and link to another table os GasTypes)
TankCapacity

Pump table:
PumpID (Primary Key)
PumpName
TankID (Foreign key)

Chris