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:

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

There is one record in tblDELIVERY_MUM:

There are two records in tblDELIVERY_CHILD:

There are two records in tblNEWBORN:

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.