Wow.. that is exactly what i m trying to do.. but my english vocabulary is
short and i didnt remember words like "batch" or "Lot" to explain my
situation.

So, what happens if i use a generated ID and an UNIQUE constrain, whats
going to happen when i try to make 2 inserts at same time with the same
"Lot" number? Is it going to be safe this way? just with the triggers and
this logic?

Anyway, thanx for your patience :)

-Fábio.

On 7/28/06, Helen Borrie <helebor@...> wrote:
>
> 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
>
>
>


[Non-text portions of this message have been removed]