Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Inventory Management System Expand / Collapse
Author
Message
Posted Saturday, December 12, 2009 1:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 15, 2010 3:13 AM
Points: 2, 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
Post #833299
Posted Saturday, December 12, 2009 8:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:29 PM
Points: 5,617, Visits: 25,218
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
Post #833337
Posted Saturday, December 12, 2009 9:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 12:38 AM
Points: 33,267, Visits: 15,436
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
Post #833342
Posted Saturday, December 12, 2009 10:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:14 PM
Points: 37,091, Visits: 31,647
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #833431
Posted Monday, December 14, 2009 1:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 13, 2012 2:19 AM
Points: 36, Visits: 190
You could try going for this book at this link
http://www.vkinfotek.com/
Post #833647
Posted Monday, December 14, 2009 2:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 14, 2009 2:20 AM
Points: 1, 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.,
Post #833667
Posted Monday, December 14, 2009 10:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:14 PM
Points: 37,091, Visits: 31,647
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #834302
Posted Tuesday, December 15, 2009 8:23 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 12:38 AM
Points: 33,267, Visits: 15,436
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
Post #834551
Posted Tuesday, December 15, 2009 8:33 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 3,165, Visits: 8,082

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.
Post #834558
Posted Tuesday, December 15, 2009 1:07 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 21, 2014 7:01 AM
Points: 914, Visits: 1,431
Exactly.

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




Post #834727
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse