If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Table Structure Question
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. |
#2
|
|||
|
|||
On Tue, 1 Feb 2005 07:37:29 -0800, "Brandon"
wrote: 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] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Mixed up with Relationships..help! | KrazyRed | New Users | 3 | January 26th, 2005 05:03 AM |
Table headers | Sam Hobbs | New Users | 11 | December 20th, 2004 07:18 PM |
Semicolon delimited text query help | Al Guerra | Running & Setting Up Queries | 3 | August 12th, 2004 11:50 AM |
Newbie? Do I use Report or Query | John Egan | New Users | 11 | June 28th, 2004 08:31 PM |
Cannot join 1:M table into M:M tables | Tom | Database Design | 4 | May 19th, 2004 10:16 PM |