Table dilema

[cgarmas]

Hello all,
In our community health center we are building a database that collects prenatal care data and delivery data. To collect the prenatal care data we have two tables one for women who received prenatal care in our clinic (TblPrenatal) and another one for women who came to us after delivery and had not been our clients before (TblPostPartum). The reason for having two tables for prenatal care information is because the information is somewhat different between the two groups of women. These tables have a PK which is an autonumber, there is no other fields besides PK that can uniquely identify records because of confidentiality reasons.
We also have a delivery information table for both groups of women, in this table I have a field (PrenPPID) that is suppose to store the PK from either TblPrenatal or TblPostPartum according to the corresponding delivery. What I am trying to do is to store in one field the PK field from TblPrenatal and TblPostPartum, the problem is that I can't set up a parent/child relationship to either table because if I retrieve the PK from TblPrenatal, Access tells me that the record can't be created because there is not a parent record in TblPostPartum and vice versa. We will appreciate any help on resolving this problem, and sorry for the lengthy explanation.

 

[lespaul]

Have you read the fundamentals document linked below?

You should have a single table - Patients - with an indicator Prenatal (Y/N). There are some things that you must know about all patients, this is the information you should keep in this table. There is special information needed based on if the patient was a prenatal or postnatal, these should be two more tables (linked to the Patients table). The flag in Patients will indicate which of the other tables you need to join into to get the rest of the needed information.

HTH




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[cgarmas]

Thanks for the reply, unfortunatelly this is a little bit more complicated because each patient whether they come to the clinic in their prenatal or posnatal care, they may enroll in the program multiple times for a new pregnancy or posnatal care, in which case we have a field EnrollmentNumber in the prenatal/ and postnatal tables. So althoughy I have a table clients, I still need to retrieve the unique identifier of the corresponding enrollment period using the PK from the detail prenatal/postpartum tables to link the delivery information to the specific enrollment period.

 

[lespaul]

in which case we have a field EnrollmentNumber in the prenatal/ and postnatal tables

why not have the PatientID in the enrollment table?

So you have an enrollment table:

Enrollment
PatientID (FK from Patients)
ProgramID (FK from Programs)
EnrollmentDate (allows for multiple enrollments in the same program)

Again, have you read the fundamentals document linked below? It will explain the basics of how to set up your tables and the different relationship types (one to one, one to many, and many to many).

You apparently have a many to many relationship between patients and programs (one patient can be enrolled in many programs and one program has many patients enrolled) so you will need a table to store that many to many relationship.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[cgarmas]

Leslie,
I think the suggestion of the Enrollment table is great, and the suggested reading was helpful, but I jus have one more question; would it be appropriate instead of having two tables, the clients table and the enrollment table to have just one table like this:
UniqueID PK (autonumber)
ClientID
EnrollmentNumber
Prenatal/Postpartum program
and then use the UniqueID as a link to other tables?

 

[lespaul]

That seems like a reasonable suggestion (it's hard to be "sure" since I don't know everything!)

Clients
ClientID
Name
Address
etc.

Programs
ProgramID
ProgramName
(depending on your program data you may want to put the day of the week the program is run, how many classes in the program, you may even put whether it's a prenatal or postnatal course, if you have some that are both then add two records one for the prenatal course and one for the postnatal course, etc.)

ProgramSchedule
ScheduleID
ProgramID
StartDate

ClientEnrollment
ClientID
ScheduleID

So, there's a few more suggestions, feel free to post back with any other questions!

leslie

 

[cgarmas]

Thanks again Leslie, and I do have more questions.
These are some of the tables I am creating and I am trying to figure out the best way to link them.

TblClients
ClientID

TblEnrollment
EnrollmentID
ClientID
EnrollmentNumber (first,second..)
EnrollmentDate
Prenatal/Postpartumperiod

TblInitialIntake
IntakeID
EnrollmentID
Other fields

TblRiskAssessment
RiskAssessmtID
EnrollmentID or
IntakeID
other fields

TblHealthEducation
EducationID
EnrollmentID or
IntakeID
other fields

TblNeeds
NeedsID
EnrollmentID or
IntakeID
other fields

Tables RiskAssessment, HealthEducation and Needs are part of the information collected at Initial intake, but because these pieces of information are collected again at later times I made them into separate tables. Now the dilema I have with these tables is if I should link them to the enrollment table which provides the ClientID and Enrollment Info or I should link them to the Initial Intake through IntakeID.
Because I need to retrieve these last 3 tables at later time to update and add new records to them I think it would be easier to search for them using the link to the enrollment table. What do you think? I hope this is not confusing. Thanks