R
Rasoul Khoshravan Azar
I am designing a data base to handle the procedure of ordering commodities
by a customer to a seller which includes 8 identities. It is very much the
same as Northwind example with minor differences. I have get the main idea
from it and made some amendments and changed name of some tables. I would
like to hear your comments on the structure of DB to be sure it will work
fine. Especially my concern is the PK and FK for joining tables to each
other.
What I want to do:
An applicant (customer) asks a seller (supplier) to issue a
ProformaInvoice(PI) for specified commodities (products) on his favor. If
they agree on PI content, Applicant issues a request (order) for purchasing
the goods. A Bank will be involved for openning a Letter of Credit (LC). LC
procedure is not part of my project at this stage. An Allocator (different
from Applicant and bank) will allocate the proper hard currency for
transaction. The most difficult part for me is to whom should I jion the
allocator table.
ApplicantTbl (same as customer in Northwind)
ApplicantID (PK)
.... (other fields)
BankTbl
BankID (PK)
.... (other fields)
TransporterTbl (same as shipper in Northwind)
TransporterID (PK)
.... (other fields)
RequestTbl
RequestID (PK)
ApplicantID (FK)
BankID (FK)
TransportID (FK)
.... (other fields)
RequestDetailTbl (only to make m-to-m join between commodityTbl and
REquestTbl)
RequestID (FK)
CommodityID (FK)
.... (other fields)
CommodityTbl
CommodityID (PK)
PIID (FK)
.... (other fields)
PITbl
PIID (PK)
SellerID (FK)
PINo (PK) This is the question for me
.... (other fields)
SellerTbl
SellerID (PK)
.... (other fields)
TIA
Rasoul Khoshravan
by a customer to a seller which includes 8 identities. It is very much the
same as Northwind example with minor differences. I have get the main idea
from it and made some amendments and changed name of some tables. I would
like to hear your comments on the structure of DB to be sure it will work
fine. Especially my concern is the PK and FK for joining tables to each
other.
What I want to do:
An applicant (customer) asks a seller (supplier) to issue a
ProformaInvoice(PI) for specified commodities (products) on his favor. If
they agree on PI content, Applicant issues a request (order) for purchasing
the goods. A Bank will be involved for openning a Letter of Credit (LC). LC
procedure is not part of my project at this stage. An Allocator (different
from Applicant and bank) will allocate the proper hard currency for
transaction. The most difficult part for me is to whom should I jion the
allocator table.
ApplicantTbl (same as customer in Northwind)
ApplicantID (PK)
.... (other fields)
BankTbl
BankID (PK)
.... (other fields)
TransporterTbl (same as shipper in Northwind)
TransporterID (PK)
.... (other fields)
RequestTbl
RequestID (PK)
ApplicantID (FK)
BankID (FK)
TransportID (FK)
.... (other fields)
RequestDetailTbl (only to make m-to-m join between commodityTbl and
REquestTbl)
RequestID (FK)
CommodityID (FK)
.... (other fields)
CommodityTbl
CommodityID (PK)
PIID (FK)
.... (other fields)
PITbl
PIID (PK)
SellerID (FK)
PINo (PK) This is the question for me
.... (other fields)
SellerTbl
SellerID (PK)
.... (other fields)
TIA
Rasoul Khoshravan