compound key or not?

[GeorgeAtkins]

Design question:
When is it necessary to create a compound primary key in a child table? For
example. I am working on a database that records information about moms,
birth events, babies and home visits. Here is a brief view of two of the main
tables. Note that primary keys are noted using the following symbol: (*)

tblMOM tblBIRTHS
MomPK (*) BirthPK (*)
LastN MomFK
FirstN ChildDOB
Ethnicity Gestation

MOM has a 1:M relation with BIRTH via MomFK. But MomFK is not part of the
BIRTHS primary key. This design seems to work fine for data entry, queries
and reports.

Now I'll expand the design to a third table:

tblMOM tblBIRTHS tblVISITS
MomPK (*) BirthPK (*) VisPK (*)
LastN MomFK BirthFK
FirstN ChildDOB VisitType
Ethnicity Gestation Billed

BIRTHS has a 1:M relationship with VISITS, via BirthFK. BirthFK is not part
of the Visits primary key. But queries, reports, and even query-based edits
work fine.

One reason I ask this is that, if I made MomFK part of the BIRTHS primary
key, I'd be obliged to carry it into the VISITS table, as well. SO, it seemed
easier to keep it simple. BUT AM I MISSING SOMETHING? I note that I can even
edit data from these 3 tables in a query.

But, things go bad when I link in my 4th table, tblBABIES:

tblMOM tblBIRTHS tblVISITS tbBABIES
MomPK (*) BirthPK (*) VisPK (*) BabyPK (*)
LastN MomFK BirthFK
BirthFK
FirstN ChildDOB VisitType Gender
Ethnicity Gestation Billed DOB

BIRTHS has a 1:M relationship with BABIES, via the BirthFK field. Now, if I
try to create a query using all 4 tables, I can see results, but cannot edit
the dynaset; even when all fields are displayed!

The main objective: I am trying to create a dataset so I can build a data
entry form that matches the paper form the use for home visits. The fields in
the form are distribution in different tables in the system, so I thought it
I could use a single query datasource, I could avoid complications using
subforms.

What am I overlooking here?


Thanks, George

 

[Jeff Boyce]

George

It may be that I'm not understanding your data. How are you differentiating
between BIRTHS and BABIES? Both appear to have DOBs, but the BABIES table
doesn't appear to have any foreign key back to either BIRTHS or MOTHERS.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[GeorgeAtkins]

OOPS! Sorry, I think my diagrams wrapped around and screwed things up. They
looked good putting it in. Here they are with more compressed margins. Hope
these make sense.

tblMOM tblBIRTHS tblVISITS tbBABIES
MomPK (*) BirthPK (*) VisPK (*) BabyPK (*)
LastN MomFK BirthFK BirthFK
FirstN ChildDOB VisitType Gender
Ethnicity Gestation Billed DOB

George

 

[Jeff Boyce]

George

Thanks for the clarification. I still don't understand how BIRTHS are 1:M
to BABIES.

Maybe instead of trying to build one large form, you could consider using
mainform/subform construction?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[GeorgeAtkins]

Okay, let me expand a bit. A birth defines a single birth event for a mom. A
birth can contain one or more children (twins, triplets, etc.). Each child
must be reported individually (gender, weight, etc.). This is the 1:M I'm
referring to.

Why am I trying to stuff all this into one query? In most cases I am using
master/sub-forms, as you noted. However, they have a specific (on-site visit)
paper form they want me to replicate, so data entry could be done more easily
from the paper form. HOWEVER, the order of fields on the visit form does not
match the order of fields in the separate tables, naturally (e.g. mom data
appears in different locations on both sides, intermixed with birth and
children information).

So, I thought if I could create a "flat file" source, I could more easily
build this bloody form. Or would a different solution be more realistic, such
as using recordsets and pushing all of the data into the tables after the
user clicks a command button to process the information?

 

[mnature]

tblMOM tblBIRTHS tblVISITS tbBABIES

