Substitutable Products 1

[evalesthy]

Looking for best way to approach this concept of keeping track of 'related' items that are substitutes for each other.
Simple example: You have a Product Category 'Canned Goods' and within that you have 3 items that are legitimate substitutes for each other (e.g. Vendor_1 8 ounce Tomato Paste, Vendor_2 8oz Tomato Paste, and Vendor_3 8oz Tomato Paste). If you were thinking of reordering Tomato Paste you would want to be sure to check on-hand quantities of all 3 related interchangeable items. What is the easiest and most logical way to set up these relationships?

 

[Remou]

I imagine a product table with a category field would work.

http://lessthandot.com

 

[lespaul]

or a substitute product table:
[tt]
ProductName Substitute
Vendor_1 8 oz Vendor_2 8 oz
Vendor_1 8 oz Vendor_3 8 oz
Vendor_2 8 oz Vendor_1 8 oz
Vendor_2 8 oz Vendor_3 8 oz
Vendor_3 8 oz Vendor_1 8 oz
Vendor_3 8 oz Vendor_2 8 oz[/tt]

but this would be a maintenance nightmare!

Leslie

Have you met Hardy Heron?

 

[MajP]

One question you need to answer are all relationships transitive. If B is a replacement for A, is A also a replacement for B? With food probably true. But if for example and you are ordering building material or car parts this may not be true. Bolt B can replace A because it is slightly smaller, but A can not replace B because it will not fit.

It this is the case you would have to go with Leslie's table vice Remous idea.

 

[evalesthy]

I appreciate the comments so far. To keep the discussion going - some valid issues have arisen. You could have 4 Tomato Paste items under 'Canned Goods - Sauces' Category

Item A: 8oz Tomato Paste - Low Sodium
Item B: 80z Tomato Paste - Low Sodium
Item C; 10oz Tomato Paste - Low Sodium
Item D: 8oz Tomato Paste - High in Sodium

Here, A could substitute for B and B for A (they are interchangeable). However, D is the right product but is not substitutable because the container quantity of 10oz does not work well in recipes. And, Item C will not work for dietary reasons of being too high in Sodium. So how does one make the legitimate Substitution link between A and B in a user friendly way? There could be 1000's of items in the storeroom so this is a constant issue - not an infrequent one. Also, you may have the exact same issue with 4 similar 16oz Tomato Paste products.

All comments appreciated.

 

[GSCaupling]

Are the attributes separated in your table? I hope "8oz Tomato Paste - Low Sodium" is not in a single Product Field.

Sounds like you need to separate the product description into multiple categories, e.g., description, size, feature, etc.. Then use queries to return matches on the relevant fields.

I think this would give you maximum flexibility to examine any inventory situation.

Good luck,
GS

[small][navy]**********************^*********************
I always know where people are going to sit. I'm chairvoyant.[/navy][/small]

 

[Remou]

I am wondering if you should have assemblies.

[tt]tblProperties
Property

tblProduct
ProductID
Product

tblProductProperties
ProductID
Property
Value

tblSuppliers
SupplierID

tblProductSupplier
ProductID
SupplierID[/tt]

[tt]The tblProductProperties might be:
ProductID Property Value
1 Size 10oz
1 Sodium Low
2 Colour Green
2 Fat High[/tt]

http://lessthandot.com

 

[evalesthy]

Remou: I like your suggestions and will study them more carefully in the next day or so. Thanks.