Apparel matrix

[Kurtismonger]

I'm trying to setup an apparel matrix that will allow me to validate user entry on my inventory database.
We have garment styles and each style can have a variety of different colors and sizes. Some garment styles are available in some colors/sizes and not others. I created a table each for Style, Color and Size which contain the possible values for each of those. I then setup a form that lets the user pick a style and then in a subform create however many combinations of color and size they need. From this I would like to generate an item number such as 1100-Red-XL(style-color-size). I would also like to create a table from this that is essentially a list of all possible item numbers. Then later, when the user is adding/removing inventory I want to check to make sure that the style/color/size combo they are trying to Add/Remove exists.
I would also like to use the item number list to generate inventory status reports.
I'm stuck at how how to take the matrix form data, concatenate each style/color/size into a single field and then generate a new table with that.

 

[Pat Hartman]

I would not concatenate the attributes. That leads to numerous problems. Keep each one as an individual field. You can then use small combos to choose values rather than having to generate a massive list of all possiblities.

Use an autonumber as the primary key for the table and create a compound unique index to the combination of fields that uniquely defines the item. If you don't know how to create an index with multiple fields, post back.

 

[The_Doc_Man]

This problem has been discussed many months ago in an inventory database for someone's T-shirt shop. Search this forum for "Inventory" from at least two years ago. (Pat, are the older articles still available?)

Your problem is that your attributes are going to weigh you down. It will be tedious. But not impossible. I agree with Pat. Drop the idea that you will use the attributes as part of the code. Dollars to donuts says that at some point you will run out of room in your key field if you take this approach.

The way I would do it,...

For each attribute, you need an attribute translation table that tells you a code number for the attribute and the text equivalent for same. The code number will be the prime key for each attribute table. The same code number will be a foreign key in the master inventory identification table (see below).

One of the attributes will be what the garment is - like "pants" "shirt" "socks" and you can have a code for each class of item. If you want to subdivide this into "short pants" "long pants" "gaucho pants" "short shorts" "bikini" - you can do this easily. One code per garment type.

Another will be color/pattern - like "red" "green" "pink" "yellow with red trefoils in saltire" (Heraldry term...) Define as many color/pattern combos as you wish. One code per entry.

Another will (perhaps) be label/maker - like "Schumacher" "Tom McCann" "Dexter" "Ariat". This one might include two keys - the unique code number AND a foreign key that matches the garment type. In which case all of the entries I just named would be "Shoe/Boot" makers.

Another table might be your sizing table. Here again, you need the garment type in order to validate the size codes because shoes will have numeric sizes with a secondary size that is really a width. Long-sleeved shirts will have sizes and sleeve lengths. T-shirts will have either numbers or S,M,L,X,XX, beached-whale, ... so the garment type would be a filter to help validate sizes.

Now you need an "master inventory description and availability" table that has a record number that becomes your master key for the item and attributes that describe the item. This might actually have NOTHING in it EXCEPT for code numbers and the master record number. But once you choose the attribute codes, you can search this table for all records that match the combination of codes. This produces a list of garments with the same attributes. If you have a couple of attributes in your query, you might get a long list of garments. If you have a lot of attributes in your query, you might get back a very short list of garments - or an empty list if that given combination is not a stock item.

Search the forum for "Inventory" to see how to track product on hand. You would track it based on the master availability table number, NOT the attributes.

If you are not sure why this simple problem suddenly got so complex, you need to Google-search articles on Database Normalization. Every attribute you choose to track adds to your problem's complexity. And you have lots of attributes that you mentioned, not to mention any others that might crop up during program development.