Problem with unit design

Hi

In my inventory system I have a table Units which has 3 fields - UnitID, UnitName, Quantity.

Suppose I purchase any product. I will input that in my Receives table with a Quantity and Unit field. When I issue a product I'll input that in Issues table with a Quantity and Unit field.

Now problem is I will purchase products with one unit like Box and issue product in another unit like piece. A box may contain some pieces.

How can I provide this facility with the following tables ? I want something like, when quering current stock, it should show -

Product Quantity

-- --

Pencil 16 Box and 3 Piece

And while issueing and purchasing I want to calculate the stock. In the Unit table I want to define each unit with the containing smallest quantity, Like -

Dozen 12

Packet 20

Need help!

Regards

Kapalic

Option 1:

Always store the peices on your table. Then you can represent it any unit.

Product QuantityInPieces DisplayUnit

Option 2:

You can change the datatype of Quantity from int to float.

[Box/Dozen].[Pieces]

12.5

12 Box 5 Pieces if displayunit = Box

The floor is always Box/Dozen it may be identified from the Unit column. The decimal numbers always indicate the pieces..

BUT I recommand to use OPTION 1.

Code Snippet

Declare @.Units Table
(
UnitId int,
UnitName varchar(100),
UnitQty int
)

Insert Into @.Units Values(1,'Box',20);
Insert Into @.Units Values(2,'Dozen',12);

Declare @.Product Table
(
product varchar(100),
qtyinpieces int,
displayunit int
)

Insert Into @.Product Values('One', 122,1);
Insert Into @.Product Values('Two', 234,2);
Insert Into @.Product Values('Three', 24,2);
Insert Into @.Product Values('Four', 50,1);

Select
P.product
,Convert(varchar,P.qtyinpieces/UnitQty) + ' ' + UnitName + Case When P.qtyinpieces%UnitQty <> 0 Then ' ' + Cast(P.qtyinpieces%UnitQty as Varchar) + ' Pieces' Else '' End
From
@.Product P Join @.Units U on P.displayunit=U.UnitId