One to One relationship?

[Carla]

Hi,
I am trying to create a database to track safety observations. for each
observation there will be a single counter measure. I think I need to create
a one to one relationship between by observation table and the counter
measure table but I'm not sure how to do that? Should the Observation ID
show up in the Counter Measure table or vice versa?

 

[John W. Vinson]

Hi,
I am trying to create a database to track safety observations. for each
observation there will be a single counter measure. I think I need to create
a one to one relationship between by observation table and the counter
measure table but I'm not sure how to do that? Should the Observation ID
show up in the Counter Measure table or vice versa?

Why not just have a field for the countermeasure in the Observations table?

Do check your business rules though: I'm perhaps visualizing this incorrectly,
but I would expect that an observation of (say) a serious fire hazard might
require many countermeasures ("switch to metal fuel containers", "add crashbar
closure to exit door", "install new fire extinguishers", "employee safety
training") - is this truly 1 to 1?

 

[KARL DEWEY]

I expect you need a one-to-many relationship. The reason I say this is that
for a single counter measure it will apply to many different safety
observations and a safety observation may have many counter measures.

If this is correct then the safety observation ID and counter measure ID
will be primary key fields. For a single safety observation you may have
many counter measures so you need a third table. The table will have field
for safety observation ID and have a field for counter measure.

When setting the relationship select Referential Integerity and Cascade
Update options.

In a form/subform (safety observations/ counter measure) use the counter
measure ID in a combo for selection.

 

[Dorian]

It sounds like you just need to put the observation and countermeasure in the
same row of a single table as two different columns.
Can the same countermeasure be used for different observations? If so, its a
many-to-one relationship.
You probably need 3 tables:
ObservationCounterMeasures (which links the below two tables)
ObservationID (foreign key)
CounterMeasureID (foreign key)

Observations:
ObservationID

CounterMeasures:
CounterMeasureID

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[John Spencer]

No, that is a one to many relationship.

One countermeasure would be related to many safety observations.

Unless the countermeasure is always going to be unique to each safety
observation. In which case you would include a field in safety observation to
record that unique value.

If you have only one attribute for a counter measure then the only reasons for
the counter measure table to exist is to have the ability to lookup the value
for consistent entry
OR
Having a autonumber primary key field plus a field with the text of the
countermeasure. Then you can save storage space by using a foreign key field
in the observations table and have the ability to universally change the text
of the countermeasure if you have a spelling error or an administrative
decision to change the text.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Tom Lake]

I expect you need a one-to-many relationship. The reason I say this is that
for a single counter measure it will apply to many different safety
observations and a safety observation may have many counter measures.

That would be a many-to-many relationship and you'd need a junction table.

Tom Lake