如何在单个表中存储多个选项？ [英] How to store multiple options in a single table?
问题描述
我想为结果计算设计一个应用程序。
首先，我需要知道如何在MySQL数据库中存储记录，使学生可以他们附有许多课程，例如学生A可以附加6个科目，而学生B可以附加12个科目。
在这种情况下，我需要知道如何设计数据库结构，允许字段以数组的形式存储尽可能多的主题。
任何建议或更好的处理方式这将非常感激。
请阅读数据规范化，一般索引概念和外部键约束以保持数据清理与引用完整性。这将让你走。
将数据存储在数组中可能在纸上看起来很自然，但对于db引擎来说，性能大多没有索引使用。此外，您将在第二天找到获取和维护您的数据将是一场噩梦。
以下内容让您在修补程序时有一个良好的开端。 加入。
create table student
（studentId int auto_increment primary key，
fullName varchar（100）not null
- etc
）;
创建表dept
（deptId int auto_increment主键，
deptName varchar（100）not null - Economics
- etc
）;
创建表课程
（courseId int auto_increment主键，
deptId int not null，
courseName varchar（100）not null，
- etc
CONSTRAINT fk_crs_dept FOREIGN KEY（deptId）REFERENCES dept（deptId）
）;
创建表SCJunction
（ - 学生/课程衔接表（又名学生正在接受课程）
- 也保存考勤和成绩
id int auto_increment主键
studentId int not null，
courseId int not null，
term int not null， - term（我在下面的例子中使用100）
出席率int not null， - 无论你想要什么，100 =总是在那里，0 =他一定是在派对，
grade int not null， - 只是一个想法
- 参见（注意综合指数）at
唯一键（studentId，courseId，term）， - 不允许组合重复（注意学生可以重新下一步）
key（courseId，studentId），
CONSTRAINT fk_sc_student FOREIGN KEY（studentId）REFERENCES student（studentId），
CONSTRAINT fk_sc_courses FOREIGN KEY（courseId）REFERENCES课程（courseId）
）;
创建测试数据
insert student（fullName）values（'Henry Carthage'），（'Kim Billings'），（'Shy Guy'）; - id的1,2,3
插入学生（fullName）值（'害羞'）;
insert dept（deptName）values（'History'），（'Math'），（'English'）; - id的1,2,3
插入课程（deptId，courseName）值（1，'早期罗马帝国'），（1，'意大利国家国家'）; - id的1和2（History dept）
insert course（deptId，courseName）values（2，'Calculus 1'），（2，'Linear Algebra A'）; - id的3和4（Math dept）
insert course（deptId，courseName）values（3，'World of Chaucer'）; - id 5（英语dept）
- 显示为什么FK约束基于当前数据
insert course（deptId，courseName）值（66，'Fly Fishing 101' ）; - 将生成错误1452.该部门66不存在
- 该错误是一个很好的错误。比错误的数据更好
- 有Kim（studentId = 2）注册几个课程
插入SCJunction（studentId，courseId，term，attendance，grade）值（2,1,100， - 1，-1）; - 早期罗马帝国，术语100（组成），未知出席/年级
插入SCJunction（studentId，courseId，术语，出勤率，成绩）值（2,4,100，-1，-1）; - 线性代数A
插入SCJunction（studentId，courseId，term，attendance，grade）值（2,5,100，-1，-1）; - 乔治世界
- 有害人（studentId = 3）仅注册一门课程。他是害羞的
插入SCJunction（studentId，courseId，term，attendance，grade）值（3,5,100，-1，-1）; - 早期罗马帝国，术语100（组成），unknow考勤/等级
- 注意，如果您再次运行该行，错误1062重复条目发生。每个术语不能一次性相同的课程
一些简单的问题。
什么课程在哪个部门？
显示全部，使用表别名（缩写）使打字更少，可读性（有时更好）
select c.courseId，c.courseName，d.deptId，d.deptName
from course c
加入dept d
on c.deptId = d.deptId
order by d.deptName，c.courseName - 注意顺序
+ ---------- + ----------------------- + -------- + ---------- +
| courseId | courseName | deptId | deptName |
+ ---------- + ----------------------- + -------- + - --------- +
| 5 |乔er世界| 3 |英文|
| 1 |早期罗马帝国| 1 |历史|
| 2 |意大利国家| 1 |历史|
| 3 |微积分1 | 2 |数学|
| 4 |线性代数A | 2 |数学|
+ ---------- + ----------------------- + -------- + - --------- +
谁来参加乔治世界的这个学期？
（知道courseId = 5）
以下是SCJunction中的一个复合索引。复合是多个列的索引。
select s.StudentId，s.FullName
来自SCJunction j
加入学生s
在j.studentId = s.studentId
其中j.courseId = 5和j.term = 100
+ ---------- - + -------------- +
| StudentId |全名|
+ ----------- + -------------- +
| 2 |金比林斯|
| 3 |害羞的家伙|
+ ----------- + -------------- +
Kim Billings参加了这个术语？
select s.StudentId，s .FullName，c.courseId，c.courseName
从SCJunction j
加入学生s
on j.studentId = s.studentId
加入课程c
on j。 courseId = c.courseId
其中s.studentId = 2和j.term = 100
order by c.courseId DESC - 降序，只为了它的乐趣
+ ---- ------- + -------------- + ---------- + ---------------- ---- +
| StudentId |全名| courseId | courseName |
+ ----------- + -------------- + ---------- + ------- ------------- +
| 2 |金比宁斯| 5 |乔er世界|
| 2 |金比林斯| 4 |线性代数A |
| 2 |金比林斯| 1 |早期罗马帝国|
+ ----------- + -------------- + ---------- + ------- ------------- +
Kim不堪重负，所以下降数学类
从SCJunction
中删除，其中studentId = 2和courseId = 4，term = 100
运行上面的select语句，显示金正在服用的：
+ ----------- + -------------- + ---------- + - ------------------- +
| StudentId |全名| courseId | courseName |
+ ----------- + -------------- + ---------- + ------- ------------- +
| 2 |金比林斯| 5 |乔er世界|
| 2 |金比林斯| 1 |早期罗马帝国|
+ ----------- + -------------- + ---------- + ------- ------------- +
啊，更容易一点。爸爸不会很高兴，但是。/ p>
注意这样的事情，如SCJunction.term。很多都可以写出来，我现在将跳过它，除了说它也应该在一个FK的某个地方。你可能希望你的术语看起来更像是SPRING2015，而不是一个int。
只要id就去了。这是我做的方式。这是个人喜好。它需要知道id＃，查找它们。其他人可以选择拥有一个类似HIST101而不是17的课程。这些读者的阅读能力非常强，但索引较慢（几乎没有），所以最适合你。
< h2>注意复合索引
复合索引（INDEX表示KEY，反之亦然）是组合多个列用于快速数据检索的索引，命令翻转为两个复合材料在SCJunction表中，这样，根据您的数据后面的查询的Universe，数据库引擎可以根据您要执行的最左边的列，选择要使用哪个索引进行最快的检索。
对于唯一键＃1，旁边的注释说明强制不重复（意味着垃圾数据）是不言自明的，例如，学生1课程1术语1不能存在
要理解的一个关键概念是最左边排列列名称的概念一个索引。
对于 studentId 仅之后的查询，首先列出 studentId （最左边）。在 courseId 之后的查询中，最左边的 courseId 的键用来。在studentId和courseId之后的查询中，db引擎可以决定要使用哪个复合密钥。
当我说后续时，我的意思是在code> on子句或 where子句条件。
有这两个复合键（其中的第1列和第2列翻转），然后在查询列中不是最左边索引的查询中，您不会受益于键因此，这两个索引组合了以下两个概念：
ul>
外卖
重要的外卖是，Junction表可以快速索引检索，并将数据的统一管理与逗号分隔的数据（数组思维）填充到列中，以及使用这种构造的所有痛苦。
I want to design an application for result computation.
First, I need to know how to store record in a MySQL database in such a way that students can have as many courses attached to them, e.g. student A can have 6 subjects attached to him, while student B can have 12 subject attached to him.
In this case, I need to know how I could possibly design a database structure that will allow a field to store as many subject as possible in form of an array.
Any suggestion or a better way to handle this will highly be appreciated.
Please read up on Data Normalization, General Indexing concepts, and Foreign Key constraints to keep data clean with referential integrity. This will get you going.
Storing data in arrays may seem natural to you on paper, but to the db engine the performance with mostly be without index use. Moreover, you will find on Day 2 that getting to and maintaining your data will be a nightmare.
The following should get you going with a good start as you tinker. Joins too.
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)
);
Create Test Data
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
Some simple questions.
What course is in what department?
show all, uses table aliases (abbreviations) to make typing less, readability (sometimes) better
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 |
+----------+-----------------------+--------+----------+
Who is taking the World of Chaucer course this term?
(knowing the courseId=5)
The below benefits from one of our composite indexes in SCJunction. A composite is an index on more than one column.
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 is enrolled in what this term?
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 is overwhelmed, so drop drop the math class
delete from SCJunction
where studentId=2 and courseId=4 and term=100
run that above select statement showing what Kim is taking:
+-----------+--------------+----------+--------------------+
| StudentId | FullName | courseId | courseName |
+-----------+--------------+----------+--------------------+
| 2 | Kim Billings | 5 | World of Chaucer |
| 2 | Kim Billings | 1 | Early Roman Empire |
+-----------+--------------+----------+--------------------+
Ah, much easier term. Dad won't be happy though.
Note such things as SCJunction.term. Much can written about that, I will skip over it at the moment mostly, other than to say it should also be in an FK somewhere. You may want your term to look more like SPRING2015 and not an int.
And as far as id's go. This is the way I would do it. It is personal preference. It would require knowing id #'s, looking them up. Others could choose to have a courseId something like HIST101 and not 17. Those are highly more readable (but slower in the index (barely). So do what is best for you.
Note Composite Index
A Composite Index (INDEX means KEY, and vice-versa) is one that combines multiple columns for fast data retrieval. The orders are flipped for the two composites in the SCJunction table so that, depending on the universe of queries that go after your data, the db engine can choose which index to use for fastest retrieval based on the left-most column you are going after.
As for the unique key, #1, the comment next to it stating enforcing no duplicates (meaning junk data) is rather self-explanatory. For instance, student 1 course 1 term 1 cannot exist twice in that table.
A crucial concept to understand is the concept of left-most ordering of column names in an index.
For queries that go after studentId only, then the key that has studentId listed first (left-most) is used. In queries that go after courseId only, then the key that has courseId left-most is used. In queries that go after both studentId and courseId, the db engine can decide which composite key to use.
When I say "go after", I mean in the on clause or where clause condition.
Were one not to have those two composite keys (with the column 1 and 2 in them flipped), then in queries where the column sought is not left-most indexed, you would not benefit with key usage, and suffer a slow tablescan for data to return.
So, those two indexes combine the following 2 concepts
- Fast data retrieval based on left-most or both (studentId and courseId columns)
- Enforcing non-duplication of data in that table based on studentId, courseId, and term values
The Takeaway
The important takeaway is that Junction tables make for quick index retrieval, and sane management of data versus comma-delimited data (array mindset) crammed into a column, and all the misery of using such a construct.
这篇关于如何在单个表中存储多个选项？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
