which database design should I use ?

I am having the following problem :

After having created a general ORDER-table in my database, I should have the opportunity to relate each order to a certain project. The obvious solution would be to create a PROJECT-table with a key-field Projectnumber and to add a field to my ORDER-table that links to the PROJECT-table :

ORDER
Ordernumber (Primary key)
Projectnumber (Foreign key to projectnumber in PROJECT)
...

PROJECT
Projectnumber (Primary Key)
...

However, there is a possibility that a certain project resides in another project. There can be several levels of sub-projects :

e.g. : - Myproject

• MySubproject
  • MySubSubproject
    • MySubSubSubproject
      • ...

There should be a possibility to link each order to one or more of those projects or subprojects.

Can anyone help me how the database design should look like ?

Thanks in advance,

Chris Gheysen Received on Mon Dec 17 2001 - 15:32:28 CET