SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Inventory Management System


Inventory Management System

Author
Message
srikanthkamuju
srikanthkamuju
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 7
Hi All,

Basically i don't have knowledge in Inventory Management System. so any one can help me the flow of inventory management system and data base design for small business, like invoices purchase orders, stores

Thanks in Advance
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7821 Visits: 25280
My suggestion to you would be to create a requirements document.

Some simple questions which need to be answered before starting the database design would be:

1. What model of a system - first in, first out or last in first out or both flows be implemented with first in, first out for products which might have a "use before date". Or the third option which is neither first in / first out or last in/first out.

2. Is product cost to be tracked in the inventory system, so that the inventory value can be arrived at for fiscal period accounting purposes?

3. Do you have to track which item and number of, are shipped to what outlet (store or customer)?

Additional items added at 12:35

4. Will the inventory system have to calculate and set re-order levels for stocked items? Then when stock on hand is at or below the re-order point generate a report for purchasing.

5, Are stocked items seasonsal items / non-seasonal or a mixture of both. Seasonal would be something like bathing suits, while non-seasonal might be nuts and bolts.

And as you familiarize yourself with the complexity of a good inventory system you will find more questions that must be answered before starting the design of your database.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62466 Visits: 19102
You might pick up a copy of this as well: http://www.amazon.com/Server-Relational-Database-Design-Implementation/dp/143020866X/ref=sr_1_1?ie=UTF8&s=books&qid=1260635050&sr=8-1

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86009 Visits: 41095
Heh... look in Books Online and the AdventureWorks database that comes free with SQL Server. ;-)

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
briogene
briogene
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 194
You could try going for this book at this link
http://www.vkinfotek.com/
ramalakshmi
ramalakshmi
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 0
Hi All,

Inventory Management Flow

Item Master
Item Name , Item Code , Make , Specifications, Opening Bal,
Qty in Hand, Reorder Level , Cost Price , Selling Price

Bill of Material ( if Manufacturing Company)
Note: Not Required if Trading Company

Purchase Order to be Placed to Supplier for Insufficient Qty

Material Inward from Supplier - Stock to be added. Purchase Order Status should be completed.

Material Outward to Customer - Stock to be Reduced.
Invoice (Bill) , DC( Delivery Challan) , GP ( Gate Pass) should be
generated.

Purchase Ledger

Sales Ledger

Daily Stock Ledger with Inward and outward of Material

Analysis Reports
1. Supplier Wise / Manufacturer Wise Sales
2. Item Wise Sales
3. Customer Wise Sales
4. Stock Report etc.,
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86009 Visits: 41095
This isn't a subject to be taken lightly... inventory management is complex and frequently changing. It's not something that can be learned correctly in a couple of hours especially if you don't have a very, very good background in database design.

My suggestion would be to get some professional help on this one.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62466 Visits: 19102
Very well put, Jeff. There are multiple ways to handle inventory, and modeling is not something you learn in a day. It takes time, and if you've never done it, you ought to have someone that has experience work with you and review your designs.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Alvin Ramard
Alvin Ramard
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4137 Visits: 11639
I agree with both Jeff and Steve, but I'd like to add that their comments apply to more than just Inventory Management System. Database design is not something that should be taken lightly. A poorly designed database can break a system, especially as the number of records increases.

The book referenced by Steve is a great book. I have the SQL Server 2005 version. It will certainly open your eyes to what you need to consider in your design.



Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help
Steve-3_5_7_9
Steve-3_5_7_9
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 1599
Exactly.

My MIS degree program devoted 2 semesters (courses) to Systems Analysis and Design.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search