Help with inventory database

Hello,

I am designing an inventory database(in Oracle) for a government agency. I have a few doubts about
how items should be taken out of inventory and put back in inventory.

I would appreciate if some one can give me suggestions after reading the below:

First,a transfer order is generated to transfer property.

Each transfer line item has information that indicates the type of item, number of items
and acquistion cost for each item.

The same transfer order is used to recieve the property. i.e number of items received is
indicated on the transfer order.

When the property is received, it is classified depending on the item's value and
a service charge is determined.
If the item is assigned as a controlled item then they are assigned control numbers.

There is a one to many relationship between a transfer line item and inventory item.

For example, if there are 360 pairs of socks in transfer lien item, they can be put in 6 boxes.
However, since the user likes to issue 3 boxes on the invoice, I have decided to
treat 6 boxes as a single inventory item with a quantity of 6.

For example, if there are three trucks of the same type in a transfer line item, the three
trucks can be issued three control numbers and there are 3 inventory items with quantity of
1.

The acquisition cost associated with each property item is adjusted occasionally.
My first question, is it a good idea to store the acquistion cost in the inventory table
? This value needs to be computed using the acquistion cost of the transfer order line item.
Normally, it is not a good practice to store computed values.

However, in this case it appears to be necessary because this value tends be used
extensively while issuing items in an invoice, for internal use, etc. Also, this value
is adjusted .

My second question, should the quantity field in inventory reflect the item on hand.?

For example,
if three boxes of socks have been donated.

the inventory item would be adjusted to have a quantitiy of 3 and a record is
created in the inventory transaction table indicating that three have been donated.

Or

should the quantity field in inventory be left unchanged?

For example,
if three boxes of socks have been donated.

the inventory item would still be a quantity of 6 and a record is created in the inventory transaction table indicating that three have been donated.

My third question is, about the acquistion cost value which tends to be adjusted ?
However, any answer to my second question should answer this question as well.

I would apprecaite any help or sugggestions.

Thank you.

Reddy Received on Sat Dec 13 1997 - 00:00:00 CET