Keith or any DB guru,

This is the first in series of questions:

Question # 1 

I have two tables with One-to-Many Relationships: Project (One) & Task (Many)

Rule:  One Project has one or more tasks.

Any suggestions on the structure?

Hi Marl,

I think you already answered your question since you already know your expected schema.

Here is a sample schema that you can use as a base:

Projects
-------------
ProjectID
Name
Description
DateCreated
DateDeleted

Tasks
-------------
TaskID
ProjectID
Description
StatusID
DateCreated
DateDeleted

Status
-------------
StatusID
Description

The sample schema is not deemed to be complete but it should give you a good overview on how to build your succeeding tables. Also, since this is Issue/Project tracker type of thingy I would like to suggest you take a look at at some existing samples

http://www.asp.net/downloads/archived/starter-kits/issue-tracker/ 

http://asp.net/downloads/starter-kits/time-tracker/

This will give a good idea on how to build your tables.

HTH

- Keith

create table project
(
	id integer primary key,
	name varchar(100)
	-- the rest of your project columns
);

create table task
(
	id integer primary key,
	project_id integer references project(id),
	name varchar(100)
	-- the rest of your task columns
);

keithrull:

...since this is Issue/Project tracker type of thingy...

No. Although it also serves the functions of a tracker in another aspect, it is a project estimator. There will be pre-defined tasks/processes a particular project will go through. Say, Project1 goes through task1, task2, task3, and task4. But projects will not always be like that. Like, Project2 only goes through task2 and task3. Project3 goes task1, task3 and task4...etc. But I don't think it's a good idea to have separate columns for Task1, Task2, Task3, etc. Any idea?

marl:

keithrull:

...since this is Issue/Project tracker type of thingy...

No. Although it also serves the functions of a tracker in another aspect, it is a project estimator. There will be pre-defined tasks/processes a particular project will go through. Say, Project1 goes through task1, task2, task3, and task4. But projects will not always be like that. Like, Project2 only goes through task2 and task3. Project3 goes task1, task3 and task4...etc. But I don't think it's a good idea to have separate columns for Task1, Task2, Task3, etc. Any idea?

 

jop:

Stick to the project/task schema that both keith and I suggested. Handle the task per project/task restrictions somewhere else - maybe that logic is code in the application itself or there are supporting tables that define that rules to apply.

Hell, yeah. I didn't realize this is quite similar to the Instructor-Student-Class scenario.

Maybe I could find what I am looking for in one of these: http://msdn.microsoft.com/en-us/express/bb403186.aspx#2  

I'll look into these and expect more question from me...

So, like what Jop said, the best way I can do this is to handle the "Task-Project" relation, maybe in the form (checkbox, List, etc). Then on the DB side, this is how to be captured (same as Keith's Model but with the addtional of "bridge" table):

Any comments?

marl:

No. Although it also serves the functions of a tracker in another aspect, it is a project estimator. There will be pre-defined tasks/processes a particular project will go through. Say, Project1 goes through task1, task2, task3, and task4. But projects will not always be like that. Like, Project2 only goes through task2 and task3. Project3 goes task1, task3 and task4...etc. But I don't think it's a good idea to have separate columns for Task1, Task2, Task3, etc. Any idea?

keith's and jop's suggestions serve as the foundation for this. It's the same basic pricinple you can use for a parameter-based workflow management where each project or task goes through a pre-defined process (milestones). You can also use this principle for KPI (Key Performance Indicator) project as well, where you measure the time elapse between tasks (from start to finish).

You will require to have a Master table for the Project definition, a Detail table for the task definition of each Project. Then a transaction table for the project tasks.