Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Database Design / May 2005

Tip: Looking for answers? Try searching our database.

Multiple Product Items

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
miranda65 - 16 May 2005 11:49 GMT
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.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.