Hello Fabio,

At 11:46 PM 28/07/2006, you wrote:

>You have a point.
>
>What i expect is to have a faster access to the stock quantity for each
>serial number in the database... i.e.
>
>I m selling a product, instead of making a select sum() on the stock table,
>i can just check the products table for the current quantity, and i want
>make this using the serial number, for each serial number i ll have a
>current quantity thats why i have the second table.
>
>That second table is nothing but the sum of all the records on the other
>table wich have a certain serial number, so i dont need to make a select in
>N rows, instead i select just ONE value from that table.
>
>Did you undestand where i m trying to get?

Let me give you a scenario.

Say I am running a knitting yarn shop. I stock hundreds of different
types of yarn, so my system groups and classifies them in a number of
ways. For example, manufacturers include Cleckheaton, Patons and
Sirdar. Each manufacturer offers a huge range of yarn types. Within
each yarn type is a range of different colours. So, we set up our
tables to accomplish these groupings, taking full advantage of our
relational database.

However, at the very lowest level, each individual ball or skein of
yarn has a batch number. We hold many skeins of the *same* batch
number, because it indicates that all of these skeins were dyed in
the same lot. So, for example, if a customer needs 10 balls of
Patons 4-ply baby wool in "Spring Green", it is no use to that
customer if we are unable sell her 10 balls of one batch number. 5
balls of batch 2306 and 5 balls of batch 2310 are no good, because
there will be a shade variation that would spoil the work.

That means that, for each Stock_item that is "Patons" + "4-ply baby
wool" + "Spring Green" we must also hold a record in another table,
e.g. Product_Batch, that has a count of our stock of this exact item
*for each batch number* that we have. We might keep replenishing our
stock of "Patons" + "4-ply baby wool" + "Spring Green" for many
years, but the batch numbers will change frequently.

Therefore, whenever we sell n balls of "Patons" + "4-ply baby wool" +
"Spring Green", and whenever we accept new stock or customer returns,
we must update our stock at this lowest level, i.e., in Product_Batch.

There is a mandatory 1:Many relationship between our Stock_Item table
and Product_Batch. There cannot be a Product_Batch record for a
Stock_Item that does not exist. Product_Batch will have a foreign
key that refers to the primary key of Stock_Item and that
relationship will take care of the mandatory nature of the relationship.

The batch number is an attribute of the Product_Batch entity, not of
Stock_Item, and there is no logical place for it to be stored in
Stock_Item. We could place a trigger on Product_Batch, to update
some kind of "total stock" of "Patons" + "4-ply baby wool" + "Spring
Green" each time the quantity of a batch record changes, or when a
quantity of a new batch is added, but this is neither desirable nor
necessary.

You should *not* use the batch number (your "serial number") as a
primary or foreign key. They are external numbers and they have
semantic meaning. You should use generated sequences for your keys
and you can place a UNIQUE constraint across the foreign key and the
serial number to ensure that you have only one record in
Product_Batch per serial number.

p.s. Yes, I'm knitting furiously for a forthcoming grandchild at the
moment. :-)

./heLen