Question Locked

Access clothing matrix

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi
I am trying to develop an Access database for a clothing department.
Does anybody know how i would go about including or setting up a matrix for Size and colour.
I have a range of fashion products which each have a product code say 556762 but need to add on particular sizes and colours of the product.
I then need to run a report to give me the breadown of this.
Don't know why i got lnded with this one...I'm a bloody network guy....Thanks

5 replies

by tony hopkinson

If yuo do a teeshirt in sizes S to XXXL, is it aavailable in all the possible teeshirt colours.
For instance if you do size S in red, green, and black , but XXXL only comes in black...

If the above isn't a consideration then
you need two more tables
ProductColours keyed by ProductCode and ColourCode
ProductSizes keyed by ProductCode and Size Code.

To be really nice, you could have a Sizes and Colours Table as well for storing any extra info, such as might be.

If you only do certain colours in certain sizes of a product, then have
a ProductOptions Table keyed by ProductCode and OptionCode, then and Options Table keyed by OptionCode with a ColourCode and a SizeCode field.

HtHs

by tommykav

Hi Tony

Mnay thanks for your reply and apologies for not getting back to you any earlier.

I have many products but they can all use the same colour and size option when inputting the product data.
I think the idea you have of a seperate table is a possability.
The product sizes would be driven by the product category....I.E..
Womenswear > size range 8,10,12,14,16,18
Shoes > 4,5,,6,7,8,9 etc
Menswear 38,40,42,44,46,etc....

The colour would cover all the categories..

Hope that makes sense....

What do you think..

Cheers

Tommy

by tony hopkinson

There's a concept in database design called normalisation, it gets really techy when you look it up, but in most situations it's common sense. One table for each entity.
So
Products
ProductId
ProductCategoryID
Size
Colour
....

ProductCategories
ProductCategoryID
ProductCategoryDescription

Sizes
ProductCategoryID
ProductSize

Colours
ColourID
ColourDescription

It looks a bit over the top, but it's future proofing

You should also resist using variable length strings for keys and sort indexes.

The design I used above without ProductCategtoryCode

Would have Menswear in every menswear product and every menswear size. That's 8, btytes per instance, 16 if you are on unicode. An Integer 4. Less space, quicker to load, to sort, to look up and if you boss comes along and says, I want it to be
"Mens wear". You only have to change it in one place.

The disadvantage, you'll be doing a join or two for the front end

Good habits are as hard to break as bad ones.

This discussion is closed to new replies.