Multidimensional Array fits in a Table??

[Ortegahey]

Hi
That's the question I have been challenged with.
I have a VB6 program for a clothing wholesaler who asked me to modify it so that every garment could be stored with prices, description and all that but also with the right ammount regarding sizes and colours. And that is the problem To put it more graphically:
S M L XL
Black 2 5 1 0
White 8 2 3 4
Pink 5 4 2 2
etc.

The question is: Can the identification columns coexist with the stock (in sizes and colours) in the same table, in a kind of 3D table?
These data can be easily handled in an array, but the problem comes with storing them. It seems there is no way to make it but using two tables, one for identification data and another related one for stock, like this:
IdArt Colour Size Quantity
1 Black S 2
1 Black M 5
1 Black L 1
1 Black XL 0
1 White S 8
1 White M 2
and so on and on...

I have read something about this multidimensional arrange of data here , but I am unable to make any sense out of it.

Any help or different point of view is great, please feel free to leave your comment.

 

[Banana]

I'm afraid I don't quite see how a multidimensional array would be necessary.

A table (actually more like a query, to keep things normalized) can just have those columns, and there is nothing wrong with multiple tables like this:

tblColors -Enumerates all colors
ColorID
ColorName

tblSize - Holds all different possible sizes
SizeID
SizeName

tblGarmentType - Identify whether this is pant, shirts, etc.
GarmentID
GarmentType

tblBrand -Names different brands a store carry.
BrandID
BrandName

Those are our base tables. Now to make them a bit useful, because you've actually got many-many relationship.

First, connect Brand to Garment, as a brand can be on several kinds of garments. But suppose one brand only make shirts while another made both shirts and pants while yet another jeans only? Then we have to consider that one brand can make four different kinds of jeans.

jctBrandGarment
BrandGarmentID
BrandID
GarmentTypeID
GarmentName
Description

Now we have a specific kind of garment, we need to describe colors and size it comes in. Some may have so many sizes, while other has less range, or use totally different kind of sizes (e.g. XS, S, M, L, XL vs 28 x 30, 30 x 30, 30 x 32, 32 x 34....)

jctGarmentSize
BrandGarmentID -- Note that we are referring to the previous junction table here!
SizeID --This is a compound key to prevent duplicates

jctGarmentColor
BrandGarmentID
ColorID

Now finally, we need to have a table with stock on hand. I'm afraid I haven't done a inventory database, so I'd strongly advise you to read Allen's great piece: Quantity on hand

So a query with calculated field may be actually appropriate to keep maintence simple but either way, it'll look like this:

BrandGarmentID (BrandGarmentID+SizeID) (BrandGarmentID+ColorID) Quantity

The two columns in ()s are compound keys to ensure that we only show sizes and colors that this garment actually comes in and never other color that it isn't availab le in.

I hope that was helpful.

 

[georgedwilkinson]

Hey Ortegahey. I read the link you posted and the supporting information.

You should, at all costs, avoid that web site. The information is good but it is way beyond what anyone could/should expect to do.

The business problem you are trying to solve has been solved numerous times, using relational technology. It works well, doesn't suffer from the link's description of what's wrong with relational technology, and is real...not theoretical.

Banana has given you an excellent starting place. If you need additional help with the relational method of solving this, let us know.

 

[Ortegahey]

Thanks everybody
I think banana's approximation is the best to use and, as soon as I can (I am working in 3 projects simultaneously, have two babies and building a house), I promise to sink into it 100%.
I will let you know the results or, if I have any problems or take a wrong detour, you will see me again asking for help.

Thanks again for your time