Purchase Order Relationship (1 Viewer)

[silentwolf]

Hi guys,

just wonderig your thoughs on the following relationship.

I got a Supplier table 1: n to Product table

PurchaseOrder Table 1:n to PurchOrderDetails table

Attached the Relationship.

I was wondering if I should include SupID also in the PurchaseOrders Table so I can Pick a Supplier and create a PurchaseOrder or is it better to leave the relationship as it is?


Thanks for your Input!

Cheers
Albert

 

[cheekybuddha]

Leave it as is.

You can query the supplier via the ProdIDRef -> Products.ProdID -> Products.SupIDRef -> Suppliers.SupplierName

When you pick a product for the PurchaseOrder it is already tied to a supplier.

 

[cheekybuddha]

If a product is available from more than one supplier you will have to change the relationship between Suppliers and Products to a n:n (via a junction table)

 

[silentwolf]

Hi David,

Ok than I will leave it as it is as there are not really more suppliers with the same Products.

Cheers!

 

[Pat Hartman]

I disagree. I would add the supplierID to the PO. This will be used to control the combo on the details form so that you can only select products sold by a particular supplier. Technically this looks like a duplication and a circular relationship but you are not using the SupplierID in the PO to enforce RI but simply to control the combo of the subform. Also, once a single Item has been added to the subform, you have to prevent the SupplierID from being changed since it makes no sense to have a PO that contains products from multiple suppliers.

 

[silentwolf]

Hi Pat,

thanks for your reply!
So you mean just add the SupplierID to the PO table but don' t join it to Suppliers Table?
Or join it and just don't enforce RI?

 

[Pat Hartman]

I would always enforce RI. There is no reason to join to the Suppliers table in your queries unless you want to pick up additional information such as the shipping address. Use a combo on the form to show the Supplier. Just understand that enforcing RI between the PO record and the Supplier will NOT have any effect on the PODetails. You are using the Supplier in the PO record to control the selection options in the rowSource of the Product in PODetails because it makes NO SENSE to allow the PO for Supplier1 to have PODetails for products only supplied by Supplier2.

 

[silentwolf]

Thanks Pat for your explaination!

 

[RogerCooper]

You can have a default Supplier ID in the Product table. That can save time when creating orders but does not prevent you from using a different supplier.

 

[Pat Hartman]

The safest solution as I said, is to use the SupplierID in the main form to select the products in the subform's Product combo. That will let the combo ensure that there is no conflict in the Supplier between the two forms and all instances of the subform.

 

[gemma-the-husky]

I would not assume you can only have one supplier for a given product. That sounds unreasonably limiting to me

When you say suppliers 1 to many with products, do you mean you store the supplier in the product table?

However you store the supplier, you still want the supplierID on the order, as otherwise you could add products to the current order from more than one supplier.

 

[silentwolf]

Hi,

there are few suppliers with many products but each supplier has its own products.
So if I select a supplier I like to show only the products of that supplier and then create a Purchase Order for that particular supplier.

When you say suppliers 1 to many with products, do you mean you store the supplier in the product table?

I store the FK to the Product Table and the FK to the PurchaseOrder Table.

Hope that makes sence?

 

[gemma-the-husky]

Hi,

there are few suppliers with many products but each supplier has its own products.
So if I select a supplier I like to show only the products of that supplier and then create a Purchase Order for that particular supplier.



I store the FK to the Product Table and the FK to the PurchaseOrder Table.

Hope that makes sence?

So what do you do/would you do if you change the supplier for a product, or you ever need to source a product from a different supplier? I don't know what products you sell, but there are generally some generic products in most systems that could be bought from more than one supplier. You can't do that if you store the designated supplier in the product table.

 

[silentwolf]

Well that system needs to look for parts for maintainence.
And those Parts are from an individual supplier.
I had it with multiple Suppliers set up first but as there are so many products about 10 tsd. the System will blow up if I set it up so each supplier could sell the same products.

Which in most times that is not the case.
A supplier has Models and sells Parts to those models.
So if I have maintainence work to do I need to find those parts related to the specific Model or in that case the Supplier.

Not sure how I could set it up so I don't have thousands of products also able to buy from different suppliers so I keep the system not blowing up.

Hope that makes sence and is understandable.

 

[gemma-the-husky]

You need 3 tables as others have said.

A products table
A suppliers table
A products-suppliers table

You don't set it up that any supplier can supply any product.
You set it up that each product can be supplied by the suppliers you designate for the product, possibly at different prices.

Otherwise, if you want to buy a Widget that is supplied by ACME, but ACME haven't got any, you're in trouble. If you have other suppliers then if ACME don't have any you can order from The Widget Co instead and add the new supplier as an additional supplier for the Widget in the products-suppliers table. You don't want to change THE supplier for the Widget from ACME to The Widget Co, because next time you might want to go back to ACME. You design the system to provide you with the flexibility that you actually need in your business. The system analysis is most important so hopefully you get it right (or nearly right) the first time. You can designate one of the potential suppliers as the preferred supplier, or have a supplier preference order. Whatever you need to do the job.

 

[silentwolf]

Ok well then I change it again .
But how would you set up the Purchase Order in relation to those tables?

 

[Pat Hartman]

The Product table should NOT store the SupplierID, but it should store your sale price. When you need to support multiple suppliers - and I suggest you build the app to do that because you will have to do it at some point - you need a child table that holds the SupplierID and the purchase price.

Even if right now, when every product comes from only one supplier, the separate table for the SupplierID and Price works fine. You'll thank us later.

 

[silentwolf]

Ok Thanks Pat,

I will put something together and post the layout maybe you could have a commant about it then see if it is as you think.

Cheers

 

[silentwolf]

This is how I had the structure a while ago.
For better understanding I placed the English Table Names on top of each table.

Sorry for having it still named with my old convention but just so to give you an idea of how I had it set up.

Units and PackagingUnits are used so you can have have prices based on the Packaging Units

For example:
Units = Liter, Package, Hour, Piece,
PackagingUnits = Bottle, Canister, Box, Barrel

I buy a product

BaseCategory = Chemical
Manufacturer = Buzil
Description = Spezial Cleaner
BaseArticle = T201

The above Article can be bought in different Units like
1 Liter
10 Liters
200 Liters

This Article can be sold by different Suppliers
Supplier = AB
Supplier = ABC

Supplier AB sells 1 Liter, Bottel T201 for 8,99
Supplier ABC sells 1 Liter, T201 for 10,55
Supplier AB sells 10 Liter, Canister, T201 for 60,99
Supplier ABC sells 10 Liter, Canister T201 for 75,88

So if I use this setup then it would mean I need to have all References in those tables which then blow up the database.
But it would be very flexible in terms of using the right packaging units and compare those prices for the cheapest.

And buy it then where I get the cheapest price.

 

[Pat Hartman]

I can't figure out the column names so I can't comment. Sorry. If you purchase in one UOM and sell in different ones, you need translation tables so you can convert liters to canister, etc. This also makes the inventory much more complex to manage. My wholesale florist app had to deal with that. They purchased by the box but sold by the box or the bunch. Bunches were always 12 stems but the number of bunches in a box depended on the size of the stem (flower or greenery) itself. The boxes were all the same physical size so smaller stems had more bunches to the box where larger stems had fewer.