Database design with "nested" tables

[BARJRD]

I need to design a database with the following requirements:

For each Contract, there can be 1->many Projects
For each Project, there can be 0->many sub-Projects
For each sub-Project, there can be 0->many subsub-Projects
For each subsub-Project, there can be 0->many subsubsub-Projects
For 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 Tasks

I 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

 

[Banana]

You will want to look up on self-joins; you should just need two tables:

tblProjects <- Description of all products
tblLinkedProjects <- Many side of project with the structure:

ParentProjectID
ChildProjectID

Both fields are derived from ProductID in tblProduct. This way you have infinite levels of nesting and correct normalization. Unfortunately, SQL doesn't really support this well, but this can be worked around in various ways.

HTH.

 

[gemma-the-husky]

if a subproject at any level is really the same thing - ie refers to othertables in the same way, then you really only have one entity, that of project (if a task is, if you like, a a special sort of project (with no children) , then you possibly dont even need a task table - just include the tasks in the project table

your problem is how to relate those projects together

as banana says, you just need another table saying that project 12, is a parent project for projects 20, 24, and 25, and that project 20 is a parent of projects 31, 33, 36 etc
for this to work

although showing multiple levels of hierarchy (sort of like a tree view) is not something immediately exposed to vba - so it needs some work to get there

if the level of relatedness is 1 - ie a project can only be owned by one other project (which is probably the case), then you could even get by with just one table, where a project/task either stores the projectid of the parent, or stores 0, if its a top level project