wiki:Hicdep_1.60/NotesPregnancyTables

Version 2 (modified by nevma, 5 years ago) (diff)

Added OUTCOM to example tblPREG_OUT

Notes on the Pregnancy-Related Tables

The correct and accurate modelling of a pregnancy and its outcomes is rather involved, and some assumptions had to be made. This page illustrates the model used in the pregnancy tables to which these assumptions lead.

Example

Consider the following example:

  1. A female patient (123) gets pregnant at time (a).
  2. At time (b) she has an abortion which causes the dead fetus of CHILD 701.
  3. At time (c), she gets pregnant again.
  4. The delivery of this pregnancy is at time (d), when the twins (CHILD 702 and CHILD 703) are born.
              (a)     (b)             (c)          (d)
MUM 123   -----|-------|---------------|------------|--------------------

CHILD 701              |-|
CHILD 702                                           |--------------------
CHILD 703                                           |--------------------

This story is described in the different pregnancy tables as follows:

There are two records in tblPREG, one for each pregnancy:

MOTHER_ID PREG_SEQ MENS_D ...
123 1 (a) ...
123 2 (c) ...

There are three records in tblPREG_OUT describing the pregnancy outcome for each fetus:

MOTHER_ID PREG_SEQ CHILD_ID OUTCOM ...
123 1 701 21 ...
123 2 702 1 ...
123 2 703 1 ...

There is one record in tblDELIVERY_MUM:

MOTHER_ID PREG_SEQ MEMRUP_D ...
123 2 (d) ...

There are two records in tblDELIVERY_CHILD:

MOTHER_ID MEMRUP_D CHILD_ID ...
123 (d) 702 ...
123 (d) 703 ...

There are two records in tblNEWBORN:

CHILD_ID ...
702 ...
703 ...

Important relations

The following statements are true for a well-encoded data set:

  1. For every record in tblPREG, there are 1 or more records in tblPREG_OUT referring to it.
  2. For every record in tblPREG_OUT that describes a delivery, there is exactly one record in tblDELIVERY_CHILD referring to it.
  3. For every record in tblDELIVERY_MUM, there are 1 or more records in tblDELIVERY_CHILD referring to it indirectly through tblDELIVERY_MUM.
  4. For every record in tblNEWBORN there is exactly one record in tblDELIVERY_CHILD referring to the same child.
  5. For every record in tblDELIVERY_MUM, there is some record in tblPREG to which it refers.