Suppose there is a table with 3 columns
id, name, score
Name and score are both unique columns. The use case is that,
- The table is pre-populated with 10 rows. Scores are set from 1-9.
- A user can insert a new row which can either conflict with an existing score or a new score.
- A user can update an existing row which can also conflict with existing score.
Currently, the list of records are small but we do not know if this number may grow in the future (but assuming it will)
Brute force way to handle this in business logic would be, when score conflicts with incoming payload for POST or PUT method, update every other row to score+1.
Another approach is, instead of scores from 1-9 use gapped integers say 1, 10, 20,..so on. This would have lesser conflicts only.
I want to avoid the approach of updating every row with score+1. Please suggest how to handle this .
Update: This is a postgres database.
Suppose table has
id name score
1 | 'A' | 1
2 | 'B' | 2
3 | 'C' | 3
4 | 'D' | 4
5 | 'E' | 5
6 | 'F' | 6
7 | 'G' | 7
8 | 'H' | 8
9 | 'I' | 9
suppose a POST payload contains {'name': 'J', 'score': 10 }, in this case its simply inserted at the end because there is no conflict with existing scores.
suppose a POST payload contains {'name': 'J', 'score': 1 } then as per requirement, the payload has to be inserted and row in the table has to be updated to current score + 1.
ON CONFLICT UPDATE SET SCORE ...., can be used to update every row in case of a conflict but is there a better approach than that? Because, the cost of updating rows can increase if end-user runs a script with POST/PUT payloads to update scores or insert new (name, score) tuples.
When end user does a GET collection, then rows are returned sorted by score, would expect
id name score
10| 'J' | 1
1 | 'A' | 2
2 | 'B' | 3
3 | 'C' | 4
4 | 'D' | 5
5 | 'E' | 6
6 | 'F' | 7
7 | 'G' | 8
8 | 'H' | 9
9 | 'I' | 10
