I am tasked with designing a database for my company to track inventory
of different items we might sell. We can't use any of the "shopping
carts" out there because this is really more for internal use and we
will be integrating it into our existing system later.
I need some help with the design of the database for this part only.
The problem I am running into is that I need to be able to have it
track inventory based on a combination of "options" available for each
product, not just the product itself.
So for example:
Assume I have a product_name of "ski jacket" with a product_id of 1234
and its other corresponding description fields etc. We need to be able
to add "options" to that product_id (w/ a join table etc.) that says
product_id 1234 is available in colors [black|red|blue] and
sizes[Small|Medium|Large]. Inventory then needs to be tracked for
EVERY combination of options and not just "ski jacket" in general.
(i.e. - blue/small = 20 but blue/medium = 0).
- The part that I'm having a hard time with, is the fact that products
don't start off with ANY options added to them. It IS possible that a
product not have ANY options, in which case inventory just needs to be
tracked for the product (either by a default non-visible "option", or
an "inventory" column in the products table). Also, it is possible
that a product have 1 option, 2 options, all the way to X options.
Obviously adding too many options becomes a management headache when
having to input inventory numbers for every combination. Even so, the
design should scale to X options.
I would love to hear any thoughts or suggestions on this, or how you
may have accomplished this already. Thanks!
Received on Sun Mar 06 2005 - 06:03:50 CET
