One-to-many relationship using same SQL table

Question

EDIT: Because my brain failed, I actually meant a one to many, not a many-to-many as I first wrote this. This makes it a bit easier :)

I'm in the process of designing my database and one of the Tables (Tasks), needs to be able to have a one-to-many relationship with itself. This is because a task can have a number of sub-tasks that have the same data (much like a question and answer on SO).

I'm just a little confused, due to my not very strong SQL, as to how to make a one-to-many on the same table.

Currently I have these rows:

TaskId (uniqueidentifier)
aspnet_OwnerUserId (uniqueidentifier)
Title (nvarchar(50)) Description (nvarchar(MAX))
StartDate (smalldatetime)
DueDate (smalldatetime)

Answer 1

Well you could do this as a many-to-many but really its more like a nested set.

Answer 2

While I am not very sure what you are going to achieve, but depending on what you've given as the table fields, I think a one-to-many relationship with the table itself is more appropriate.

TaskId (integer *Primary Key)
Ref_Id (integer *Foreign Key references to TaskId above)
ASPNet_OwnerUserId (integer)
Title (varchar/text)
StartDate (Date/Timestamp)
DueDate (Date/Timestamp)

If you want a subtask to have multiple parent tasks, then please forget what I have said. That said, one or more answers can be made to a certain question, but not the other way around.

EDIT: I would suppose you are going to have another table "aspnet_OwnerUser" which holds some user info. Please take a look of the follow SQL if that's the case. Otherwise, forget it. ;)

CREATE TABLE `aspnet_OwnerUser`
(
    `id` SERIAL PRIMARY KEY
    , `name` VARCHAR(128)
    -- further detail follows
);

CREATE TABLE `task`
(
    `id` SERIAL PRIMARY KEY
    , `ref_id` INTEGER
        CONSTRAINT REFERENCES `task`(`id`)
    , `aspnet_OwnerUserId` INTEGER
        CONSTRAINT REFERENCES `aspnet_OwnerUser`(`id`)
    , `title` VARCHAR(128) NOT NULL
    , `startdate` TIMESTAMP
    , `duedate` TIMESTAMP
);

p.s. the above SQL is written for PostgreSQL, for other DBMS, please feel free to alter it.

Answer 3

The intersection (junction) table is coded pretty much as you would expect, only with two foreign keys pointing at the same table.

create table task_subtasks
 ( master_id number not null
   , sub_id number not null
   , constraint task_subtask_pk primary key (master_id, sub_id)
    , constraint task_subtask_master_fk foreign key (master_id)
         references tasks (taskid)
    , constraint task_subtask_sub_fk foreign key (sub_id)
         references tasks (taskid)
    )
/

edit

Having typed that up, I would like to interrogate your data model. I can see that a task can own many sub-tasks, but I am not sure how a sub-task can belong to many master tasks. Are you sure you don't really want a one-to-many relationship?

edit 2

While I was writing that edit I see you edited your question to answer that point.

create table tasks (
TaskId number not null
, aspnet_OwnerUserId number not null
, subTaskId number
, Title (nvarchar(50))
, Description (nvarchar(MAX))
, StartDate (smalldatetime)
, DueDate (smalldatetime)
, constraint task_pk primary key (taskid)
, constraint sub_task_fk foreign key (subtaskid)
    references tasks (taskid)
)
/

Answer 4

If your analogy is like a question and answer on SO then this is not a many-to-many relationship, it is a one-to-many relationship. One question may have several answers, but an answer belongs to one and only one question. The simplest way to map this is:

Table - Tasks

TaskID uniqueidentifier NOT NULL,
ParentTaskID uniqueidentifier NULL,
(other fields)

Then create a self-referencing foreign key constraint from ParentTaskID to TaskID.

Let's say that for some reason you really do need a M:M mapping. That has to be done using a mapping table; a self-refeferencing M:M isn't really any different from a M:M involving two tables:

Table - Tasks

TaskID uniqueidentifier NOT NULL,
(other fields)

Table - SubTasks

TaskID uniqueidentifier NOT NULL,
SubTaskID uniqueidentifier NOT NULL

Place a foreign key constraint on both TaskID and SubTaskID in the SubTasks table that references the Tasks (TaskID) column. The only difference between this and any other M:M relationship is that both foreign key constraints point to the same table (and on some DBMSes, you won't be able to cascade both of them).