mysql - 如何在单个表中存储多个选项？

Question

我想设计一个用于结果计算的应用程序。

首先，我需要知道如何在 MySQL 数据库中存储记录，以便学生可以拥有尽可能多的类(class)，例如学生 A 可以附加 6 个科目，而学生 B 可以附加 12 个科目。

在这种情况下，我需要知道如何设计一种数据库结构，以允许字段以 数组 的形式存储尽可能多的主题。

我们将不胜感激任何建议或更好的处理方法。

Answer 1

请继续阅读 Data Normalization , General Indexing概念，和 Foreign Key保持数据清洁和参照完整性的约束。这会让你继续前进。

在纸面上，将数据存储在数组中对您来说似乎很自然，但对于数据库引擎来说，性能主要是在没有使用索引的情况下。此外，您会在第 2 天发现获取和维护数据将是一场噩梦。

以下内容应该可以让您在修补时有一个良好的开端。 Joins也是。

create table student
(   studentId int auto_increment primary key,
    fullName varchar(100) not null
    -- etc
);

create table dept
(   deptId int auto_increment primary key,
    deptName varchar(100) not null -- Economics
    -- etc
);

create table course
(   courseId int auto_increment primary key,
    deptId int not null,
    courseName varchar(100) not null,
    -- etc
    CONSTRAINT fk_crs_dept FOREIGN KEY (deptId) REFERENCES dept(deptId)
);

create table SCJunction
(   -- Student/Course Junction table (a.k.a Student is taking the course)
    -- also holds the attendance and grade
    id int auto_increment primary key,
    studentId int not null,
    courseId int not null,
    term int not null, -- term (I am using 100 in below examples for this term)
    attendance int not null, -- whatever you want, 100=always there, 0=he must have been partying,
    grade int not null, -- just an idea   
    -- See (Note Composite Index) at bottom concerning next two lines.
    unique key(studentId,courseId,term), -- no duplicates allowed for the combo (note student can re-take it next term)
    key (courseId,studentId),
    CONSTRAINT fk_sc_student FOREIGN KEY (studentId) REFERENCES student(studentId),
    CONSTRAINT fk_sc_courses FOREIGN KEY (courseId) REFERENCES course(courseId)
);

创建测试数据

insert student(fullName) values ('Henry Carthage'),('Kim Billings'),('Shy Guy'); -- id's 1,2,3
insert student(fullName) values ('Shy Guy');

insert dept(deptName) values ('History'),('Math'),('English'); -- id's 1,2,3

insert course(deptId,courseName) values (1,'Early Roman Empire'),(1,'Italian Nation States'); -- id's 1 and 2 (History dept)
insert course(deptId,courseName) values (2,'Calculus 1'),(2,'Linear Algebra A'); -- id's 3 and 4 (Math dept)
insert course(deptId,courseName) values (3,'World of Chaucer'); -- id 5 (English dept)

-- show why FK constraints are important based on data at the moment
insert course(deptId,courseName) values (66,'Fly Fishing 101'); -- will generate error 1452. That dept 66 does not exist
-- That error is a good error to have. Better than faulty data

-- Have Kim (studentId=2) enrolled in a few courses
insert SCJunction(studentId,courseId,term,attendance,grade) values (2,1,100,-1,-1); -- Early Roman Empire, term 100 (made up), unknown attendance/grade
insert SCJunction(studentId,courseId,term,attendance,grade) values (2,4,100,-1,-1); -- Linear Algebra A
insert SCJunction(studentId,courseId,term,attendance,grade) values (2,5,100,-1,-1); -- World of Chaucer

-- Have Shy Guy (studentId=3) enrolled in one course only. He is shy
insert SCJunction(studentId,courseId,term,attendance,grade) values (3,5,100,-1,-1); -- Early Roman Empire, term 100 (made up), unknow attendance/grade
-- note if you run that line again, the Error 1062 Duplicate entry happens. Can't take same course more than once per term

一些简单的问题。

哪个系有什么类(class)？

全部显示，使用表别名(缩写)减少输入，提高可读性(有时)

