Hi all (my first post!),

I have been requested to design a database (MSSQL 2000), that will "track" projects. There will be "Major" projects, which could have any amount of sub-projects, and these sub-projects could have any amount of sub-sub-projects.

All the projects/sub-projects/sub-sub-projects will have the exact same fields. It wont be overly complicated in regards to fields, i.e date started, status, deadlines, objectives. To me it looks like a single table could do the trick, as all projects/sub-projects/sub-sub-projects will have the same fields.

I'm just trying to get my head around how it all will work, how would you differentiate what was a sub-project or a sub-sub-project's parent?

Thanks for any help you may give

Cheers.

just have a parent project field and leave it 0 for top level projects

I agree with George. In addition, you might also want to create a field called MajorProjectID, which would contain the top-level project ID for all projects.

ProjectID, ProjectParentID, MajorProjectID, ProjectName
0, <NULL>, <NULL>, All Projects
1, 0, 1, ProjectA
2, 1, 1, ProjectA1
3, 1, 1, ProjectA2
4, 1, 1, ProjectA3
5, 4, 1, ProjectA3a
6, 4, 1, ProjectA3b
7, 0, 7, ProjectB
8, 7, 7, ProjectB1
9, 8, 7, ProjectB1a
... etc

The MajorProjectID field would allow you to easily query all subprojects within a particular Major Project.

Thanks guys! I will run with those ideas.

Cheers.

Hi,

You might find this Data Model interesting if you come to think about planning Resources on Projects :-
http://www.databaseanswers.org/data_...ning/index.htm

and Staff on Projects :-
http://www.databaseanswers.org/data_...ents/index.htm

B.Dimple
Senior DBA