family dependencies in database structure

[Guest]

i'm doing some pro bono work at a hospital and i've been asked to try to put
together an a2k (or maybe it's going to be a 2003) database. the
investigator's interested in studying childhood obesity. as you would imagine
there's a table that's geared around the patient with one record per patient.
there's a table that's geared around the patient's parent's info w/ one
record per parent. a table geared to record (the same background) data about
the patient's close relatives (siblings, aunt/uncle, grandparents) w/ one
record per close relative. there are some other tables which capture the lab
tests done on the patient at recurring clinic visits.

i haven't asked the folks doing this study this question as yet, but it's
occured to me that the patient him/herself might have a sibling that ends up
being a patient his/herself!!!

things seem a little murky to me at this point....

before thinking of this 'question', i sort of envisaged using the patient's
medical record number as the linking (PK) control spanning the tables cited.
in a certain sense, i viewed the patient's demographic table as the 'mother'
of all tables (since the ones capturing the data for the other persons of
potential interest to the project all 'hooked back' to patient 'him'self).
but what would one do if a sibling were to show up as a patient. would the
same design then be just as applicable?

any deep database thinkers out there feel like chiming in?

-ted

 

[John Nurick]

Considering the number of teenage pregnancies, it also seems possible
that a patient could become a parent - especially if the study is a
longitudinal one.

So I feel you need a Person entity that includes patients and others. If
the "background" information you store about patients is very different
from what you store for others, subclass a Patient entity from Person;
otherwise you may only need a table to track the dates at which any
Person became or ceased to be a Patient (this might be a PatientEvents
table which also tracks visits, interviews, lab tests).

For the relationship tracking, I'd take a look at some genealogical
database structures. Depending on what the study's trying to achieve, do
you also need to track households, so they can analyse who the Patient
was living with at various periods?

 

[Allen Browne]

Ted, I would also be inclined to put everyone into the one table, and use
another table to record the kinds of interterconnections that exist between
the people.

There is a downloadable example of this kind of structure in this article:
People in households and companies - modelling human relationships
at:
http://allenbrowne.com/AppHuman.html

 

[Guest]

thanks be to you both guys.....this is a definite starting point. hopefully
it'll make a lot more sense afterwards.

with best regards,

-ted

 

[Tim Ferguson]

i'm doing some pro bono work at a hospital and i've been asked to try
to put together an a2k (or maybe it's going to be a 2003) database.
the investigator's interested in studying childhood obesity.

<A paediatrician grumbles...>
Obesity is _not_ a medical problem.

would imagine there's a table that's geared around the patient with
one record per patient. there's a table that's geared around the
patient's parent's info w/ one record per parent. a table geared to
record (the same background) data about the patient's close relatives
(siblings, aunt/uncle, grandparents) w/ one record per close relative.
there are some other tables which capture the lab tests done on the
patient at recurring clinic visits.

All this is perfectly reasonable; but I guess there is a lot more
exchange to be done with the researchers. One plan is to get them to
start at their (putative) results -- this includes the hypotheses they
plan to test, what tests they mean to do, and so on. From that you can do
the power calculations, and then work out what data structures you will
need to feed into the stats software. At this stage you can predict what
tables and scchema you need to make the whole thing work. I would beware
of devising complex structures to map recursive family interrelations:
fifty quid says that all they want from the parents is the mean weight!

All the best


Tim F

 

[Guest]

tim,

after looking at allen's design for a while, i began to imagine what the
guys who were tryng to decode the rosetta stone must've felt like (which is
just a reflection on the fact i'm not an archeologist and not on its
soundness). certainly, i wish i were in position to take the pulse of the
investigators more frequently and longer than time has permitted me thus far,
since, as you correctly wrote, there are a few lacunae that need
addressing...i've gotten some indication that my questions are in the process
of being reviewed and replied to in the very near future which is a good sign.

i wanted to thank you for your input and to add that in view of having
managed to get over grappling with allen's mdb paradigm, what i thought i
would do would be to add another level, i.e. "Family" or "Household" to it,
subordinating the tables having the members relevant data to a household---
in the event the investigators later decide to begin capturing data that
would properly belong at that level (e.g. 'value of residence', 'annual
family income', phone number). does that make sense?

-ted

 

[Tim Ferguson]

in the event the investigators later decide to begin capturing data that

well, yes... double-guessing your commissioners is a neccessary evil at
times, but just make sure that they are actually going to be interested
before spending a lot of time on it. Bear in mind the kind of hypotheses
they are going to test. If they are looking at the genetics, then it's the
degree-of-separation that matters much more than actual relationships. If
it's cultural/ family stuff, then recording which school the parents went
to is more important. And so on.

And -- get the advice of a real statistician _before_ setting the data
model into stone!

Best of luck


Tim F