Database Structure

NZ_Kiwis

I have a situation where I need to add dynamic data into my database.

I have a table of Users. UserID, UserName etc.

I have a table of Tasks. TaskID, TaskName. (This table will get tasks added (new row & active set to 1) and removed (active set to 0 so not deleted) over time.

Each month I want to insert a row for each user and I want to insert a Boolean into a database to see if each Task was completed.

Easy but when a Task is entered how to add another column to a database

My idea at the moment is to have this very small (columns)and very long (rows) set up

TaskCompleted
ID
TaskID
UserID
DateAdded (mm/yy)
TaskCompleted (true/False)

Then I would group all results by user & month and loop through each task?

I would end up with millions of rows quickly however.

Weedpacket

NZ_Kiwis wrote:
I would end up with millions of rows quickly however.

In a properly-indexed table using appropriate data types this would not be a problem.

dalecosp

This shouldn't be too difficult; as long as the Tasks table references the user table, you can easily get whatever information you need fairly simply.

[ATTACH]4963[/ATTACH]

"Started" and "Completed" would be some sort of timestamp.

How many tasks does a user have unfinished?

SELECT COUNT(ID) FROM TASKTABLE WHERE USERID=$user and completed = 0;
// or "null" if your table is so created...

How many tasks does has a user completed?

SELECT COUNT(ID) FROM TASKTABLE WHERE USERID=$user and completed > 0;

... etc.

db_structure.png

WenchDeal

"dalecosp"
Yeah dude! I agree with you,You can easily get whatever information you need fairly simply....