How to avoid paying for the same item multiple times

Question

I'm trying to draw a database design of an ecommerce, and fulfilment of order platform. The company currently has a distribution centre for fulfilling the orders. But they want to extend this to use its stores for a part of the fulfilment process. I have designed a database of "internet sales" and "store sales", but I am stuck on the fulfilment of the internet order, and I wonder if any of you can help me with this.

Scenario : When the customer places in an order, and the distribution centre doesn't have a stock of an item to ship to the customers, the item needs to be taken from one of the stores. This item is then sent to the customer.

But the problem is that I can't just take an item from a store, and then send it to the customers, because the item hasn't been sold in the store, its (store) stock database isn't going to be updated. If I put the item through the cash machine, the item is removed from the stock table, but there are two transactions for the same item - one transaction from the internet, and the other from the store.

I guess my question is, how do I go about processing internet orders, and avoid having two transactions on the same item?

Any helpful pointers on this issue is greatly appreciated.

Update : Here's what I have done so far after advice from Jo Douglass,

Database Design Here

Sorry, I can't post images, because I don't have enough points. And please note that the above database design isn't complete

Answer 1

One idea is to go ahead and treat the item as sold from the store (through an online transaction) and credit the store's account with the sale price. The distributor has probably already received the wholesale price from the store so it's happy, the store gets credit for the sale (with at least some part of the shipping charges) so it's happy, and you don't have to create new transaction codes or any other modification to the existing database.

Answer 2

It sounds like you have a Transaction entity, and you have or are planning on having some logic which ensures that when one of these is created for an Item, your system knows to deplete the stock level for the relevant location (either a store of the distribution centre).

You could use an entity which shows an Item being transferred from one location (a store) to another (a distribution centre), and then create some logic which works very similarly to your existing logic - depleting the stock level in the starting location, and increasing the stock level in your destination location. Then when you carry out the last part of the process (sending the item to the customer), you'll have a Transaction showing that and depleting the distribution centre's stock level. Depending on the rest of your model, you might carry this out via a change to the Transaction entity, or by creating a new entity altogether.

Alternatively, if that doesn't really model what's happening in the business very well, then maybe you just need to modify your logic (and possibly your model - hard to tell without seeing your existing model). Rather than only being able to create store transactions via use of the cash register, perhaps you simply need to be able to create a store transaction that's been kicked off via the Internet.