(Solved - Sort Of) Mysql Database Table Problem

[Dahl99]

******* I have managed to get around this by not sync'ing the price which I will amend/adjust manually on both databases.********

 

 

I have an EPOS till software that can connect to my on-line shops database remotely so stock levels between the 2 databases maintain the same stock levels.

 

The quote below explains how the EPOS software checks the product id, stock quantities and prices with the on-line shop database. 

 

The problem is the product id and price are in the "ps_products" table  but to actually change the quantity this is changed in the "ps_stock_available" table.

 

I have no idea about databases but I'm sure some one knows how these 2 tables can be linked/sync/joined or whatever so this can work.

 

 

 

EEOnline Web Store Synchronisation Module

The EEOnline module is one of the "bolt-on" options available with the EPOS-Excel system. It enables you to synchronise stock level and price data between your in-store EPOS system and up to two remote web store databases.

How does the system work?

To explain how the system works further lets assume that you have a high street store selling computer hardware. You also sell most of your in-store products online using an online store web site. Although in effect you have two separate shops, all the stock that you sell is stored in a common location (more than likely in the store room at your high street store). So what happens if someone walks into your store to buy stock item 1001 which in this case we'll assume is a PC motherboard...

You complete the transaction on your in-store EPOS system and the stock level is adjusted accordingly. But what about the stock level that's showing on your online store? Well the only way to reflect the new stock level would be to manually enter it using the web store admin interface. That's not too bad if you've only got a few amendments to make during the day but if you're selling larger quantities, stock level adjustments between the two systems can become a real pain.

EEOnline is a method of automating this process. The following flow diagram shows how the EEOnline module deals with a simple shop sale.1 x stock item 1001 (PC Motherboard) sold in store.
V
EEOnline checks the latest stock level for item 1001 on the web store database (found to be 5 units)
V
Web stock level is assumed to be the most recent and is used to set the new stock level.
V
New stock level = web level - quantity sold in store. (5 - 1 = 4 units).
V
New level is now stored on BOTH in-store database and web database.


It's important to note that ONLY stock items that appear on BOTH your web store database AND your EPOS-Excel database will be synchronised. The module will not attempt to update any online stock records that do not appear on your EPOS-Excel database.



What happens when I sell items via my online store?

So you may be wondering how this system works in reverse. What happens when someone makes a web purchase? Well the key to this is that the EEOnline system will always check the latest web store levels before showing you a local stock level. For example lets assume that at the start of the day your in-store database and web store database were both showing 10 units of stock item 1001. However at 10AM you sell two of these motherboards via your online store. If EEOnline is enabled, whenever you perform a stock record lookup using EPOS-Excel, the EEOnline module will download the latest stock level from your web store and show this value as the current stock level. It will also save this value locally to your EPOS-Excel database so that everything is synchronised.

So in the above example, the EEOnline module will return a value of 8 items. This will then be shown to the till user as the current stock level.

 

Edited November 8, 2013 by Dahl99 (see edit history)