When and how to project child rows

from the Artful MySQL Tips List


A table lists periods of administered medications and associated side effects:


drop table if exists mini_exposure_to_medication;

create table mini_exposure_to_medication (

  medpatkey int(10),

  medage int(4),

  MYSTART varbinary(10),

  MYSTOP varbinary(29),

  MYMEDCODE int(9),

  onset_date date,

  has_ae varchar(5)

);



The column 'has_ae' ("has after-effects") is supposed to have four options:
  • 'never': took the medication and never had a side effect
  • 'N'; took medication and had a reaction after a period of time such that it cannot be attributed to the medication
  • 'Y': took a medication and had a reaction during or immediately after taking the medication
  • 'B': had the side effect before taking the medication
Here is sample data:


insert into mini_exposure_to_medication 

(medpatkey, medage, MYSTART, MYSTOP, MYMEDCODE, onset_date, has_ae) Values

(56,64,'1996-08-05','1996-08-05',7,NULL,"never"),

(56,72,'2004-12-13','2006-12-03',6,NULL,"never"),

(56,72,'2004-12-06','2005-12-01',6,NULL,"never"),

(56,72,'2005-02-23','2006-02-18',6,NULL,"never"),

(40,68,'1996-06-27','1996-07-27',6,NULL,"never"),

(40,68,'1997-01-14','1997-02-13',6,NULL,"never"),

(53,80,'2003-11-10','2004-11-04',1,NULL,"never"),

(53,80,'2004-10-21','2005-10-16',1,NULL,"never"),

(53,80,'2005-05-26','2006-05-21',1,NULL,"never"),

(53,80,'2005-09-30','2006-09-25',1,NULL,"never"),

(53,80,'2006-02-21','2007-02-16',1,NULL,"never"),

(53,80,'2006-06-20','2007-06-15',1,NULL,"never"),

(94,78,'2006-05-09','2006-05-09',5,NULL,"never"),

(781,47,'2005-12-22','2005-12-22',1,NULL,"never"),

(781,47,'2006-08-19','2006-08-19',1,NULL,"never"),

(790,76,'2003-10-28','2003-11-27',6,NULL,"never"),

(790,76,'2004-12-08','2005-01-07',6,NULL,"never"),

(294,60,'2004-05-04','2005-04-29',6,NULL,"never"),

(294,60,'2004-10-28','2005-10-23',6,NULL,"never"),

(294,60,'2005-10-24','2006-10-19',6,NULL,"never"),

(294,60,'2006-05-19','2007-05-14',6,NULL,"never"),

(515,75,'2005-02-18','2005-02-18',7,NULL,"never"),

(538,80,'2004-01-08','2004-01-08',1,NULL,"never"),

(538,80,'2004-06-28','2004-06-28',1,NULL,"never"),

(538,80,'2004-11-29','2004-11-29',1,NULL,"never"),

(538,80,'2005-06-03','2005-06-03',1,NULL,"never"),

(538,80,'2005-11-04','2005-11-04',1,NULL,"never"),

(538,80,'2006-04-11','2006-04-11',1,NULL,"never"),

(538,80,'2006-04-12','2006-04-12',1,NULL,"never"),

(898,66,'2005-08-01','2005-11-29',2,'2005-03-29',"B"),

(898,67,'2005-08-31','2005-09-30',2,'2005-03-29',"B"),

(898,67,'2006-09-29','2007-02-26',5,'2005-03-29',"B"),

(327,66,'2005-06-06','2005-06-06',1,'2006-03-09',"N"),

(327,67,'2006-03-09','2006-03-09',1,'2006-03-09',"Y"),

(2322,80,'2003-12-08','2003-12-08',5,'1995-11-02',"B"),

(2322,80,'2005-10-27','2005-10-27',5,'1995-11-02',"B"),

(2089,68,'2006-08-30','2007-08-25',7,'1998-03-11',"B"),

(2089,68,'2006-08-30','2007-08-25',7,'2004-06-11',"B"),

(2089,68,'2006-07-12','2007-07-07',7,'1998-03-11',"B"),

(2089,68,'2006-07-12','2007-07-07',7,'2004-06-11',"B");



A basic rule of thumb for discovering what query design pattern to apply is to look for normalisation problems. The mini_exposure_to_medication (`mem`) table needs normalising to get to 3NF: remove the side effects data from `mem` to a child table. Or in relationspeak, project them.

Then we have a parent table with one row per medication period, and a child table with one row per side effect event per medication period:


DROP TABLE IF EXISTS med_exposures;

CREATE TABLE med_exposures (

  ID INT PRIMARY KEY AUTO_INCREMENT,

  medpatkey INT,

  medage SMALLINT,

  medstart DATE,

  medstop DATE,

  mymedcode INT

);



DROP TABLE IF EXISTS side_effects;

CREATE TABLE side_effects (

  id INT PRIMARY KEY AUTO_INCREMENT,

  memkey INT,

  onset_date DATE NOT NULL

);



INSERT INTO med_exposures 

  SELECT DISTINCT NULL, medpatkey,medage,mystart,mystop,mymedcode

  FROM mini_exposure_to_medication;



INSERT INTO side_effects

  SELECT NULL,m.id,mem.onset_date

  FROM med_exposures m

  JOIN mini_exposure_to_medication mem ON m.medpatkey=mem.medpatkey AND m.medstart=mem.mystart

  WHERE mem.onset_date IS NOT NULL;



Now side-effect queries are dead simple, eg ...


SELECT 

   m.id,m.medstart,m.medstart,

   s.onset_date,   

   CASE

     WHEN s.onset_date IS NULL THEN 'Never'

     WHEN s.onset_date < m.medstart THEN 'Before'

     WHEN DATEDIFF( s.onset_date, m.medstart ) > 30 THEN 'No'

     ELSE 'Yes'

   END AS 'Side effects'

FROM med_exposures m

LEFT JOIN side_effects s ON m.id = s.memkey

HAVING `side effects` <> 'Never';




Return to the Artful MySQL Tips page