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 - 17 May 2005 17:52 GMT
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,

Signature
George Nicholson
Remove 'Junk' from return address.
> Hi All,
>
[quoted text clipped - 16 lines]
>
> Any help would be appreciated.