>Hi,
>Thank you for your great ideas. It really minimize alot of works.
>
>I have another similar questions.
>
>1. My application designed to handle fashion products (color and size).
>
>Item A
> + Color Red, Size M
> + Color Red, Size L
> + Color Blue, Size M
>Item B
> + Color Red, Size XL
> + Color Red, Size S
> + Color White, Size M
>
>
>I want to keep track all transaction of item, ex.
>
>
>Good Received 01/01/2004 : Item A, Color Red, Size M
>Sales 02/01/2004 : Item A, Color Red, Size L
>
>
>I want my report able to show stock qty of itemA; also Item A, Color Red, Size M ONLY.
I would say, if Item A will always be Red, and its size will always be M, keep this information only once in the product list. From there, you can JOIN with the sales table (for example).
If the SAME item, that is, with the same product code, might change its description over time, or if you have different varieties that you don't want to reflect in your product table, you need to specify the details in the sales table.
>
>2. How about serialno product?
>
>How would you store it in product, and sale details table?
For the serial numbers: If you have 100 identical products (no serial number), you can keep a single entry in the product table. For the serial numbers, since each product has a different serial numbers, you need a separate table, with one record for each product you want to keep track of.
All this is easy in principle, but much more work for the end-user!
For the basic principles involved in database design (normalization), see my article at
http://www.utmag.com/August2002/Page7.asp. In this case, there is a one-to-many relation between "product" and "serial number" (one product type can have several serial numbers, namely, one for each individual product), so you need a separate table.
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)