Optional info in datasheet

[B.L.]

Hi,

I'm trying to put together an catalog database. My
question is, if I have an item with a bit of variable
information, how is the best way to handle that variable?

For example, say I have a clothing store... I have several
brands and items. I create a table for brands, and a
separate table for item. Then, each item has variable info
such as size or color. How do I handle the variable info
for size and color?

EX.:

***Brand_Table***

Fields Sample Data
-------------------------------------------------------
Brand_ID (Primary key) 0101 , 0103
Brand_Name Polo , Levi's



***Item_Table***

Fields Sample Data
------------------------------------------------------
Item_ID (Primary) 010101 , 010301
Brand Polo , Levi's
Item Shirt , Jeans
Available_Colors White|Blue|Green , Blue|Grey
Available_Sizes S|M|L|XL , 36|38|40



Now, in the table, what is the best way to handle the
variable data? (size, color)

Should I create a separate item for each possible
variation? (e.g. 0103010101 Levi's Jeans Blue 36,
0103010203 Levi's Jeans Grey 40)

Or is their some way to include those variables in a cell?
(i.e. "colors_available=Blue,Grey" "sizes_available=36,38,4
0")

Moreover, is it possible to do the latter, and make those
options "selectable" in an order or an inventory?

Or, does each variable necessitate a new item?

Anyway, thanks in advance... Hope that all made sense...

Brad

 

[Jeff Boyce]

Brad

If your model is that each brand can have many items (one-to-many), and each
item can have many size and/or color attributes (again, one-to-many), one
approach is to use a "relation" or "resolver" table that holds the IDs of
the many attributes of each specific item. Maybe something like:

trelInventoryItem
InventoryItemID
BrandID (from tblBrand)
ItemID (from tblItem)
SizeID (from a tlkpSize)
ColorID (from a tlkpColor)

With this kind of table, you can easily query and find all the Items = X,
Color = Y and Brand = Z items.

I'd sure avoid putting multiple values into one field (your "available
colors/sizes") -- Access doesn't have "cells", and is a relational database,
not a spreadsheet. You'd just be making an incredible amount of extra work
for yourself if you did that.

If you build such a table of "possibles", then an order system could include
a form that has combo boxes depending on each other in sequence. That is,
pick an Item from the first combo box, and requery the Brand, Size and Color
boxes to only hold those values for the Item selected. Pick the next (e.g.,
Brand), and again limit the remaining combo boxes.

Good luck

Jeff Boyce
<Access MVP>