Item with multiple options

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:05, nrsut...@gmail.com wrote:
> 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)

This is a database question, not a php one.

If you are using mysql, I should go ask it on comp.databases.mysql.
Otherwise, at a push alt.php.sql

nrsutton@gmail.com wrote:
> 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.
>
Standard one-to-many database stuff.


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

Ok..

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

Yes. Create a table of items, and another table of options, and have a
field in the options that carries the value of the stock item.

That works for infinitely variable options.

If the option set is limited, create a table of all possible ooptions,
and then a further table that links an option to the stock table.

i.e. if the option 'small' is unique ID 1, and T-shirt is unique ID 2,
make a link table with three fields..its unique ID, and a pointer
(containing 1) to the option 'small' and a pointer (containing 2) to the
T-shirt..

To bring up lists, use a join type (explicit or implicit) SQL statement.


I have just such a system used to cerate structure ddata aout a product:
I have a table of name/ value pairs that conytains a file that is used
top oredr the elements for display, and to link to teh priduct:

so my table of optional data looks like this:-

ID Name Value display_order stock_id
678 width 22mm 0 337

where 337 is the part number needing this extra info.

doing "select name,value from options where stock_id='337' order by
display_order, name" will net me a list of optional stuff about this
part number.

If there are no extra options, you simply get a null length list.