Multi-user web app - Database design

Question

I am going to be developing a multi-user web app where users will record every day whether or not they have completed various tasks. The tasks are repeated every day, eg: Every day XYZ needs to be done.

Not all users will have the same tasks to complete each day. There will likely be a database table containing all possible tasks. When a new user signs up on the web, they will select the tasks that apply to them by creating a profile for themselves.

Each day the user will then record whether or not they completed their respective tasks. Following that, there will be in depth reporting and historical stats not just on a users own task history,...but also globally to look for trends.

Im just looking for any suggestions on how to design the database (in general terms). Would it be ok to have a task table that contains all the tasks. Then when a new user creates their own profile online, a brand new table is created with their profile information and the tasks that they have selected. Each unique user profile table will then contain an ongoing history of tasks completed each day.

Or is there a better way to design this?

Edit: Or would a better idea to be to have something like the below:

Task history table:

PersonID | Date     | Task1        | Task2    | Task3    | Task 4
001      | 24Jan15  | Complete     | Complete |          |
002      | 24Jan15  |              | Complete | Complete | Not Complete
003      | 24Jan15  | Not Complete |          |          |

So there would be one table containing all the users (and the tasks they've chosen), another table containing all possible tasks, and lastly the above table recording the task history each day.

The only issue here is that not every task is applicable to every person. So there will be blanks. Not sure if that matters.

As you can no doubt tell, im a beginner. So any advice would be appreciated.

Answer 1

It is almost never a good idea to create new tables dynamically to hold subsets of the data. Data for different users should go in the same set of tables, with some field identifying the user. There is no good reason to have hundreds of tables that are all identical except that one is for some key value A, the next is for key value B, etc. Just add the key field to the table.

As a_horse_with_no_name says, numbered columns is a strong sign that you are doing it wrong. There are many reasons why this is a bad idea. Among them: If you have one column for each task, what happens when a new task is added? Instead of just adding a new record, now you have to add a new column to the table, and update all the existing records. Also, it makes queries very complicated. A query like "what tasks were done today" requires a separate test for every column, instead of one test on a single "task" column.

From what you've said, here's my first thought on how this should look:

Task table
(task_id, task_name)

This lists all the tasks of interest.

User table
(user_id, user_name)

This lists all the users.

Assigned_Task table
(user_id, task_id)

This relates users to tasks. There will be one record in this table for each task for each user. That is, if Alice is user 1 and she is supposed to do tasks 1, 2, and 3; and Bob is user 2 and he is supposed to do 2 and 4, then there will be records (1,1), (1,2), (1,3), (2, 2), and (2,4).

(Note: You might have an assigned_task_id field for this table to be the primary key, or the PK could be user_id + task_id, as that must be unique.)

Task_Status table
(user_id, task_id, task_date, completed)

This will have one record for each user/task combination, for each day. So after 30 days, if Alice has 3 tasks, there will be 3 x 30 = 90 records for her, 3 for each day times 30 days.

(You might have a task_status_id as the PK, or you might use user_id + task_id + task_date. Keys with more than 2 fields tend to be a pain so I'd probably create a task_status_id. Whatever.)

Any of these tables might have additional fields if there's other information you need. Like the User table might have employee number, phone number, department, etc.

Then a question like, "What tasks were not completed yesterday?" is easily answered with this query:

select user.name, task.name
from task_status
join user on user.user_id=task_status.user_id
join task on task.task_id=task_status.task_id
where task_date=@date
  and completed=0

How many tasks were completed today?

select count(*)
from task_status
where date=@date and completed=1

Etc.