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';

Last updated 19 Oct 2010

Return to the Artful MySQL Tips page