|
Forwarding Yacine's reply via email:
Dear Simon,
I have had a look at the current design of the pediatric tables. I understand your issue about primary key and uniqueness. Please find below some comments and suggestions
tblPREG - Pregnancy
following your comments, i would suggest to have a dedicated ID variable, like PREG_SEQ, to take into account the case of multiples pregnancies within the life of a cohort making the couple (MOTHER_ID, PREG_SEQ) the primary key for this table. The cohort team should be able to manage such a variable that will be under their control. The Date of last menstrual period MENS_D (or even the variable GYNAE_D) will be used to control the consistency of PREG_SEQ.
tblPREG_OBS - Obstretical problems during pregnancy in a relationship
(0,1) with tblPREG should have the same primary key/reference key (MOTHER_ID, PREG_SEQ). An occurence should exist in tblPREG_OBS only if PROB_Y of tblPREG is Yes. I wondered whether a variable is not missing in this table ? We need to have beside the variable PROB_T (all types of obstetrical problem), an additional numeric variable PROB_V (Problem_Value) taking the values: 1=Yes, 0=No, 9=Unknown.
tblPREG_OUT
We assume that CHILD_ID exists even when the variable OUTCOM takes the values 10, 11, 20, 21, ie the pregnancy outcome is negative. This is an important thing to check with the cohort managers. The primary key could be (MOTHER_ID, PREG_SEQ, CHILD_ID) with (MOTHER_ID, PREG_SEQ) as the reference key with tblPREG.
-tblDELIVERY_MUM and tblDELIVERY_CHILD
Obvioulsy tables tblDELIVERY_MUM and tblDELIVERY_CHILD are linked to tblPREG in a relationship (1,1*) and (1,n*) respectively, that means that in some cases a PREGNANCY could not lead to any record in either tblDELIVERY_MUM or tblDELIVERY_CHILD (in case of termination for instance, QA check). (MOTHER_ID, PREG_SEQ) could serve as a primary key (and reference key) for tblDELIVERY_MUM. (MOTHER_ID, PREG_SEQ, CHILD_ID) and (MOTHER_ID, PREG_SEQ) will be the primary key and the reference key to tblPREG.
-tblNEWBORN is in relationship (1,1) with tblDELIVERY_CHILD
tblNEWBORN_ABNORM is in relationship (1,1*) with tblNEWBORN, an
occurence in tblNEWBORN_ABNORM existing only if ABNORM_Y of tblNEWBORN = Yes. As for table tblPREG_OBS, i wondered whether we don't need to add the variable ABNORM_V (value corresponding to ABNORM_T) unless you are assuming that cohort will only report the occurring abnormalities (is we don't want to register the No and Unknown values). Please could you clarify ?
I hope these comments will be useful.
Best regards, Yacine
|