0

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.

0

As a general rule I would recommend updating/creating relationships instead of copying data around to different tables. This is more in line with the relational model, and prevents tables-as-business logic which is what you seem to be doing.

Instead of copying data from one table to another I would think about updating a status flag. This could be a simple boolean value or a FK relationship to a status table when there are more than two possible states.

Depending on your business logic a better solution would probably be to associate a user with their selected tasks. This could be via a join table or with a nullable FK on the BookedTasks table.

To give a better or more complete answer I would need you to update your question to include your proposed table structures and some sample data/workflows.

  • That make sense. I would have only one table to manage this, with the Boolean status flag you suggest. I could then delete the records corresponding to the workers that were not booked -- they appear useless to me. Well, thank you. And I think to myself : I should have thought about it. – Steve Belanger Aug 19 '15 at 0:05
  • No worries we all have those moments 😀 – Erik Aug 19 '15 at 0:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.