Multiple Product Items

[miranda65]

Hi All,

I'm having trouble with the following database design issues.

The database is to sell individual items and items as associated to a
combo-set (case) - special discount when multiple items are sold.

For example: the following products AB124, AC321, QW456 & TZ567 can be sold
individual or all together at a discount with the special CaseID CS112.

How do I assign 4 product items each with their own ProductID to the CaseID
in my tables when the items are sold in together in a combo-set?

I have a suppliers table, a product table, case table....how do I join them
together?

Any help would be appreciated.

 

[George Nicholson]

This is what I would probably do for maximum flexibility & robustness:

*** tblComponents
(think of these as the RawItems or Parts that comprise your
Merchandise/Products.)
-ComponentID (Unique, maybe PK)
-ComponentDescription
-Supplier
-?ComponentCost
Inventory records (if any) would use this as their starting point.

*** tblProducts
(This reflects what is available for sale. They are comprised of one or more
components: single vs. case)
-ProductID (Unique, maybe PK)
-ProductDescription
-?ProductCost
Sales records would use this as their starting point

*** tblProductComponents
(A Join table. Translates Products into Components & vice versa. What
Components go into a Product? A "single" product with a single component
will have one record here, a "case" with multiple components will have
multiple records)
-ProductComponentID (AutonumberPK)
-ProductID (many-to-one: tblProducts)
-ComponentID (many-to-one: tblComponents)
-ComponentQuantity (default is 1, but easily allows multiple components
in a single product: "two-for-one" specials)
The combination of + ProductID + ComponentID should be a unique index.
***

HTH,