Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Dbar
Starting Member
12 Posts |
 Posted - 2008-12-05 : 20:04:15
|
| I need to design a database with the following requirements:For each Contract, there can be 1->many ProjectsFor each Project, there can be 0->many sub-ProjectsFor each sub-Project, there can be 0->many subsub-ProjectsFor each subsub-Project, there can be 0->many subsubsub-ProjectsFor each subsubsub-Project, there can be 0->many subsubsubsub-Projects....(there can be up to 7 levels of Projects...For each subsubsubsubsubsubsubsub-Project, there can be 0-many TasksI hate to create 7 (identical) Projects tables, especially since there will seldom be more than 3 levels of nesting. However, there will often be data at the lowest level (Task).Any ideas? I hope this makes sense |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-05 : 23:51:54
|
| i think you need a hierarchial table with ProjectID and ParentProjectID as two fields. each lower level will have parentprojectid value as immediate higher level. this way you can store the project ladder data upto any level. and for retrieveing data you can use recursive queries.the top project record will have NULL or 0 as value to show its top level. |
 |
|
|
Dbar
Starting Member
12 Posts |
Posted - 2008-12-06 : 07:45:59
|
| Thanks --- that's the table structure I currently have (i.e., one table with ProjectID and ParentProjectID). I need to become more familiar with recursive queries. Are they cumbersome? |
|
|
|
|
|
|
Database design with "nested" tables