We have a small SQL SERVER 2000 database for importing - trading goods and extending new features to it. I have confusion to implement the right PK - FK Relationship. Thanks in Advance.
There are three tables we have:
These are bulk products and there are no packaging , but just quantities.
- All PK's are surrogare (auto incrementing)
- With one contract we can have 0 to many invoices and one invoice can be belong to just one contract.
( 0 invoices is going to be 1 in a few days)
- With one to many invoices you make 0 to 1 import.
(0 imports is going to be 1 and only one in a few days)
(many invoices means therecan be partial contracts ending and beginning.)
- Quantities : Total Invoice quantity for one contract cannot exceed the contract quantity. We build trigger to control this. Import Quantity is exactly the same as Invoice Quantity.
1- Is this model (PK and FK's) in the right way)? What do you think?
2- After importing we load the goods to trucks what we have imported, so we should enter which product from which supplier we want to load. If we start to make the JOIN query from Truck Table to Contract Table until we get the ProductID and SupplierID then it is a very long query. What do you think?
3-What do you think about the quantity control? Inventory is calculated with calculated field on reports.
1. and second questions are very important for us t move further. Your help is apprecieated.