Data Model Help

[Tim Tafflinger]

I'm building a medical case management database in which the patient will be
asked a series of questions to assess their current situation, with
additional questions asked depending on their answers.

The two main tables are:

MEMBERS
PK - MemberID

CASES (each member can have multiple cases)
PK - CaseID
FK - MemberID

What I'm struggling with is that related to the case are about 35 distinct
assessment areas with multiple questions in each area and some of those
questions can have multiple answers which requires another related table.
Plus there are another 5 tables with other data related to the case. It
seemed to me that relating all 40 tables to the CASES table was the way to go
but when trying to set up the relatioinships with referential integrity I
received an error. Apparently that exceeds the number of relationships one
table can have in Access.

So, the first question would be is my data model ok and I'm just dealing
with an Access limitation, or is it a poor data model? If it's a poor data
model then I'm at a loss to know how to redesign it. Any suggestions greatly
appreciated!

 

[Duane Hookom]

"35 distinct assessment areas with multiple questions in each area and some
of those questions can have multiple answers which requires another related
table" does this suggest that you have different fields for your questions?
If so, consider normalizing your structure. Review the tables in "At Your
Survey" found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Each response to each question for each member should create a record in a
table.

 

[Tim Tafflinger]

I tried your link but the site must be down. Will try again later. Here is a
little more explanation.

An example of an assessment area would be asking the patient if they have
diabetes. I have one table for that as shown below. The question isn't part
of the field, I included it for clarity. It's the same for the other areas
such as Stroke, Cancer, etc.
---------------------
DIABETES
CaseID
HaveDiabetes (Question: Do you have diabetes?)
HowLong (Question: How long have you had diabetes?)
BloodSugarCheck (Question: How often do you check your blood sugar?)
HgbAiCLevel (Question: Do you know your hgbAiCLevel?)
OpenWound (Question: Do you have an open wound or cellulitis?)

 

[Tim Tafflinger]

I was able to get to your site and download the survey. I had started down
that path before but couldn't quite grasp how it would come together. This
will be a big help! Thanks!