Our application allows users to read/add/edit/delete table records through the UI. To prevent concurrent users from editing the same table row (stale record scenarios) we use a row identifier and row version field in the table. These operations are now exposed through web services too. In order to reuse code the read api was designed to return the row id and row version values from the database so that the same values could be passed in the adjust api to identify a row in the table. So if a user wants to edit rows in a table, he needs to first fetch it using the read api to get the row id & row version for each row. These values need to be then passed to the adjust api call so that the system identifies the right row and perform the edit operation.
I understand that there are two issues with such an api design
- Internal database details are exposed through API's
- Performance - currently the adjust api call needs to be coupled with the read api call.
Business wise we do have a concept of key fields in the table which define functional uniqueness. We could use these user defined fields to identify the row but how would we prevent concurrent row updates without row version?
Do you have any suggestions on how could the api be designed to cater to the business requirements? Any real world examples that could be referenced.