how to update all clients after each db transcation

Question

I am developing a Client-Server based app using WPF, SQL-Server and WCF. This is basically a kind of POS application (not using any POS library but features are almost same). Server side coding is almost done. Now I am moving to client side coding. I am new in WCF (first app on WCF) and Client-Server architecture, so I surrounded by some doubts before I begin my coding. According to my Client (Person), he should be able to print item sales bill (Memo) from Client computers as well as Server. Every memo should have a Memo No for that transaction which should increment automatically and salesman should be able to see which memo no he is going to use. So for this purpose I have added a Memo No field on Application Sales Windows which would show Memo No (Last Memo No + 1) from database.

Here comes the problem, suppose there are 3 sales counters and when salesman opened their respective Sales Windows for billing then all of them would see (last Memo No + 1). And if 3 of them bill simultaneously then same memo no will be stored for 3 different transactions.

I must tell you that I have 2 tables in database to store sales. Table 1 stores discount, grand total, salesman id and Memo no and Table 2 stores memo no, item code, quantity, bill date etc. So its Memo no which binds two tables and if this get manipulated then you know what mess it can create in Monthly Reports.

This situation in overflowing my brain and came here to get solution. What I can do to overcome this. Any suggestion, link, code will be very helpful.

Answer 1

You could consider amending your design so that a memo number is assigned when a client begins a sale.

One way to implement this would be to use another table to store the sequence of memo numbers:

CREATE TABLE MemoSequence
(MemoId int identity(1,1),
 DateAssigned datetime
)

Each time the sales window is opened on the client, the current date/time should be inserted into MemoSequence.DateAssigned, and the corresponding MemoId value returned (using an OUTPUT clause or SCOPE_IDENTITY()) to the client as the MemoId.

That way, each client would get a unique memo number assigned from the start of the transaction.

NB - this may not be a suitable solution if it is a requirement that there are no gaps in the sequence of Memo numbers.