On 12 May, 15:31, Nick S <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)

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.