Hello John,

>Hi,
>Thank you.
>I am not quite understand on this:
>
>>>1.Products contains a product ID and a category ID - the first to link to Styles, the second for reporting purposes.
>
>

Do you meant 1st is product ID, and second is cateogry ?

PRODUCTID is my primary key that links to Styles. CATEGORYID is a character field that provides for report grouping.

>>>Styles contains each unique product you sell by color, style, size. I used an integer Primary key, modified to generate a second character field that becomes a barcode. This table also contains a matrix field C(1) that links to the sizing table. As of this moment, the Sizing has 8 records by 18 fields, giving me all the combinations I need for reporting purposes
>
>

How is the table structure for this? Can you send me the table strucute
>for this? I think that will be more clearer. Thank you.

The basic structure to support the above is:
Styles table
bcodebase - Integer PK, default value generated by a "newid()" stored procedure in DBC
barcode - C(20), created with a location code and bcodebase, like "NY"+STR(bcodebase)
productid - C(15) link back to products
sizematrix - C(1) pointer to "sizing" table
colorstyle - C(15)
size - C(5)
instock - N(4)
sellprice - N(6.2)
and a few more that are application specific.

Sizing table
sizekey - C(1) see Styles - I use "A", "B", etc.
size1 - C(5)
size2 - C(5)
.
.
.
size18 - C(5)
descript - C(30) note to describe this "size" definition

If you wish, I can send you the GENDBC output so you can view this in more detail. Be forewarned, there will be a lot of 'baggage' :>)

>When you sell/recieve the item, do you let your client to select the matrix too? How do keep the stock? Does every item with different colour, different style and different size will keep it own qty, or the qty will be increased or decrease base on the product id only? If your client want to know about which colour of the particular product is fast moving, does your program able to show it?
>

When the client sells something, the only thing they need to know is the barcode. The complete definition is held in Styles and is all that is needed to sell the item. The stock is maintained in styles with the 'instock' field. All the remaining tables support reporting, reordering, etc. The POS in essence could be driven with just "Styles" table. One of the main driving requirements of the client was to quote: "If I don't have the exact color or style the customer wants, let me show them all I have that is similar. I want to sell them something!" I think you will find that those "fast moving" products type of queries will not apply very well in this case. With seasonal changes in styles and colors, it can become pretty meaningless very quickly. If you sell a stable product line, fine. My "categories" provide enough detail to keep my client happy in this regard. How are slacks selling here? Or shoes over there? This type of thing I summarize in a day end report...

>Do you think client want to know about the stock status with very clear (what product with diff colour, diff size and diff style), or they just wan know the product doesn't matter with the colour, size and style?
>

With the seasonal changes mentioned, in our case no. This client does have a core stock of certain items and yes, maintaining par levels of these items is critical. But as far as how many "Looney Tunes" blouses in size 5XL he has, no. Another item I track in the 'Styles" table is a date when an item was last sold. Now the client has an immediate indication if a certain item is a dog, and discount it right at the POS if he desires. Yep, he wants to do that.

>Thank you very much. Hope can have a look on your table structure design
>
>:)