Stock management - needs suggestions

Question

I need to create a database which will have below modules:

1. Items [Food items, stationary]
2. Items Stock Management [stock in out]
3. Customers
4. Items Sale Management

Issue:

The system will have different types of items & so can have different 'Unit of Measurement'. Possible Units of Measurement can be bags, pieces, Kilograms. Another things is that certain items can be sold as a whole 'unit of measurement' OR 'part of unit of measurement', for example tomato can be sold 2 kilograms or 450 grams.

Questions:

1. How should i handle the 'Unit of measurement'?
2. What is the best way to keep the stock in out?

EDIT:

For stock in out, i am thinking about to have a separate stock transaction table that will store any in/out transaction. I am still not clear about the 'Unit of measurement'.

Please forgive me if my question is not clear.

Thanks for your valuable time & help.

Answer 1

Not sure how much of help this will be for you. but I had to have an inventory database for a pub. I had similar problem with different measurements, in that at times they could sell a whole bottles and at times n number of liquid units from the barrel. it is not as complex as your problem, but it has similarities.

What I ended with, is defining a few rules:

1. Every product has volume
2. Every product can have a Base product.
3. Every product must either multiply or divide it's base.

Now, let's say they brought a barrel of 20 liters, and they want to sell mugs of 500ml, 330ml or whatever they have for that matter.

They'd first define a single ml of beer with a volume of 1. then they can define a whole barrel of 20kml and based that on the beer while saying that the barrel is multiplying the beer. then if they want to sell mugs of 500ml, they will define a new product based on the 20k ml barrel that has a volume of 500, but this time it will divide from the beer.

We can now buy different quantities from the supplier and distribute it in different quantities to the customers.

Your problem is slightly more complex. but perhaps this will give you a general direction.