Need Help to create DB Tables of Inventory with FIFO valuation methods

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

Heloo Guys..
I am working on a Inventory project for catering projects. The products are flour, rice, juice, Meat, Fruits, Vegetables, etc. Here the products are consumed/issued.
We purchase items at difference price at different times.
For example :
Juice Bottle bought @ $10 of 200 units.(On Jan 1st 2012)
Juice Bottle bought @ $12 of 500 units.(On Jan 10th 2012)
Juice Bottle bought @ $15 of 300 units.(On Jan 20th 2012)

I want to use FIFO inventory valuation method on this. I can’t use any other methods like Weighted Avg.
Using FIFO : At this point the consumption of 200 units should be of value $10, again if 100 units are consumed then the consumed value should be $12,etc.
How to build database tables on this approach and how to link the tables?
I am using SQL Server 2005 with VB6. At the end of month, I need to display Weekly/Monthly Consumption reports.

I have made following tables so far :

tblProductsMaster :
Product Code(PK), Product Desc, Qty, UOM(FK), Min Stock Lvl, Reorder Lvl, Category, Price.
Every product is unique record in tblProductsmaster.

tblUOM :
UOMcode(PK),UOMName.

tblConsumption :
ConsumptionNo, RequestedBy, RequestedDate, ApprovedBy, ApprovedDate > Header Fields
Product(FK), Product Desc, UOM, Qty, Price > Line Items

Products are entered in Products master having different prices. I don’t need purchase tables, since I directly take the products in tblProductsMaster.
I also need to implement Base UOM, Alternate UOM, Conversion Factor. This is secondary. How to make tables and link them? I don’t know much about creating database tables and linking. Please someone help me……………. It would be very grateful if you help on this matter.

Posted 31-Jan-12 1:05am

tonydsouza1987

Updated 2-Feb-12 0:06am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

krumia · Accepted Answer · 2012-02-10T06:10:00

I think you need to think of your items as batches.

Create a table for batches. In this table, you will have all the batches of items. And you need to bring the price and quantity columns into this table. Introduce a sequence column to identify the sequence of batches.

When you consume items, you can find the records for that item in the batches, locate the record of the minimum sequence number, and reduce the quantity.

But, before doing any of this, try to rethink your project. I think you have serious design problems. Think it over forgetting the implementation details. Draw a few class/entity diagrams. That should help.