New Database Design Problem

[dweinar]

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 can’t figure out how to setup the relationships in order to make this database successful. Any hints?

 

[johnherman]

PointTable
PointID - PK
PointDesc
other point variable

ContractTable
ContractID - PK
ContractDesc
other contract variables

PointContractTable
PCID - PK
PointID - FK
ContractID - FK
Capacity

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[dweinar]

First, I want to thank you for the help johnherman. That was greatly appreciated. Secondly, I want to apologize for being such a "newbie".
I have one problem that I couln't get around today. I'll give an example. On contract 19 we have supply that we are purchasing from supplier S at point P. We are also purchsing on contract 19 supply from supplier Z at point P. The actual supplier doesn't matter, just the point that the supply is coming from. How ould I handle that?

 

[johnherman]

PointTable
PointID - PK
PointDesc
other point variable

ContractTable
ContractID - PK
ContractDesc
other contract variables

SupplyPointContractTable
SPCID - PK
PointID - FK
ContractID - FK
Capacity

DeliveryPointContractTable
DPCID - PK
PointID - FK
ContractID - FK
Capacity

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[dweinar]

Thanks again John. That seems to be right on target for what I am looking for. If I want to keep tract of the supplier that is supply the gas at the supply point I would just add a Supplier table with a SupplierID as a primary key. That SupplierID would than be the foreign key in the SupplyPiontContractTable, right?
Also we have certain members/customers that have multiple delivery points. I would just create a member name table with memberID primary key. The memberID would than be the foreign key in my delivery point table, right?
You have been most helpful, I hope that I'm not starting become a bother.

 

[johnherman]

Yes, I think you have grasped the key ideas.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[dweinar]

I was looking things over and realized that I forgot about the deals that we need capture in our database. The supply for the supply points needs to come from somewhere in order to be able to deliver the gas. We basically just track the pipeline that the gas is going into, the date of transaction, the supplier and their contact information, the deal number, the volume, price, and point that the deal is being done from, and the trader that did the deal.

Would I be able to simply use a form to enter that information into my currrent tables (basically the ones you described above) our would I need a deals table?

 

[johnherman]

You need to determine what the relation of "deal" is to the other tables. Clearly, a supplier will have more than one deal. do the rest of the relationships hold?

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[dweinar]

I was thinking that I could create a deal table.
DealTable
DealID - PK
SupplierID - FK
PointID - FK
a few other deatails such as the transaction date, volume, price etc.

That could relate to the deals table to the Supplier Table via supplierID. That would be a one on the supplierstable side and a many on the deals table side(because 1 supplier can have many deals, but one deal has one supplier). Then, I would related the Deals table with the points table via pointID. This would be a 1 on the ponits side and a many on the deals side (becuase 1 deal has one point, but one point can have many deals).
Does that seem like a solid setup? Or would you advise to do it in some other fashion.

 

[johnherman]

Yes. You need to determine the relationship between Deal to the other entities.

If I understand, on the supply side you have a Supplier providing one or more Deals, each Deal being able to be delivered to one or more Points against one or more Contracts.

On the delivery side, you have a Point. Over the course of time, a point can have many Contracts and could be supplied by many Deals and/or Suppliers.

The key table will be the Eelivery Event wherein a Supplier provides a Point with product, that product being all or part of a Deal and fulfilling all or part of a Contract.

When you finish the tables, you need to talk through the relationships in ordinary business language to make sure they are valid.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[dweinar]

I have setup the relationships in a way that I belive is correct. I know that the whole datbase depends on these relationships. I would like to show the layout to you to get some feedback.

I have 8 tables I'll give them a number to make the relationship discussion easier.
Table #1 - DeliveryPointContract Table #2 - Members
DPCID - PK MemberID - PK
DeliveryPointID - FK MemberName
ContractID - FK MemberPhoneNumber
Capacity etc.
MemberID - FK

Table #3 - DeliveryPoints Table #4 - Contracts
DeliveryPointID - PK ContractID - PK
DeliveryPointDRN ContractType
DeliveryPointName
DeliveryPointType

Table #5 - Deals Table #6 - Supply Points
DealNumber - PK SupplyPointID - PK
SupplierID - FK SupplyPointDRN
SupplyPointID - FK SupplyPointName
DealType
TransactionDate
DealContact Table #7 - Suppliers
DealPipeline SupplierID - PK
DealVolume SupplierName
DealPrice SupplierPrimaryContact
DealGasFlowDate SupplierPhoneNumber
DealContactMethod etc.
FGUTrader

Table #8 - SupplyPointContract
SPCID - PK
SupplyPointID - FK
ContractID - FK
Capacity
SupplierID - FK

I will state the relationships in terms of the one side to the many side.
Table #2 is related to table #1 via MemberID
Table #3 is related to table #1 via DeliveryPointID
Table #4 is related to table #1 via ContractID
Table #4 is related to table #8 via ContractID
Table #6 is related to table #5 via SupplyPointID
Table #6 is realted to table #8 via SupplyPointID
Table #7 is related to table #5 via SupplierID
Table #7 is related to table #8 via SupplierID

I know that is a lot of information to process. I can't thank you enough for your help in getting this process started.
If you need any further background information please do not hesitate to ask anything.

 

[dweinar]

Thanks for the tips, things seem to be working well so far.