MomPK (*) BirthPK (*) VisPK (*) BabyPK (*)
LastN MomFK BirthFK BirthFK
FirstN ChildDOB VisitType Gender
Ethnicity Gestation Billed DOB

There are a number of ways to set up tables for what you are trying to do.
Much of the design of tables depends on how you are using the data you
collect in your tables. In this case, I'm assuming that recording the visits
after the birth is the focus of your database. Here is one way of doing it:

Moms table just has information about the mother, such as name, ethnicity

tbl_Moms
MomID (PK)

Birth table just has information about the birth, which might be
complications to mother, medications, gestation. Joins a particular mother
with a particular baby. In the case of multiple births on one occasion,
there will be multiple entries in this table.

tbl_Births
BirthID (PK)
MomID (FK)
BabyID (FK)

Babies table just has information about a specific baby, which would be
birth weight, height, complications to baby

tbl_Babies
BabyID (PK)

A visits table will just have information about a single baby who visits the
doctor. Even if there are multiple births, it is possible for one baby out
of several to visit by themselves. If all babies come in, then multiple
visits are recorded.

tbl_Visits
VisitID (PK)
BabyID (FK)
VisitDate

 

[GeorgeAtkins]

Thanks for the information. I suppose I should have gone more into the scope
of the project, but I was really asking a question about updatable queries,
and I didn't want to get too detailed. However, here is the nature of the
project:
1. we have a program that conducts visits to moms with new births. We record
a bunch of information related to the mom, the birth, the baby/ies, and the
visits to the mom/baby.
2. Mom data includes name, address,dob,family income, insurance,etc.
3. Birth info includes date,location,ethnicity,insurance,referral letter, etc.
4. Babies table includes info about each baby of the mom's birth.
5. The visit table has date of visit, visit type, # seen in visit, whether
billed, etc.
Visits is linked to births rather than moms, since the visit is related to
the birth. I did not link it to babies, since a visit could include new-born
twins or triplets, for example.

I see your design includes a separate birth record for each baby, whereas I
have a single birth record linked to one or more babies. I think I did it
this way because I have to include other birth information as mentioned
above, and saw no reason to duplicate it. Is there another reason to have
multiple birth records (which would create 1:1 relationships in the babies
table)?

There are lots of reports about ethnic counts (moms and babies), # of visits
overall and by city, geographic distribution of families, unique counts of
moms for various statistics (some based on age ranges), etc.

This project actually started out as a simple excel file (as these often do)
and has grown into a more complex project. Obviously, I am only skimming the
surface here.

So, visits ARE important, but not the only or central focus of data
collection. And visits consist of our staff (we are a school district) seeing
new moms.

 

[Jeff Boyce]

George

(see responses in-line below)

Okay, let me expand a bit. A birth defines a single birth event for a mom.
A
birth can contain one or more children (twins, triplets, etc.). Each child
must be reported individually (gender, weight, etc.). This is the 1:M I'm
referring to.

Got it. (so, why do you have DOB in that table ... if MOM has triplets,
starting before midnight and ending after, what/who's DOB are you using?)

Why am I trying to stuff all this into one query? In most cases I am using
master/sub-forms, as you noted. However, they have a specific (on-site
visit)
paper form they want me to replicate, so data entry could be done more
easily
from the paper form. HOWEVER, the order of fields on the visit form does
not
match the order of fields in the separate tables, naturally (e.g. mom data
appears in different locations on both sides, intermixed with birth and
children information).

Trying to limit your tables (and even your forms) to match a paper process
might be an unnecessary (and even inappropriate) constraint.

So, I thought if I could create a "flat file" source, I could more easily
build this bloody form. Or would a different solution be more realistic,
such
as using recordsets and pushing all of the data into the tables after the
user clicks a command button to process the information?

As soon as you "flatten" the data, Access has no way to know which
data/table gets updated. You do always have the option of using an unbound
form, and marshalling the data in and out.

Regards

Jeff Boyce
Microsoft Office/Access MVP