Relationships for database

  • Thread starter Thread starter jeff4379
  • Start date Start date
J

jeff4379

I am to set up a case management database for social workers to track their
case loads. I have taken Access courses, but am still unclear on how to set
up the relationships between multiple tables. These tables will be used to
create forms with subforms.

The program this database is for consists of mothers who have one or more
children and are involved in programming through our agency. In addition
to basic contact information for the parent and child(ren), I need to set up
tables to collect assessment information (collected every 6 months), case
notes (daily or weekly), and survey information (collected weekly). One of
the complexities I am facing is that the parents often have different
addresses, therapists, programs, and medications than their children.
Therefore, all this information needs to be kept seperate, but needs to be
able to be joined on a form or a report.

Below is my outline

tblFamily
FamilyID (primary key)
ParentID (foreign ID)
ChildID (foreign ID)

INTAKE
tblParent
ParentID (primary key)
ChildID (foreign key)
FirstName
LastName
etc.

tblChild
ChildID (primary key)
ParentID (foreign key)
FirstName
LastName
etc.

tblParentMedical
ParentID
MedicationName (could be multiples)
MedicationDose
MedicationDirections
Diagnoses (could be multiples)

tblChildMedical
ChildID
MedicationName (could be multiples)
MedicationDose
MedicationDirections
Diagnoses (could be multiples)

ASSESSMENT
tblParentAssessment
SatisfiedHousing
ResourcesHousing
SatisifiedPhysicalHealth
ResourcesPhysicalHealth
etc.

tblChildAssessment
MedicalDifficulties
TakingMeds
Allergies
Counseling

CASENOTES
tblParentCaseNotes
ParentID (primary key)
Date
Time
Location
ProgressGoals
Notes

tblChildCaseNotes
ChildID
Date
Time
Location
ProgressGoals
Notes

SURVEY
tblParent
Date
SessionNumber
ParticipationHelpful
ImprovedFunctioning
ImprovedSelfEsteem
ImprovedRelationships
etc.

Jeff
 
I am to set up a case management database for social workers to track
their case loads. I have taken Access courses, but am still unclear
on how to set up the relationships between multiple tables. These
tables will be used to create forms with subforms.
Click to expand...

This is an horrendous responsibility for someone with little or no
relational db experience -- it's a substantial analysis, design,
implementation, testing and training exercise for a commericial outfit! The
data protection implications hardly bear thinking about -- wrongly label a
child who does not have child protection concers and just watch the fur
fly. Again, there are real job-termination consequences from disclosing
sensitive information to the wrong parent. And so on.

I would seriously urge you to look at commercial products (of which there
are many) -- it will cost your organisation less in the long^W medium term.

Best wishes


Tim F
 
I appreciate your feedback on the complexities of this database design and
the possible implications of not following HIPAA laws. Because I will be
sharing these concerns with my supervisor, I would appreciate a second
opinion. Thanks.

--
 
I appreciate your feedback on the complexities of this database design and
the possible implications of not following HIPAA laws. Because I will be
sharing these concerns with my supervisor, I would appreciate a second
opinion. Thanks.
Click to expand...

I absolutely agree with Tim. Great care is needed here; a professional
experienced in these issues should be consulted.

If your boss is concerned about credentials, I worked for Parke-Davis
(then Warner-Lambert, now a division of Pfizer) for 17 years. I'm
fairly familiar with the issues of medical information handling.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top