Product Inventory

[Anon]

Hey guys,

My roommate and I are currently developing a very small site for my parents bread business. We have most everything taken care of except how to handle inventory. The problem is the quantity that we currently have in-stock.

I have thought of a couple of ways to work it out but they all tend to be bad when one of two things happen: 1) Two customers try to order at the same time. 2) The customer aborts midorder.

Any ideas on how to handle inventory and the current stock?? Using PHP4, MySQL, & Apache on a linux box.

Thanks,

Zach

[Anon]

Zach,

What your a really describing is a transaction in a database system which does row level locking.

Ideally each order should be handled a 1 logical unit of work. This will have the following effects:

1. On cancellation of an order, all the necessary data is rolled back out of the database.
2. Multiple orders will not lock up the whole order table.

You really need a transactional database to do this.

Gives you 3 options:
1. Spend a small fortune on one of the big boys (e.g. DB2 / Oracle)
2. Use the InnoDB capabilities of MySQL which adds transactional processing.
3. Use PostgreSQL which has transactional processing built in.
4. Do a substantial amount of coding to put in integrity checking in your PHP code.

Given that you are running on Linux, any one of those 4 are fine. You'll find that supporters of each db can be quite vocal :-)

It's worth doing a search on the site for examples as many people have written and made available order entry systems in php.

Best of luck,

Justin
P.s. sorry it's so wordy.

[Anon]

That was kind of what I was thinking....although I wasnt quite sure. Thanks for the input.

When reading your reply and some other sites, I get this feeling that I may need to go back and redo my data model and reconsruct the database. I dont believe it is set up properly anyhow.

Any ideas on how to properly set up a database for order processing and transactions?

Zach

[Anon]

Zach,

The highly simplified schema (last count about 20,000 tables / 40,000 indicies in the complete system) that SAP (a large ERP package) uses for it's sales chain is along the following lines:

1. A couple of tables relating to customer (i.e. name, location, various preferences) linked by a unique ID.
2. A series of tables related to the materials involved (e.g. name, description, unit of sale, current availability etc), again linked by a unique ID.
3. A few key tables for sales order (oder details, status, order history)
4. A large number of key pricing tables which govern how the base price on the material table is manipulated to give the final price for the customer.
5. An financial document table (contains invoices, credit notes, etc).

All of these tables have either foreign key relationships or constraints on many of the fields to ensure that the data is correctly validated when entered.

In transaction terms, a sale in SAP is regarded as 1 unit of work, but affects the material availablility table, the sales orders tables and the financial documents tables. All the updates must suceed for the transactionto be fully commited.

Each case though is different, but the basic principles still hold valid. Generally have a couple of customer tables (names & addresses), a couple of material tables (raw materials and current availabilty), a sales order table and an invoicing and payments tables.

If you do decide to use InnoDB, all the setup details are available at http://www.innodb.com

PostgreSQL is available at:
http://www.postgresql.org/

Best of luck

Justin

[Anon]

Thanks for the info.....also found some other info online that helped.

Ok...I have revamped my data model and feel it is getting stronger. I am still running into one issue though (this might not be a database problem). The problem is how to handle actual orders and orders in progress along with orders that were abandoned and what the current inventory is.

Again, I say this is not necessarily a database issue. As a note I am using PHP Sessions to handle user tracking and information gathered in the ordering process.

Any info on how to handle this would be appreciated.....

thanks

[Anon]

Zach,

You're exactly right, this depends on how long the business generally takes to make the final product and how flexible you are with you customers. It's not particularly a database issue, apart from the question of when to make changes to the various tables.

In general, you could give the user 2 options on their order form.
1. Check availability (which when pressed will allow no 2.)
2. Confirm order.

Option 1 works out the total amounts of all materials required to complete the order. It then performs a read on the material stocks table to see if sufficemt material is availble. How a success / failure is handled depends on the way that you would normally do business.

Option 2 does exactly the same, except that
this time the stock table is actually updated to reflect the new quantities. It would also be good to have a store for your own use linking customers -> orders with a data field for order status (e.g order confirmed, in production, production finished, delivered, invoice issued, payment received etc) .

Hope that gives you some food for thought.
If it's more convenient, feel free to email me directly ([e-mail])

Cheers

Justin