which database design should I use ?

From: chris gheysen <chris.gheysen_at_village.uunet.be>
Date: 17 Dec 2001 06:32:28 -0800
Message-ID: <c00eab86.0112170632.6b1b00a3@posting.google.com>

Hi,

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 - 08:32:28 CST