Relationship setting

[aw]

We have 2 area, "Sale" & "Purchase".

For SALE :
==========================

Supplier list
-------------
1 ID (Key)
2 Supplier Code
3 Suppliers


Purchase Contract
-----------------
4 Pur Contract no
5 Supplier code
Purchase Order Tx
-----------------
6 ID (Key-auto no.)
7 Pur Contract no
8 Item Number
9 QTY
10 Unit Cost



relationship setted : 1->5 (1 to many),4->7 (1 to many)



For PURCHASE :
==========================


Customer list
-------------
a ID (Key)
b Customer Code
c Customers


Sale Contract
-----------------
d Sale Contract no
e Customer code
Sale Order Tx
-----------------
f ID (Key-auto no.)
g Sale Contract no
h Item Number
i QTY
j Selling Price



relationship setted : a->e (1 to many), d->g (1 to many)



Remarks :
- Sale & Purchase are alway in 'pair' (no inventory).
- But sale contract number -> Purchase contract number is "1 to many"
relationship.



Questions :

1. How relationship should place (which table) & set (1-many or ..) between
Sale no & Pur Contract no as they should appear in one pair (or 1-many)

2. The best solution to eliminate duplcaite input of '8','9','h' & 'i'

 

[dan dungan]

Hi aw,

I don't understand what this means:

For SALE :
==========================

relationship setted : 1->5 (1 to many),4->7 (1 to many)

For PURCHASE :
==========================


relationship setted : a->e (1 to many), d->g (1 to many)

Dan

 

[Fred]

Could you also provide more detial on the subject of "- Sale & Purchase are
alway in 'pair' (no inventory).
- But sale contract number -> Purchase contract number is "1 to many"
relationship. "


Including resolving the conflict.....your description indicates that these
are paire dorder-to-orde, but other items in yoru posts imply that they are
paired at the item level

 

[aw]

Let me explain further for our work flow :

1. Obtain Customers order from Salesman
2. Find Supplier to supply goods for (1)
3. if no supplier can be found, then ignore this order (no further action
need)
4. if supplier / supplier(s) found in (2), issue Sale Order & Purchase Order
at the same time. (that is what I mean ‘in pair’)

What our database most concerns are :
1. Set relationship between Sale & Purchase order, so that we can know the
profit by each Sale order.
2. We can trace back from Purchase order for which Sale order they really
come from
3. As Sale order & Purchase Order’s quality occur in ‘pair’, how to design
our database for data input so that we can reduce our input time.


Remark : relationship b/w Sale order with Purchase order is “1 to manyâ€