Hi

I have a real headache of a problem and was wondering if anyone can
help me.


I'm writing a system that saves stock items. Each item can have none
or an infinite number of options attached to it.


For example you can have a cupboard door as one stock item or you can
have a Polo Neck sweater with the options of size:small, medium,
large
and colour: red, green, blue.


My problem is that I have no idea how to store this information in
the
database. My first thought was three tables STOCK_ITEMS,
STOCK_OPTIONS, OPTION_VALUES The item name would be stored in
STOCK_ITEMS with the option names in STOCK_OPTIONS and their values
in
OPTION_VALUES. This works all very well until you want to store
something that doesn't have any options. Now I have one item in
STOCK_ITEMS with one id and another item that DOES have options with
a
seperate id in OPTION_VALUES.


I feel like I'm going around in circles with this and it feels like
there should be such a simple solution but I'm just not seeing it.


Is there anyone out there that can give me a pointer and show me the
ligh?. Am I looking at the problem from completly the wrong angle?
What I want is a common way to identify a stock item (even if it does
or doesn't have an option)

On 12 May, 15:31, Nick S <nrsut... (AT) gmail (DOT) com> wrote:
Hi Nick,
first thing to do is to identify the relationships between Items,
Options and Values.

For a start, can an option have more than one value? If not, then
options and values should appear in the same table.

An example of each of the possible combinations would help.

On 12 May, 16:18, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:
Hi Captain.

Thanks for the reply. I think I demonstrated that in my initial
post. An item can have many or no options and an option can have 1 or
more values.

Thanks

Nick S wrote:
Ahh, I think I am with you now. I had taken option values as being prices
for the option.

I see now that you mean that options are things like size or colour and your
optio values are small,medium,large or red,green,blue respectively.

OK, I'll have a think.

On 12 May, 18:09, "Paul Lautman" <paul.laut... (AT) btinternet (DOT) com> wrote:
That's right Paul

The problem is that there can be any combination of options used, so
the old auto_increment field becomes a bit useless as an identifier
for an item because it could have a combination of size and colour,
which gives me three id's The on in STOCK_ITEM and the one for the
value of size and colour from OPTION_VALUES.

After spending some time in the head I was thinking maybe if I gave
each item and each option a small unique code I could identify a
specific item by combining the codes, i.e.

If for example a polo neck shirt was given the code MLPOLO and the
size option was SZ with values of S, M and L and the colour option was
CL with values of RE, GR, BL

A medium red polo neck would have a code of MLPOLO-SZ-S-CL-RE But
you're adding around 6 characters per option, and with something that
has a lot of options it may become very cumbersome.

Nick

On Mon, 12 May 2008 16:31:36 +0200, Nick S <nrsutton (AT) gmail (DOT) com> wrote:
I'd rather say undetermined amount then the technological impossible
infinite :P.

This?:
ITEMS
id
name

OPTIONS
id
name

OPTION_VALUES
id
options_id
value

And then either an ITEM_OPTIONS if something with 'color' should have all
possible values of color:
ITEM_OPTIONS
item_id
option_id

....or if specific values can be available or not, an ITEM_VALUES table:
ITEM_VALUES
ìtem_id
option_value_id

If there is more info you need to store about a particular item (for
instance, a green sweater has another amount in stock then red sweaters),
those are effectively different items, but in a group with some common
values (a name, perhaps a manufacturere, etc.), so I'd:

GROUPS
id
name
...and alter items:
ITEMS
id
name
group_id
stock

....or, if items can be in multiple different 'groups'
GROUPS
id
name
ITEMS
id
name
stock
ITEM_GROUPS
group_id
item_id
--
Rik Wasmus
[SPAM] Now temporarily looking for some smaller PHP/MySQL projects/work to
fund a self developed bigger project, mail me at rik at rwasmus.nl. [/SPAM]

Does this mean that all of the combinations of options are legitimate
applied to an item?

How does one determine whether a particular combination of options
is allowed or not? I would think that at some point you would need to have
a table of "configurations", where a "configuration" is an item with
a particular set of options that is allowed (and perhaps things like
an inventory level and price for that particular configuration).
A problem is that the number of configurations can get very large.

For example, I don't think you'd really want to sell an
item like:

2008 Toyota Prius, waist size: 79, color: red, colour: blue, shoe size: 10D,
warranty: 10 years, sex: female, breed: Poodle, race: Marathon.

The Attribute = Value approach works fairly well for storing odd
bits of data about something, for which many of the possible
attributes are missing. The best example I can think of is the
personal address book, where a person has a name, but that's about
all that's mandatory, and they may have a few of a dozen or so types
of phone numbers and addresses, a birth day/date, a company, etc.

I think at some point you are going to need to come up with a list of
what configurations an item can have, and unfortunately the most general
way to do this is to enumerate them. If there's no configuration for
an item with no options, you can't have one. For example, on items
that have sizes and colors, you usually need the item to have exactly one
size and frequently exactly one color. Even with the simple cases
(jeans have a size and a color), you may find you don't support the full
range of colors for the sizes at the extreme ends of the range.

Incidentally, are options ordered? Is Flag color:red, white, and blue
the same as Flag color: blue, red, and white?


Infinite flexibility has its costs. Just because you can have a size 100
bikini, and a gold-spangled bikini, doesn't mean you'll ever be able to
sell a size 100 gold-spangled bikini.

This, by itself, doesn't prevent someone from trying to look up
MLPOLO-CL-RE-SZ-S and not finding it. You might need a "standard
ordering of options". And then there's the interesting restriction
that not all of the colors are available in the largest and smallest
sizes. It might seem strange, but try looking at a clothing catalog
some time.