Table Structure Question

[Brandon]

I have a project table with one record for each project.
The project table stores various pieces of info about the
project. I have a need to be able to link sub projects to
the main projects. So my thought is to have a sub table
where I enter sub projects and have a main project ID
field. My question is, what is the best way to arrange
the fields? The sub projects are going to be classified
in exactly the same way with all the fields from the main
project table, but I don't think it would be ideal to have
all the fields duplicated on both tables.
Finally, the main project table has a one to many
relationship with the savings table. Project savings are
recorded as one record for each month of savings. The
goal is to be able to track savings by project, but also
roll up the savings to a main project even if it has four
or five subprojects.
I also struggle with how the queries will work. Please
help or point me to examples of such a table structure.
Thanks!
Current State of the database:
The project table has two fields, one field is a yes/no
field that designates the project as a "parent project."
Then a second field allows you to choose one of the parent
projects (where the yes/no field is set to yes), thus
designating that project as a "child project." I feel
like this is not ideal.

 

[John Vinson]

I have a project table with one record for each project.
The project table stores various pieces of info about the
project. I have a need to be able to link sub projects to
the main projects. So my thought is to have a sub table
where I enter sub projects and have a main project ID
field. My question is, what is the best way to arrange
the fields? The sub projects are going to be classified
in exactly the same way with all the fields from the main
project table, but I don't think it would be ideal to have
all the fields duplicated on both tables.

It sounds like a classic Heirarchical table structure. One simple way
to do this is to have a ParentProjectID field in the Project table;
this will be NULL for a "main" project and will contain the ID of the
main project in the case of a subproject. You can create a query
joining the table to itself joining the ParentProjectID to the
ProjectID to see the related table data.

Finally, the main project table has a one to many
relationship with the savings table. Project savings are
recorded as one record for each month of savings. The
goal is to be able to track savings by project, but also
roll up the savings to a main project even if it has four
or five subprojects.

A three table join - main project to subproject to Savings - will do
this very nicely.

I also struggle with how the queries will work. Please
help or point me to examples of such a table structure.
Thanks!
Current State of the database:
The project table has two fields, one field is a yes/no
field that designates the project as a "parent project."
Then a second field allows you to choose one of the parent
projects (where the yes/no field is set to yes), thus
designating that project as a "child project." I feel
like this is not ideal.

The ParentProject field is all you need; if it's NULL it's a parent
project, if it's not, it's a subproject. You can omit the yes/no
field.

John W. Vinson[MVP]