0

Explanation: I currently have a database of thousands of tasks and a few hundred people that can see these tasks. Each user has their own login but all see the same list of tasks and all have to complete all of these tasks.

My Problem: I want each user to be able to mark each task complete as completed within their account without marking it complete for all other users.

What I currently have: 1 table of tasks and descriptions, 1 table of users, 1 table of discussion on each task. Users can login and see all of the tasks but cannot mark them complete.

I'm not sure if I am explaining this well enough but I am just looking for any insight as to the best way to get started with this as I kind of hit a brick wall and need people smarter than me to give their insights :)

Thank you all in advance for your comments.

Brandon Orndorff
  • 257
  • 6
  • 18

1 Answers1

0

You need one more table that holds the relation between the users and the task. Everytime a user completes a task you make an entry into that table:

 idx   task_id    user_id 
   1     1        3
   2     4        3
   3     1        5  ...

That way you can query for users who have completed a task, or the tasks completed by a user, the tasks that have not been completed by a user, etc...

PabTorre
  • 2,878
  • 21
  • 30
  • Thank you, this seems to be an option although I worry about the millions of records that could end up being created over several years of use as new people are added. Any thoughts on an array of some sort within a table value that holds all of the completed tasks? – Brandon Orndorff Dec 07 '16 at 18:49
  • This is basically a key-value pair, if volume is a concern there are a few ways to get around this such as using a key-value store such as HBase. Although I doubt that a table in the millions of rows could be too much of a problem for most RDBMS. Writting an array with task_id, user_id pairs and updating it would be a much more expensive transaction than appending to a relation table. You could add a date/timestamp column and then use this to expire the data. – PabTorre Dec 07 '16 at 20:09
  • 1
    This works perfectly both ways you have mentioned and I am currently using an array of completed values appending to each users profile to test and after a few days will decide if I need to change it up. Thank you. – Brandon Orndorff Dec 10 '16 at 15:20