Question posted in 
I’m making a Django app using a postgresql database for a family who need to coordinate their grocery purchases.
Say one person has on their mobile device a full list of the groceries and is in the supermarket, buying and ticking each item off the list.
His partner decides to stop at another shop and begins buying the items on the same list.
How would you approach ensuring that they don’t buy double or, more importantly, that they don’t end up messing up the data entirely?
A simple first-come, first-served lock, giving the second user only read-only rights? Or is there a more flexible way?
And if the best way is a lock, how much does postgresql do for you?
2
Answers
I think a simple ‘bought’ or ‘to_be_bought’ flag as a field should be enough, for a user to see if the item has to be bought rather than a lock on transaction, and I think its the more user friendly approach. I think a cooler feature would be having a push notification so all the users that are interested in buying an item could get notified when the item is flagged as bought, so no time is wasted buying the same item twice.
I would have something like ‘products’ table that has ‘name’ and ‘quantity’.
When someone is shopping – he / she will see all products where ‘quantity’ is not zero. When he / she buys something, they will specify how much of that product they have bought, and we will add in Postgres a check that the value can’t be less than zero when we subtract the amount bought.
In that way, let’s say you have a product “carrots” and quantity of “5”, when two people mark they have bought 4 carrots, one will succeed and one will get error that value can’t be lower than 0. Therefore we ensure that the amount bought is never exceeding the amount needed / listed.