Originally Posted by
Vini
Thanks for understanding my query, but unfortunately I don't understand your answer
It's a little above me
lol! I'll sort something (bodge)
Well I had been drinking at the time so maybe it was me.
1) Essentially you can generate a list of open tasks by looking at anything that hasn't been allocated. I'd prefer a distinct flag but as per Steve's reply you could simply use a null user id, e.g.
Code:
select * from tasks where user_id is null
2) When you come to allocate tasks to a user you'll need to pass the task IDs and user ID into an update query, e.g.
Code:
update tasks set user_id = 666 where task_id in (1, 2, 3) and user_id is null
You could also use a loop and allocate each task one at a time instead of using "in (1, 2, 3)". You need to check the user id again to avoid re-allocating tasks that have already been assigned to someone (as you're going to have multiple people using the system at the same time)
3) Then you need to confirm which tasks have been successfully allocated to the user, e.g.
Code:
select * from tasks where task_id in (1, 2, 3) and user_id = 666
Because, as above, if the same task list will be open for multiple users to select from at the same time you might find that user X selects task 2 (only) while user Y is still looking at the full task list. User Y then selects tasks 1, 2 and 3 but you should only allocate tasks 1 and 3 to user Y because task 2 has already been taken. It would probably be nice to tell the user that task 2 was already taken (possibly giving the user name of the person that took it, possibly not depending on who your users are and any privacy concerns).
Still with me?
I'd also use a field to indicate whether or not a task has been completed. You could simply delete tasks that have been completed though I'd say keeping a history is better. This is why I went on about multiple tables above. Instead of using 1 table with flags used to indicate the state of each task (allocated? completed?) you could use separate tables, e.g.
"new_tasks" - to store details of all tasks that haven't as yet been allocated to anyone
"open_tasks" - to store details of all task that have been allocated to someone but aren't as yet complete
"closed_tasks" - to store details of all tasks that have been allocated to someone and have been completed (e.g. a history of what each user did, very useful and possibly legally required depending on what the system in question is doing)
This is without going into any detail in terms of tables that might be required outside of this, e.g. a "person" (or "user") table might be used to store user details (user id, username, full name and password perhaps) which then lets you use only the user id as a foreign key in the tasks table, a "task_type" table that might define the types of task (and perhaps an associated table - "user_task_type" that would let you restrict certain tasks to certain users - e.g. if some of the tasks should only be given to 'advanced users' you could accomodate this by giving the task a type or level)...
Without knowing more it's hard to say what you need to do - is this homework or a pet project at work or are you being paid as a full time developer and being given woolly requirements?