Many to Many Design Tangle

[Guest]

I appreciate the value of this helping community!

I have a question that follows an earlier response that helped me in dealing
with a many to many relationship but has me currently tangled up in my
re-design.

I have a medical observation database for many subjects, with specific
symptoms that are repeatedly measured, sometimes by different observers. A
previously created FrontPage form exists wherein specific medical history is
obtained (will be phased out and replaced with an Access form) and the 60
plus symptoms are first presented showing a default value of zero. The
observer identifies the symptoms present and selects a value from 0-100,
representing the percentage of time the symptom is present. I have imported
that data into my current database and each of the values is a field.

As a result, when I first created my Access 2007 database I imported this
data into a table (tblHistory). I then queried the tblHistory selecting the
symptoms (really the symptom values) to create an observation table
(tblObservation) that then received subsequent repeated symptom observations.

In attempting to correct the design of my database I decided I needed a
Symptom field and created TblSymptoms—here is where I stumble- My imported
fields are numeric (although they each represent a specific symptom). Would
the following table design make sense?

TblSymptoms
SymptomID
Symptoms Text
?SymptomValue? Numeric

When I treat each symptom value as a field, as I have, I have been able to
create a form that displays each of the symptoms labeled with a combo box
offering the default of zero or any value to 100. But, with this design I ’m
unable to query Symptoms as a field. If I use the TblSymptom design is there
a proper way to link the value selected to the textually defined symptom ?

My form, with each symptom an individual field, of course, doesn’t return
the textual symptom description in a single symptom field. Should intensity
of the symptom be its own field? If so would I place the intensity field as a
field in the symptom table so they are related or would the intensity field
be in the TblSubjectSymptoms as shown below.

Should the symptoms be individual fields and if not should intensity be a
field in a Symptoms table or in the SubjectSymptoms table? Thanks for any
thoughts.

TblSubjects
SubID
<biographical data>
TblObservers
ObserverID
<biographical data>
TblSymptoms
SymptomID
Symptoms <e.g. "Tics", "Allergy">

TblSubjectSymptoms
SubID <link to Subject>
ObserverID<link to Observer
ObsDateTime <when this subject was observed with this condition>
SymptomID <link to Symptoms ----??
Intensity <1-100>??

 

[Jeff Boyce]

Bill

See comments in-line below...

I appreciate the value of this helping community!

I have a question that follows an earlier response that helped me in
dealing
with a many to many relationship but has me currently tangled up in my
re-design.

I have a medical observation database for many subjects, with specific
symptoms that are repeatedly measured, sometimes by different observers.
A
previously created FrontPage form exists wherein specific medical history
is
obtained (will be phased out and replaced with an Access form) and the 60
plus symptoms are first presented showing a default value of zero. The
observer identifies the symptoms present and selects a value from 0-100,
representing the percentage of time the symptom is present. I have
imported
that data into my current database and each of the values is a field.

As a result, when I first created my Access 2007 database I imported this
data into a table (tblHistory). I then queried the tblHistory selecting
the
symptoms (really the symptom values) to create an observation table
(tblObservation) that then received subsequent repeated symptom
observations.

In attempting to correct the design of my database I decided I needed a
Symptom field and created TblSymptoms-here is where I stumble- My imported
fields are numeric (although they each represent a specific symptom).
Would
the following table design make sense?

No, if I'm understanding your description, the "Symptom Value" pertains to
the observation of a patient, not to the symptom itself.

TblSymptoms
SymptomID
Symptoms Text
?SymptomValue? Numeric

When I treat each symptom value as a field, as I have, I have been able
to
create a form that displays each of the symptoms labeled with a combo box
offering the default of zero or any value to 100. But, with this design I
'm
unable to query Symptoms as a field. If I use the TblSymptom design is
there
a proper way to link the value selected to the textually defined symptom
?

My form, with each symptom an individual field, of course, doesn't return
the textual symptom description in a single symptom field. Should
intensity
of the symptom be its own field? If so would I place the intensity field
as a
field in the symptom table so they are related or would the intensity
field
be in the TblSubjectSymptoms as shown below.

Should the symptoms be individual fields and if not should intensity be a
field in a Symptoms table or in the SubjectSymptoms table? Thanks for any
thoughts.

TblSubjects
SubID
<biographical data>
TblObservers
ObserverID
<biographical data>

Are you quite confident that none of your "observers" will ever be
"subjects" (i.e., doctors are never patients?!). Consider having a
tblPerson with PersonID and biographical data. That way, you can use the
PersonID as the SubjectID in the "Observation" table ... and a PersonID as
the ObserverID in the same table.

TblSymptoms
SymptomID
Symptoms <e.g. "Tics", "Allergy">

TblSubjectSymptoms
SubID <link to Subject>
ObserverID<link to Observer
ObsDateTime <when this subject was observed with this condition>
SymptomID <link to Symptoms ----??
Intensity <1-100>??

