Subject RE: [firebird-support] Design Question
Author Leyne, Sean

> I have 'Item' table with following fields,
> Stock
> Cost
> Price
> ReorderLevel
> ...
> Whenever an item is sold, it deducts the quanity sold from the stock field. But
> in some special case two different records has to share the stock field, let us
> say Cigratee Pack and Cigratee Carton. Which ever item sells, stock has to
> reduced in both item rows. How could we do that

There are a couple of ways:

1 - Separate the definition of the items which are sold and the inventory items. In your case above the Cigarette Carton is nothing more than X number of Cigarette Packs. So, that when a Carton is sold, you reduce the number of Packs by X.

2 - Create a "container" relationship between the Cigarette Carton and the Packs. Defining the number of items inside the container. Then, when the "In Stock" quantity of Packs falls below 0, you know that the only way that the item could be sold is if a Carton was broken up, and thus you would reduce the Carton "In Stock" quantity by 1 and increase the Packs "In Stock" by the number of items in the container.