透視表“或”否透視表

Original post: http://anothermysqldba.blogspot.com/2013/06/piviot-table-or-no-pivot-table.html

這個話題最近想出上forums.mysql.com網站。 

表達的意見是很難擴展和維護，這將是值得重新設計架構，而不是透視表數據透視表。 這是一個有效的意見與有效點。 

我想在這裡添加主題幫助表達我的觀點，並為他人提供。 

這一切都取決於正在收集關於是否應該使用數據透視表或數據。 我在以前的帖子中給出的例子只是一個例子，他們是如何工作的。 

如果你正在收集已知的用戶信息（第一個和最後一個名字，地址信息，電話），然後是一個數據透視表更複雜，你需要什麼。 如果你只是有幾個數據點，以配合他們到外，核心信息，然後是另一個表是一個解決方案，並綁用一個簡單的連接。 

數據透視表的概念是有效的，當它是動態量的每個實體所收集的數據。 
您可能需要10個數據點100個用戶。 您可能需要500未來100用戶的數據點。 架構可以很容易處理？ 

在以前的帖子中給出的例子，我同意不要求一個數據透視表。 但我只是用在論壇給我的概念來回答這個問題問。 

理想的情況下，你可以使用這兩種模式中的解決方案。 核心數據點，保持在列。 動態數據保留在數據透視表中。 

如果它是建立正確的可伸縮性非常大，億萬我存儲在數據透視表的數據證明了這一點對我來說很容易。 這並不意味著它不會需要一些工作。 你很可能會發現，創造了一些意見或匯總表，數據透視表看起來會更容易為他人收集數據。 這引出了一個問題，那麼為什麼不以那種方式存儲的數據擺在首位？ 再次，這取決於您的數據和應用程序使用數據的動態性質。 

透視表的例子在MySQL

Original post: http://anothermysqldba.blogspot.com/2013/06/pivot-tables-example-in-mysql.html

有人問我如何建立一個訂 ​​閱表來跟踪課程等上forums.mysql.com網站 

這是比較容易在這裡發布完整的示例，它是一種快速簡單的例子，但你的想法。 

這裡的概念是很簡單的。 
我們存儲信息的行，我們可以再拉回來時需要到不同的列。 

要求學生和課程認購.... 

首先，我建立了一些表和數據... 


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() ) ;

首先是拉學生的信息： 


> 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) 

我們可以挖掘更多的，不斷增加信息... 


> 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) 

這是非常有用的或乾淨的，雖然... 
所以重做拉正是我們想要的... 


> 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)