Pivot table or No Pivot Table

This topic recently came up on the forums.mysql.com site.

The opinion expressed was that pivot tables are very hard to scale and maintain it would be worth a redesign of schema instead of a pivot table. This is a valid opinion with valid points.

I would like to add the topic here to help express my point of view and have it available for others.

It all depends on the data being gathered on if you should use a pivot table or not. The example given in a previous post by me was just an example of how they work.

If you are collecting known  user information (First and Last name, Address information, Phone ) then yes a pivot table is more complicated that what you would need.  If you just have a few data points to tie them to outside of that core information then yes another table is a solution and tied with a simple join.

The pivot table concept is valid when it is for dynamic amounts of data per entity you are collecting.
You might need 10 data points for 100 users. You might need 500 data points on the next 100 users.  Can the schema handle it easily?  

The example given in previous post I agree  does not require a pivot table. But I just used the concept given to me in the forum to answer the question asked.

Ideally you can use both solutions in your schema. Core data points, keep in columns. Dynamic data keep in pivot tables.

If it is built correctly it is very scalable, the billions and billions of data I stored in the pivot table proved this to me easily.  That does not mean it would not require some work. You very well might find that creating some views or summary tables that look into the pivot table would be easier for others to gather data. This begs the question then why wasn't the data stored that way in the first place? Again it depends on the dynamic nature of your data and application that uses the data.

Pivot tables example in MySQL

I was asked on the forums.mysql.com site how to build a subscription table to track courses and etc.

It was easier to post the full example here, it is a quick brief example but you get the idea.

The concept here is simple.
We store information in rows that we can then pull back out into different columns when needed.

The 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 redo this 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)