I like this part of the design. It appears to allow you to have one Subject
who gets observed more than once, and by more than one observer, and for
more than one symptom, with each combination receiving an "Intensity"
rating.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Bill

See comments in-line below...



No, if I'm understanding your description, the "Symptom Value" pertains to
the observation of a patient, not to the symptom itself.


Are you quite confident that none of your "observers" will ever be
"subjects" (i.e., doctors are never patients?!). Consider having a
tblPerson with PersonID and biographical data. That way, you can use the
PersonID as the SubjectID in the "Observation" table ... and a PersonID as
the ObserverID in the same table.


I like this part of the design. It appears to allow you to have one Subject
who gets observed more than once, and by more than one observer, and for
more than one symptom, with each combination receiving an "Intensity"
rating.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Changing the subject id to person id is such a great suggestion, thank you,
Jeff.

It especially works well because, yes, doctors can be patients and it also
accommodates self observers—and, I think I can also apply this to a table I
have called TblGuardian and bring this into TblPerson as well— I’ll just ask
if you can confirm that I am on the right track and again very much
appreciate your time and response.

One other point regarding this observation database is that, at each
observation point (Date/Time) all of the symptoms are measured—the default of
zero indicates the absence of a particular symptom at that observation point
and is as important as the presence of symptoms.

So, when an observer makes an observation all symptom items are presented as
zero and they update the items that are present according to the 0-100 value.
Since changing individual symptoms as fields to one field “Symptoms†each
specific symptom becomes a record whereas in my previous ill fated design
each observation date was a record.

I realize this untangles my database but I will need to be a little more
creative in designing a user friendly form. I will do some homework on that
task!

In my database, I also had guardians, pediatric patient’s parents (also
among observers) in TblGuardians—So I will make TblGuardians, TblSubjects and
TblObservers into:

TblPersons
PersonID
<biographical data>

My other tables will be:

TblSymptoms

SymptomID
Symptoms <e.g. "Tics", "Allergy">

TblSubjectSymptoms
SubID <link to PersonIDt>
ObserverID<link to PersonID
ObsDateTime <when this subject was observed >
SymptomID <link to Symptoms ----
SymptomValue<Intensity <0-100>??

If you see anything goofy just let me know otherwise, Thanks again!

 

[Jeff Boyce]

Bill

Nothing else jumps out at me.

Yes, since each observation is the combination of a Patient(person), an
Observer (person), a Date/Time, a symptom, and the 'rating' on that symptom,
each needs to be its own record.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[James A. Fortune]

I appreciate the value of this helping community!

I have a question that follows an earlier response that helped me in dealing
with a many to many relationship but has me currently tangled up in my
re-design.

I have a medical observation database for many subjects, with specific
symptoms that are repeatedly measured, sometimes by different observers. A
previously created FrontPage form exists wherein specific medical history is
obtained (will be phased out and replaced with an Access form) and the 60
plus symptoms are first presented showing a default value of zero. The
observer identifies the symptoms present and selects a value from 0-100,
representing the percentage of time the symptom is present. I have imported
that data into my current database and each of the values is a field.

As a result, when I first created my Access 2007 database I imported this
data into a table (tblHistory). I then queried the tblHistory selecting the
symptoms (really the symptom values) to create an observation table
(tblObservation) that then received subsequent repeated symptom observations.

In attempting to correct the design of my database I decided I needed a
Symptom field and created TblSymptoms—here is where I stumble- My imported
fields are numeric (although they each represent a specific symptom). Would
the following table design make sense?

TblSymptoms
SymptomID
Symptoms Text
?SymptomValue? Numeric

When I treat each symptom value as a field, as I have, I have been able to
create a form that displays each of the symptoms labeled with a combo box
offering the default of zero or any value to 100. But, with this design I ’m
unable to query Symptoms as a field. If I use the TblSymptom design is there
a proper way to link the value selected to the textually defined symptom ?

My form, with each symptom an individual field, of course, doesn’t return
the textual symptom description in a single symptom field. Should intensity
of the symptom be its own field? If so would I place the intensity field as a
field in the symptom table so they are related or would the intensity field
be in the TblSubjectSymptoms as shown below.

Should the symptoms be individual fields and if not should intensity be a
field in a Symptoms table or in the SubjectSymptoms table? Thanks for any
thoughts.

TblSubjects
SubID
<biographical data>
TblObservers
ObserverID
<biographical data>
TblSymptoms
SymptomID
Symptoms <e.g. "Tics", "Allergy">

TblSubjectSymptoms
SubID <link to Subject>
ObserverID<link to Observer
ObsDateTime <when this subject was observed with this condition>
SymptomID <link to Symptoms ----??
Intensity <1-100>??

This doesn't answer your question but you might find the following post
of interest:

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/13f0cd414aad3a0f

Note that it's possible to have more than one many-to-many relationship
with corresponding junction tables.

James A. Fortune
(e-mail address removed)