Re: Item with multiple options

nrsutton@xxxxxxxxx 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.

.

Relevant Pages

  • Re: optocoupler CNY17G-2
    ... the Farnell Australia website ... doesn't have the same stock items as in the UK, and these 2 are not included. ... I spent some considerable time on the Farnell AU website tring to determine ...
    (sci.electronics.repair)
  • Re: Pool-oriented programming
    ... > stock, stock lines and stock categories requires me to invent a highly ... > flexible method of dealing with stock items. ... An item may also be in numerous promotion pools. ... > early flexibility. ...
    (comp.object)
  • Re: Lack of Expedited Shipping From Squadron???
    ... can't order out of stock items, like just about every other online ... bin. ... I do go to their website, that is how I find out they are out of stock ...
    (rec.models.scale)
  • How to identify which cell in a row first becomes negative.
    ... Can anyone offer an elegant Excel formule to identify the following. ... could probably do it with some brute force VBA code examining every ... I have another table of forecast Stock Balance items for all the 30 ... or so stock items I''m interested in as follows. ...
    (microsoft.public.excel.programming)
  • Re: These Hypocrisies are NOT the Christians problem
    ... Roll Call's annual list of the 50 richest Members of Congress hasn't changed ... owing to the drop in price of her stock in RealNetworks, ... one point her net worth was estimated at about $40 million, ... He also lists interests in a Denver mobile home community; ...
    (alt.sports.football.pro.ne-patriots)