Which Relationship Setup?

[Andy Roberts]

This one is driving me mad!

I'm going round and round in circles and I think it all hinges on my
relationships between my tables. I can't seem to make my mind up which one
is correct for which I need.

The relationships I'm thinking about are here (please have a look at these
as I explain my requirements)

http://www.blue-bean.co.uk/rel.htm

We have to survey a large river and that river is made up of REACHES (which
are particular parts of the river) and each reach contains a number of cross
section locations that need to be surveyed.

What I want to do is log the progress of my survey teams on a daily basis,
so the arrive back at the end of the day, go into the database and log their
PROGRESS by selecting a dat, which team they are etc and which reach they
are working on. All the sections are already stored in the database so they
then select a sections (which is controlled by the reach) and then log how
they have surveyed it and who the land owner is etc. The progress with
therefore be logged in a form with a subform. The main form contains the
daily progress data such as the date etc and the subform logs the data
relative to each section.

The link shows two relationships and I can't work out which is correct for
which I need. The three tables causing me an issue are the tblProgress,
tblReach and tblSections.

Any pointers?

Andy

 

[Douglas J. Steele]

What would you do if, for some reason, they had to resurvey the same
section? Is it possible that the values in tblSections would change?

If so, Option 1 makes sense. If not, Option 2 does.

 

[Andy Roberts]

Thanks Doug!

People are going to talk! I'll have to ban you from answering my queries
;-)

Thanks for all your help over the last day or two.

Andy

 

[Public]

I think 1 is correct EXCEPT that you have the relationship backwards.
SectionID should go in the progress table rather than ProgressID going in
the section table and ReachID should be removed from tblProgress. Progress
is your daily report and you want to report which sections you worked on
which days. tblProgress should be:
ProgressID
SurveyDate
TeamID
WeatherID
SectionID
Comments

Option 2 links Progress to Reach which will not give you the detail that you
want and it still has progressid in tblSections.

Your problem is solved by correcting the direction of the relationship.

 

[Andy Roberts]

Thanks for the advice

I'm sure you are right but... (bear with me)

The reason progress and reach are linked is because the reach dictates which
sections are "available" to progress as the reach seletion filters the
available sections. If I do what you say won't I loose the ability to
filter as above?

I appreciate your help

Andy

 

[Public]

No. You would use the relationship between section and reach if you wanted
to filter by reach. You could store reachID and SectionID in the progress
table if that makes more sense to you but it is unnecessary and technically
a violation of second normal form. Having a foreign key to Section from
Progress automatically gets you to Reach if you include the Reach table in
the join.