I am working on an app that does task dispatching. A user inputs a task with a start date and end date. The app gives the user a list of workers available and chooses to offer the task to some of them. When a worker logs in the app and accepts the task offered, he is booked to do the task.
I plan on using the following strategy : There would be a taskPossibilities table and a bookedTasks table a SELECT in the bookedTasks would retain the workers who are not already booked for the chosen period. All the workers chosen by the user would be put in the taskPossibilies table. When a worker accepts a task, the record of this worker is copied to bookedTasks, and then all corresponding records in the taskPossibilities are deleted.
I am not asking how to do it : I have done it already, but tested on a local machine only. I am wondering if this way of doing things represents a risk on data integrity, and wheter there is a better practice for such a situation.