Table Relationships

[WillC]

I have the three tables related like shown in attachment. I want it to not let a period be created with a projectID and BudgetID that doesn't match any record in the Budget table. I thought the relationship would prevent this, any help appreciated, Thanks

 

[plog]

I don't think periods should be directly related to projects. Correct me if I am wrong:

Periods belong to Budgets.
Budgets belong to Projects.
You can't have a period that belongs to a project but not a budget.

If all those statements are correct, you need to eliminate the link between projects and periods. Periods would still be related to projects, just indirectly through budgets.

 

[WillC]

Thanks for the reply, I altered want you said. However, it still didn't prevent what I wanted, for example adding a period with project 1 and budget 1 was allowed even though only record in budget that had project 1 is budget 2...

 

[jdraw]

Can you describe in plain English how Budget relates to Project and how Period relates to Budget and how Period relates to Project?

 

[WillC]

A project can contain many budgets and many periods.
A budget contains a budget for a period, and there are several periods that can have that budget.
for example I have a project that has a budget of £20 a month, and lasts for three months. And a period contains the actual cost for a month. So that budget will relate to three periods... does that make sense?