Point of sale app architecture advice

Question

hi everyone! I need to develop a point of sale app that will be used on several locations. In every location it will be use a single database and it will be, in another location a master database with all cashflow and inventory moves. The restriction is that the available internet connection between nodes is very poor, so in certain way all locations the most of the time will be working offline, that is, without internet connection, and when the connection will be available again, will resync with the master database.

I was thinking to develop an architecture with a single database in each location, and implement replication between the master db and others db, but when the internet connection go offline in the nodes, they need to continue working on offline mode until internet connection will be available again. I was not sure if replication is the correct approach to this schema, because the nodes will be inserting and updating the local db and need to propagates the cashflow data and inventory data to the master db and to the other nodes.

what will be the right approach in this schema?

I plan to use dotnet and MSSql Server 2k8

Regards

Answer 1

A couple of tips that I can share with you on this in designing the infrastructure, but first you need to determine the very first and important number one factor - security, which I will emphasize the scenario and my experience of it.

Security is going to obviously be incorporated somehow from the ground up. Think about it - the approach to having a database in each location is a NO-NO. Because a) Stock Control - if the stock control is going to be localized, there's a very strong chance that a fiddle can happen on this in order to 'artificially' inflate the profit/loss margins for the sales transactions. b) There will be situations where the price of a product can either be or both, same barcode or different barcodes despite the identical packaging - this can happen quite easily - you scan something, you swear it is in the system, and end up wasting hours trying to figure it out until the barcode was changed. c) The product can have the same barcode, but the price will have changed to reflect the market conditions - this can lead into a wild goose chase trying to work out whether to markup the existing stock to the new price, or wait until the old stock is exhausted, then put in the price change.

This will bring up a hairy situation in how to handle this and what to do - thus security must be enforced - not by computers, but by humans, a simple human error can happen, such as receiving an outer of products that have the same barcode but the price either increased/decreased and put it up on the shelf...this can inconvenience and stunt the flow of POS from customer taking the product, going over to the counter, scan it in, pay the cash...you get the drift.

Thus, this is where the security must step in from the ground up, it is imperative that you tread very carefully and design this correctly as an incorrectly designed POS (even if it does work) can lead to cashiers fiddling with the stock levels, profit/loss, take cash from the POS...Also, how would the security be enforced in regards to the flow of cash coming from the POS...think about that...a fiddle can occur there...by bypassing the POS system completely and put the cash into the pocket...

How do you approach in sync'ing up to the master system, this will have to be done outside business hours depending on how many transactions were committed, furthermore a sync operation could take time hence outside of business hours, no point having it run during the day when there's customers waiting to pay for their purchases.

Security also needs to be considered in the event of 'I have the database locked down - well and good', but what about the transmission of the data, can be easily intercepted...no matter what you say, there WILL always be a tech-savvy operator operating the POS...If a problem occurs on the POS system itself, the chances are that the operator will 'consult' with outside people to fix it as they would probably feel like 'I do not want to hassle the support team - they are probably sick of me pestering them', this underlines and summarizes a number of things:

1. Security, from ground up
2. Training, must be rigorously followed up, on common sense
3. If in doubt ask the seniors...some actually do not bother to do that and assume the operators know what they are doing...
4. Need to eliminate human errors and conditions which are outside the control of the POS, market fluctuations in prices of products, barcode errors
5. Last but not least, design the UI to be as simple and friendly as possible with zero frustrations such as refusing to accept inputs etc... you get the drift...

Answer 2

I would consider a queueing system as an alternative to a local database. I'd expect such product to offer features, for automatically starting transmission of messages, as soon as a connection is available. But this is really just a technical issue.

A couple of things you should consider when building your architecture:

• are there limits on how out of sync your application is allowed to get? E.g. when your app hasn't connected to the master for a month, is still supposed to run without a hitch? You must allow for appropriate storage capabilities on the client, and ways to escalate the issue, when a threshold is met.

• You are bound to get conflicts. E.g. when you accept an order, it may sit for 4 hours on the client, and when it finally reaches the server, the product ordered might not even exist anymore. Get creative on what might get wrong and define how these cases are supposed to get resolved.

• Make sure you have decent logging, especially for stuff that is crossing the connection. You should be able to easily find logging information on server and client which relates to the same business transaction.

• Make sure you can test you components, without the others.

• And of course in a distributed system you have to access how trustworthy a client is. I.e. how can you assert that a message coming from server or client is really coming from a client and is not forged by somebody or something else.

• Clearly quantify how much bandwidth you have available, so you can make sure that all the required data actually fits in. Many small transactions might help, since they are less likely to get interrupted by a interruption of connectivity.

You also might find this book helpful

Answer 3

What you need is a local cache which syncs asynchronously with the central database.

The local databases you are proposing are just a possible implementation for such a cache. There are other options like message queues or probably even simple text files.

Doing that with raw database replication kills any means to sanity check whatever comes in to the central db. I rather would recommend building the sync functionality in the application logic.

Answer 4

You might want to look at IBM's Retail Integration Framework and IBM WebSphere Remote Server as the software bundle. Basically it uses Message Queue for the sync.... with local databases and app server in the store. That's what Walmart, Target, Kroger... all do.