Re: which database design should I use ?

Try a self relationship in PROJECT

CREATE TABLE PROJECT (
ProjectNumber INT PRIMARY KEY,
SubProjectNumber INT FOREIGN KEY (SubProjectNumber) REFERENCES PROJECT (ProjectNumber))

CREATE TABLE ORDERS (
OrderNumber INT PRIMARY KEY,
ProjectNumber INT FOREIGN KEY (ProjectNumber) REFERENCES PROJECT (ProjectNumber))

Now you can have several levels of subprojects, and can reference the orders by any project number. Actually, it doesn't matter if it's a main or subproject:
all of them will exist as "main projects" until you reference them in the subproject field, wich will cause them to turn into subprojects.

Good Luck!

Mateus Espadoto

chris.gheysen_at_village.uunet.be (chris gheysen) wrote in message news:<c00eab86.0112170632.6b1b00a3_at_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 Wed Dec 26 2001 - 11:01:14 CET