Re: MySQL design problem for tracking inventory

1. Product options exist before and without any product so define them first and independent of any other things in your database, say:

OPTION1(id,...)
OPTION2(id,...)

The structure of options could be quite complex, for example, options could have hierarchy (to have a parent option) or be multidimensional (to have several own characteristics). In any case make them independent of products and items. Options do not know where and how they are used, i.e., what other objecs they characterize or will characterize in future.

2. Abstract products exist before sold items and independent of options so define them in parallel with options:

PRODUCT(id,...)

   id=1234, Name="ski jacket"

Consider it a (simple) categorization schema for real items you sell.

3. Real items are stored separately and are characterized by options and abstract product:

ITEM(id, product_id, option1, option2,...)

Items are what you sell. Items exist after options and products appear and know them explicitly so put this table even lower in the hierarchy. Again, this level might be more complex and have more tables and more relationships. A separate problem is how you associate options with each item. The simplest approach is shown above where we define them as table columns. However, if you have multivalued options then they could be implemented by introducing additional tables implementing relationships (for example, if I could choose any combination of colors rather than a single color for some product property). If you need to have a variable number of options then some other mechanism could be used.

4. You might want to keep track of resources like the number of items, sells etc. This could be done within previous section or you might create separate level for that which keeps track of different kinds of transactions.

5. Having defined such a schema it can be used to analyse your data. One way consists in defining necessary qureies manually. Yet you might need a lot of queries or some means to parameterize them. A good solution is using some OLAP tool to produce a multidimensional picture for your data and manipulating its parameters interactively. In any case the final query will need to involve several parameters to restrict different dimensions (options, products, time, producer etc.) so the question is if you will do it yourself manually or you will use some tool. For example, in order to get a picture for one concrete (abstract) product just select it, then propagate this constraint through the rest of the database and finally aggreagate this information.

alex

--
http://conceptoriented.com


dcm_scorpio schrieb:


> Hello,


> 

> 

> 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!

>