Relationship && structure of db

[Maarkr]

I'm designing a db that has 'initiative' as the main
table, with a 1-many for 'phases', with a 1-many
for 'goals', 1-many for 'objectives' and 'milestones'.
So, my initiative, say Health, has four phases (like
assessment, planning, etc) and each phase has many goals,
with many objectives and milestones for each goal.
Q: I have the same 5 phases for all initiatives, so is it
efficient to have a separate table/relationship for it?
There is also data that relates to each phase such as
start and end dates, so I didn't think a ddlb would work.
Q: When I put data in this structure, it doesn't work well
with the many layers to input in a form, ie,
I want to input an objective, but I want to show the
initiative, phase, and goal as well, which is many
subforms deep.

Any ideas?? thanks

 

[rpw]

My first thought on this is that each phase can be assigned to many initiatives, which is a many-to-many relationship that requires a junction table. Example:

tblInitiativePhases
InitPhsID
InititativeID
PhaseID
PhaseStartDate
PhaseEndDate

On the form for this table, a combo (ddlb) for phases would work just fine.

Rethink through the rest of your tables and identify if there are any more many-to-many relationships that you need to deal with.