select c.courseId,c.courseName,d.deptId,d.deptName
from course c
join dept d
on c.deptId=d.deptId
order by d.deptName,c.courseName -- note the order
+----------+-----------------------+--------+----------+
| courseId | courseName            | deptId | deptName |
+----------+-----------------------+--------+----------+
|        5 | World of Chaucer      |      3 | English  |
|        1 | Early Roman Empire    |      1 | History  |
|        2 | Italian Nation States |      1 | History  |
|        3 | Calculus 1            |      2 | Math     |
|        4 | Linear Algebra A      |      2 | Math     |
+----------+-----------------------+--------+----------+

谁在这个学期学习乔叟的世界类(class)？

(知道courseId=5)

以下受益于我们在 SCJunction 中的一个复合索引。复合是对多个列的索引。

select s.StudentId,s.FullName
from SCJunction j
join student s
on j.studentId=s.studentId
where j.courseId=5 and j.term=100
+-----------+--------------+
| StudentId | FullName     |
+-----------+--------------+
|         2 | Kim Billings |
|         3 | Shy Guy      |
+-----------+--------------+

Kim Billings 这学期在读什么？

select s.StudentId,s.FullName,c.courseId,c.courseName
from SCJunction j
join student s
on j.studentId=s.studentId
join course c
on j.courseId=c.courseId
where s.studentId=2 and j.term=100
order by c.courseId DESC -- descending, just for the fun of it
+-----------+--------------+----------+--------------------+
| StudentId | FullName     | courseId | courseName         |
+-----------+--------------+----------+--------------------+
|         2 | Kim Billings |        5 | World of Chaucer   |
|         2 | Kim Billings |        4 | Linear Algebra A   |
|         2 | Kim Billings |        1 | Early Roman Empire |
+-----------+--------------+----------+--------------------+

Kim 不知所措，所以放弃数学课

delete from SCJunction
where studentId=2 and courseId=4 and term=100

运行上面的 select 语句显示 Kim 正在采取什么:

+-----------+--------------+----------+--------------------+
| StudentId | FullName     | courseId | courseName         |
+-----------+--------------+----------+--------------------+
|         2 | Kim Billings |        5 | World of Chaucer   |
|         2 | Kim Billings |        1 | Early Roman Empire |
+-----------+--------------+----------+--------------------+

啊，更简单的术语。爸爸不会高兴的。

注意诸如 SCJunction.term 之类的东西。关于它可以写很多，我现在主要跳过它，除了说它也应该在某个地方的 FK 中。您可能希望您的术语看起来更像 SPRING2015 而不是整数。

就 id 而言。这就是我会做的方式。这是个人喜好。它需要知道 id #'s，查找它们。其他人可以选择像 HIST101 而不是 17 这样的 courseId。它们的可读性更高(但索引速度较慢(几乎)。所以做最适合你的事情。

纸币综合指数

复合索引(INDEX 表示 KEY，反之亦然)是一种组合多个列以实现快速数据检索的索引。 SCJunction 表中的两个组合的顺序被翻转，以便根据您的数据之后的查询范围，数据库引擎可以根据您要查找的最左边的列选择要使用哪个索引进行最快的检索.

至于唯一键#1，它旁边的注释说明不强制重复(意思是垃圾数据)是不言自明的。例如，student 1 course 1 term 1 不能在该表中出现两次。

要理解的一个关键概念是索引中列名的最左边排序概念。

对于 studentId 仅 之后的查询，首先列出studentId 的键(最左边) 被使用。在 courseId 仅 之后的查询中，使用最左侧具有 courseId 的键。在 studentId 和 courseId 之后的查询中，数据库引擎可以决定使用哪个复合键。

当我说“go after”时，我指的是在 on 子句 或 where 子句 条件中。

如果没有这两个复合键(其中的第 1 列和第 2 列被翻转)，那么在查询中所查找的列不是left-most 索引的查询中，您不会受益于 key 使用，并遭受缓慢的表扫描以返回数据。

因此，这两个索引结合了以下两个概念

• 基于最左边或两者(studentId 和 courseId 列)的快速数据检索
• 根据 studentId、courseId 和 term 值强制该表中的数据不重复

要点

重要的要点是，Junction 表有助于快速索引检索，并且可以对数据进行合理的管理，而不是将逗号分隔的数据(数组思维)塞进一列，以及使用这种表的所有痛苦构造。