I am trying to design a database that would help track the daily supply of natural gas on multiple contracts. Each contract has a supply side and a delivery side which are equal in volume (when fuel is taken into account). My problem begins with the fact that each contract has a different capacity. That capacity is a total of the capacities of each point on the contract. Some points are on multiple contracts so they have different capacities on different contracts. I cant figure out how to setup the relationships in order to make this database successful. Any hints?
Preparing an ERD should be useful. What are the entities. You mention each "point" on the contract - is a "point" an entity? Would the contract then be composed of sets of these "points" in a 1:M relationship fashion?
The problem I am having is in the relationships. I'm not sure how to go about getting everything started. Let me explain a little better. We have multiple contracts that allow us to schedule the delivery of natural gas to customers. On each contract there are certain points that we can get gas supplied to us from (i.e. the station X processing plant). That point that we get the gas from as a certain maximum capacity. This is complicated by the fact that the volume of gas being supplied from station X can be coming to us from more than one supplier. In addition, point x could be on contract 1 with a max capacity of 100 and on contract 2 with a max capacity of 200. Would I need to have a table for each contract with the max capaicty at that point on that contract listed? If that is the case how do I handle the fact that we can have gas coming from point a via supplier D and supplier E?
I appreciate your help. I know I'm close, just can get over the hump.
And I assume it is possible that a supplier could provide gas at more than one station? Two possibilities come to mind.
Contract (ContractID (pk), other relevant fields)
Source (ContractID (fk), StationID (fk), SupplierID (fk), Volume)
Station (StationID(pk),other relevant fields)
Supplier(SupplierID(pk), other relevant fields).
Contract(ContractID(pk), other relevant fields)
Source(ContractID(pk), StationID(fk), Supplier, Volume, other relevant fields about the contract-station relationship)
Station(StationID(pk), other relevant fields about the station)
In both examples, Source is a relationship table that embodies the 1:M relationship between contract and stations. In some applications, additional relevant information may be included in the relationship table - hence the relationship can have attributes.
Does something along these lines get the job done?