Versioning database?

Question

I am making a database for a sportsclub, with coaches and players. The players are able to create themselves and coaches are able to create and update players too.

How do I make the edit part, so that only one coach can update a players info at the time. I'm thinking using versioning. Making a version row in each table and updating it whenever it is edited, to make sure that only one person can edit it at the time.

Any other suggestions on how to do it??

Answer 1

Yes . Your proposal is feasible.

Assume that you load an record from the database with id = 1 and version = 1 into your application . You then modify it . When issuing UPDATE SQL to update the changes , you should update and include the checking for the version column too .

The UPDATE SQL should look like :

update ITEM set REMARK="SOME UPDATE REMARK!!" , VERSION= @origianl_vesrion +1 
where ITEM_ID=1 and VERSION=@origianl_vesrion

Note : @origianl_vesrion is the value of the version column at the moment when it is loaded into your application)

If another transaction updates and commits the same row before you commit , the update row count for the issued SQL will be zero as the VERSION column is increased by that transaction and will no longer equal to 1 .

So , if the update row count is zero , you can display an error message saying something like "Someone changes the same record too , please retry"