database design

[ink777]

I am trying to develop an application for task management.Suppose I have a table with a few fields like 'Task ID', 'Description','Requested by','End Date' etc. I need to implement a feature wherein a user ('UserID') with permissions can login and assign or delegate this task to another user or to himself. How should I design my tables? Should the 'UserID' be in the same table as 'Task ID' which will most probably be the primary key? Obviously, I am new to database design.:-)

[dinesh123]

keep the users and Task in two different tables

TaskTable
=======
ID = Integer
Name = Text

User Table
=======
USerID = Text
TaskBMP = LongInteger

If u set Task1,4,5 to UserId =User1
then
User1 TaskBmp = (2^1) + (2^4 ) + ( 2^5) = 42

Getting the userTasks

if (Taskbmp and (2^TaskID) = true
User is assigned to this TaskID

[SavvasS]

I agree with leaving users and tasks as two separate tables, however I think that your suggestion of using a binary polynomial for the assigned tasks to users is a little limiting.

When task 50 is created, how will it be assigned to a user? 2^50 is rather too much to be stored in any data type.

I think the proper way would be to create a third table, name it TasksAssigned with two fields

USerID
TaskID

When a user is assigned a task, then a row is inserted to this table. If the requirement is that a task is only assigned to a single user, then a 1:1 constraint between the tasks and the TasksAssigned tables can be enforced.

Also, joining the tables can create a list of tasks by user or tasks not assigned

[antares686]

I would suggest like so.


tbl_Task
=======
TaskID (Primary Key)
Description
RequestedBy
EndDate
Assigned_UserID (Thsi would be indexed also)
...

tbl_Users
=======
UserID (Primary Key)
UserName
...


Now not knowing how the tables relate I assume the table tbl_Task will have a userid value which relates to userid in tbl_Users but the question is how will you determine they can assign the task.
If the RequestedBy field is the UserID which created and he has that right then Assigned_UserID will either be NULL or Set to the RequestBy value or be another userid where it is assigned.
If no relation to RequestedBy and it can be open to anyone who logs in and exists in the userid table then query the table for all NULL Assigned_UserID in tbl_Task and allow that person to select themselves or assign to the task.
If there is no relation in the above that would state who has access to a particular row as default then I need more info to go beyond.