I was asked on the forums.mysql.com site how to build a subscription table to track courses in which a user has enrolled.



The concept here is simple.

We store information in rows that we can then pull back out into different columns when needed.



The particular request was for a subscription of students and courses.



First, I built out some tables and data.



 CREATE TABLE `details` (

  `details_id` int(11) NOT NULL AUTO_INCREMENT,

  `details_label` varchar(100) DEFAULT NULL,

  PRIMARY KEY (`details_id`)

) ENGINE=InnoDB; 

INSERT INTO details VALUES (1,'First Name') , (2, 'Last Name') ;



CREATE TABLE `subjects` (

  `subject_id` int(11) NOT NULL AUTO_INCREMENT,

  `subject` enum('History','English','Geography','Mathematics','Science','Social Studies','Foreign Languages','Visual and Performing Arts') DEFAULT NULL,

  `subject_detail` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`subject_id`)

) ENGINE=InnoDB;

INSERT INTO subjects VALUES (1,'Mathematics', 'Algebra') , (2,'History', '1826-1926')  ,  (3,'Geography', ' Africa Studies') ;



CREATE TABLE `student` (

  `student_id` int(11) NOT NULL AUTO_INCREMENT,

  `email` varchar(150) DEFAULT NULL,

  `student_key` varchar(20) DEFAULT NULL,

 `date_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`student_id`)

) ENGINE=InnoDB;

INSERT INTO student (`student_id` ,`email`,`student_key`) VALUES (1,'foobar@gmail.com','iasdjf'); 



CREATE TABLE `student_details` (

  `student_details_id` int(11) NOT NULL AUTO_INCREMENT,

  `student_id` int(11) DEFAULT 0,

  `details_id` int(11)  DEFAULT 0,

  `details_value` varchar(255) DEFAULT NULL,

  `date_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`student_details_id`)

) ENGINE=InnoDB;

INSERT INTO student_details VALUES (NULL,1,1,'John',NOW()) ,  (NULL,1,2,'Smith',NOW()) ; 



 CREATE TABLE `courselist` (

  `courselist_id` int(11) NOT NULL AUTO_INCREMENT,

  `student_id` int(11) DEFAULT 0,

  `subject_id` int(11) DEFAULT NULL,

  `status` enum('Waitlisted','Subscribed','Denied') DEFAULT NULL,

  `date_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`courselist_id`)

) ENGINE=InnoDB; 

INSERT INTO courselist VALUES ( NULL,1, 1 , 'Waitlisted' , NOW() ) , ( NULL,1, 2 , 'Subscribed' , NOW() )  , ( NULL,1, 3 , 'Denied' , NOW() )   ;



First, just pull information about the student:



> SELECT s.student_id , d.details_label , sd.details_value

    -> FROM student s 

    -> INNER JOIN student_details sd ON s.student_id = sd.student_id

    -> INNER JOIN details d ON sd.details_id = d.details_id;

+------------+---------------+---------------+

| student_id | details_label | details_value |

+------------+---------------+---------------+

|          1 | First Name    | John          |

|          1 | Last Name     | Smith         |

+------------+---------------+---------------+

2 rows in set (0.00 sec)



We can dig more and keep adding information...





> SELECT s.student_id , d.details_label , sd.details_value , c.status, j.subject, j.subject_detail

    -> FROM student s 

    -> INNER JOIN student_details sd ON s.student_id = sd.student_id

    -> INNER JOIN details d ON sd.details_id = d.details_id

    -> INNER JOIN courselist c ON s.student_id = c.student_id

    -> INNER JOIN subjects j ON j.subject_id = c.subject_id

    -> ;

+------------+---------------+---------------+------------+-------------+-----------------+

| student_id | details_label | details_value | status     | subject     | subject_detail  |

+------------+---------------+---------------+------------+-------------+-----------------+

|          1 | First Name    | John          | Waitlisted | Mathematics | Algebra         |

|          1 | Last Name     | Smith         | Waitlisted | Mathematics | Algebra         |

|          1 | First Name    | John          | Subscribed | History     | 1826-1926       |

|          1 | Last Name     | Smith         | Subscribed | History     | 1826-1926       |

|          1 | First Name    | John          | Denied     | Geography   |  Africa Studies |

|          1 | Last Name     | Smith         | Denied     | Geography   |  Africa Studies |

+------------+---------------+---------------+------------+-------------+-----------------+

6 rows in set (0.00 sec) 



That is not very useful or clean though...

So, let's redo this table to pull exactly what we want...





> SELECT s.student_id ,sd1.details_value as FIRST_NAME, sd2.details_value as LAST_NAME,   c.status, j.subject, j.subject_detail

    -> FROM student s 

    -> INNER JOIN student_details sd1 ON s.student_id = sd1.student_id AND sd1.details_id = 1

    -> INNER JOIN student_details sd2 ON s.student_id = sd2.student_id AND sd2.details_id = 2

    -> INNER JOIN courselist c ON s.student_id = c.student_id

    -> INNER JOIN subjects j ON j.subject_id = c.subject_id

    -> ;

+------------+------------+-----------+------------+-------------+-----------------+

| student_id | FIRST_NAME | LAST_NAME | status     | subject     | subject_detail  |

+------------+------------+-----------+------------+-------------+-----------------+

|          1 | John       | Smith     | Waitlisted | Mathematics | Algebra         |

|          1 | John       | Smith     | Subscribed | History     | 1826-1926       |

|          1 | John       | Smith     | Denied     | Geography   |  Africa Studies |

+------------+------------+-----------+------------+-------------+-----------------+

3 rows in set (0.00 sec) 



If you've used this guide to build subscription tables, leave me a comment to let me know what you think. Thanks for reading!