0

I'm creating a POS like system and I'm not really sure how to do the Shopping Cart part wherein after the cashier enters all the customer's item (from Inventory table), the items entered will have a single Transaction #, just like what we see in the receipts.

Should I put a Trans_No column in the Cart table? If yes, how will I handle the assigning of a single Trans_No to multiple items? I'm thinking of getting the last Trans_No and increment that to 1 then assign it to all the items in the shopping cart of the casher. But there's a huge possibility that if 2 cashiers are simultaneously using the system they both retrieve the same latest transaction # and will increment it both to 1 resulting to merging of 2 customers' order in to 1 single transaction/receipt.

What's the best way to handle this?

Harambe Attack Helicopter
  • 762
  • 3
  • 18
  • 44

1 Answers1

1

The data object on which your transaction id goes depends on the functional requirements of your application. If whatever is in a cart should share a transaction id, then the cart table is the right place for the transaction id.

Database systems offer a variety of features to prevent the concurrent increment problem you describe. The easiest way to avoid this is to use a serial data type as offered e.g. by PostgreSQL. If you declare a column as serial, the database will care for generating a fresh value for each record you insert.

If no such data type is available, there might still be a mechanism for generating a unique primary key for a record. An example is the auto_increment directive for MySQL.

If all these are not viable for you, e.g. because you want to have some fancy logic of generating your transaction ids, putting the logic of reading, incrementing, and storing the value needs to be enclosed in a database transaction. Statements like

start transaction;
select key from current_key;
update current_key set key = :key + 1;
commit;

will prevent collisions on the key value. However, make sure that your transactions are short, in particular that you don't leave a transaction open during a wait for user input. Otherwise, other users' transactions may be blocked too long.

TAM
  • 1,731
  • 13
  • 18
  • Hi TAM, thanks for the reply! Thinking of the unique primary key you mentioned, I just created a function that, by the time you added an item to the cart, it will assign a value in a label with the generated id by using the formatted DATETIME plus the user id (for sure this will not duplicate even being used by multiple users because of the added user id in the generated primary key). When checking out the cart with multiple items, it will be inserted in a table with the items' PK got from the label. Thanks for your idea! – Harambe Attack Helicopter Apr 05 '16 at 06:33