[thelist] Item Description Field

Jay Turley jayturley at gmail.com
Wed Jul 23 01:08:10 CDT 2008


Marc-

I'd probably use a tree structure to hold your items and I'd start
with something like this:

category
-----------
id INT PK auto-increment
category VARCHAR (50) // name of category e.g. Door Leaf, Door Jamb

option
----------
id INT PK auto-increment
option VARCHAR(50) // name of option e.g. Door Type, Jamb Material

category_options
----------------------
category_id FK,PK references category.id
option_id INT FK,PK references option.id
option_value VARCHAR(50) // value of option field for a specific option

item
-----
id INT PK auto-increment
item VARCHAR(50)
category_id INT FK,PK references category.id

item_children
----------------
item_id FK,PK references item.id
child_item_id FK,PK references item.id
qty_required INT

the idea is that everything you are putting into the database is an
"item", and that what is important to represent are the relations
between items and the items from which they are composed.

I didn't follow up on what you would need if the option_values for the
category_options were defined sets; I assumed here that they could be
free-form input to simplify.

I did allow for multiple categories for items. If this is not
necessary, then category_options table becomes item_options table
instead, and uses an item_id instead of a category_id. And then the
category gets put into the item table as VARCHAR(50) since it is
really just a description. Then the category table is not necessary.

So, if we were going to represent your example below in this
structure, it would look like (using the table structure from above:

category
-----------
1 | 'Finished Wood Door'
2 | 'Door Leaf'
3 | 'Jamb'

option
--------
1 | Door Type
2 | Width
3 | Height
4 | Thickness // rolled Jamb Thickness into Thickness for ease
5 | Door Core
6 | Door Finish
7 | Material

category_options
-----------------------
1 | 1 | 'Double'
1 | 2 | '3-0'
1 | 3 | '7-0'
1 | 4 | '1 1/4'
1 | 5 | 'SC'
1 | 6 | 'Primed'
2 | 4 | '7 1/4'
2 | 7 | 'FJP'

item
------
1 | 'Item 1'
2 | 'Item 2'
3 | 'Item 3'

item_categories
----------------------
1 | 1
2 | 2
3 | 3

item_children
-------------------
1 | 2 | 2
1 | 3 | 3

That should about cover it. Generating descriptions means
concatenating the category options for a specific category_id. One
thing to think about though, is that SQL doesn't guarantee an order
for the returned result sets. So you may have to worry about how to
order the descriptions.

-Jay

On Tue, Jul 22, 2008 at 5:29 PM, Marc Cantwell
<marc.cantwell at chinookmaterials.com> wrote:
> I am trying to figure out how to design a database that has items that
> contain other items (a basic kit or BOM).  In that, I need to be able
> to reference the item description of the child items in the parent
> item.  A further complication is that each item is associated with an
> item category.  Each item category has unique fields that when
> concatenated represent the item description.  I have no idea how to
> diagram this.
>
> Ex.
> Item Name               Item Description                                                                                Qty                             Item Category
> Item1                   Double 3-0 x 7-0 1 1/4 SC Primed 7 1/4 FJP Jamb         1                               Finished
> Wood Door
> Item2                   3-0 x 7-0 1 1/4 SC Primed                                                               1                               Door Leaf
> Item3                   7 1/4 FJP Jamb                                                                          1                               Jamb
>
> In terms of Quanitities Item1 takes 2 of Item2 and 3 of Item3.
>
> Item1 takes the concatenation of Item2's description (category Door
> Leaf first followed by item category Jamb)
>
> Item Category Door Leaf requires a number of fields that are
> concatenated into the item description they are:
> Door Type : Double, Single, Pocket, Bypass, Bi-fold
> Width: 3-0 (three feet 0 inches)
> Height: 7-0
> Thickness: 1 1/4
> Door Core: SC
> Door Finish: Primed
>
> Item Category Jamb requires a number of fields that are concatenated
> into the item description are:
> Jamb Thickness: 7 1/4
> Jamb Material: FJP
>
> There are more characteristics for each of the Item Categories, but I
> only selected a few for purposes of example
>
> In order to design this properly, I need to know what tables I need,
> and what the primary and foreign keys are?  I'm kinda stumped.
>
> Thanks,
> Marc



More information about the thelist mailing list