Inventory and Order Entry Design

[jestrada101]

Can someone point me to a good place to understand the flow or transactions in a database that keep track of inventory and customer order entries in real-time.?

I'm having a hard time trying to understand a good method to implement a CUSTOMER ORDER entry system that also maintains INVENTORY (Purchase Orders, etc).

Thanks!
JE

 

[lespaul]

first off, databases don't have anything to do with flow and transactions. Databases only relate to the data you are storing. The best advice is to make sure your design follows the rules of normalization, The Fundamentals of Relational Database Design is a good place to start.

I would also look at the sample databases that Access includes to see the table structure of that ordering system.



Leslie

In times of universal deceit, telling the truth will be a revolutionary act. - George Orwell

 

[jestrada101]

Thanks Leslie.. I just found that document in another post. Thanks for the post!

JE

 

[MichaelRed]

with all due respect, databases srtore what they are designed to store. Wheather a specific application design for an inventory system uses a state model or a transaction model is not restricted by the database, but the application design (and program). While an inventory tracking application needs to RESOLVE to a "State", this can be (and often is) accomplished using the "Sum" of the set of transactions about as easily as simply maintaining the "State" after each operation (transaction).




MichaelRed

 

[willir]

To simplify the task, consider your system as three different subsystems...
- contact system (customer database) - simplest
- order entry system
- inventory system (most difficult)

Are you going to track financials? Simple or double entry GL?

Keep your "transaction" processing in one central module. All inventory updates occur at one point in the code, no where else. For example, use a function call to update the inventory. Why? Because it ensures updates are identicle and consistant, and if you change something, you only change it at one point.

You will find well designed subsystems will mesh well together.

Richard

 

[ZmrAbdulla]

I think NORTHWIND DB can give you a better idea on this.

________________________________________
Zameer Abdulla
Visit Me
The best thing to spend on your child is your time.

 

[jestrada101]

I've checked out the NW database and I totally understand that. Now my question is, what is the best method to keep track of inventory.

1. UPDATE the UNITS IN STOCK after an order takes place in the products table.?

or

2. Should I take the difference of how many products have been ordered(by the owner of the database) and how many have been sold?

3. Something else??

Thanks!
JE

 

[ZmrAbdulla]

Don't update to the table. You can find the stock on a query. You could take the difference between Actual purchase and Sales. It is not necessarily order qty and the purchase qty is equal. You may order for 10 nos and you get 5. Did you get the point?
There can be three styles(may be more..)
1)Enter each single item's purchase and sales and take difference.

2)Enter each purchase in item group and and sales then take difference of each purchase
Purchase Sales Balance
Item1 qty 10 nos - 5 nos 5 nos
Item1 qty 5 nos 2 nos 3 nos

3)Total purchase - total sales
TotalPurchase TotalSales Balance
Item1 qty 100 nos - 50 nos 50 nos

________________________________________
Zameer Abdulla
Visit Me
The best thing to spend on your child is your time.