How to create tbales/relationships to account for items and groups of items

[jja444]

I am creating a sales database. Our customers have the option of purchasing items a la carte or they can purchase a preset package of items. Part of the database will also track inventory of the specific items. So, I would like to track the items that are ordered whether they are ordered a la carte or part of the standard packages. I cannot figure out how to represent this in my tables though. Any help?

[kd2017]

Disclaimer: I'm an amateur, just throwing something out there.

So you have items and packages. A package is just an item that is made up of other items, correct? What about an items table that is related to itself? Here's an example of what I was thinking


or if a package can have multiple of the same item in it:



deletethis.zip

The quantity in stock for the packages could be a calculated value. quantityInStock of a package = min( package's product's qty instock \ qty needed for package )
I've attached the example db.
*Note im using back slash for division rather than forward slash so the division will round down
A potential issue here with calculated stock in packages is someone could order more of an item than is available, special care could be taken to prevent that. Is this a multi user environment?

Maybe it's a bad idea, idk... Hopefully the pros will be along soon to help you further.

[NTC]

One assumes you have product list table.

In your product list, packages are included as products.

Then you need a separate package table that lists/repeats package and product - 2 column minimum. In this table a product is a package (that may not make sense at first but just think of a product as a package of 1 product)

In your sales table they will select from the product list. As part of your order processing that joins to the package table so if they selected a product - it is one product, but if they selected a package - it joins to all records with a matching package ID.

in the end the order is of products - as that is the most granular set - and you include the package identity purely as presentation.