如何在单个表中存储多个选项?

问题描述

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

I want to design an application for result computation.

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

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.

在纸面上将数据存储在数组中对您来说似乎很自然，但对于数据库引擎而言，其性能大多不使用索引.此外，您会在第 2 天发现获取和维护数据将是一场噩梦.

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

创建测试数据

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

一些简单的问题.

显示全部，使用表格别名(缩写)来减少打字次数，提高可读性(有时)

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

本学期谁在学习乔叟世界的课程?

(知道 courseId=5)

Who is taking the World of Chaucer course this term?

(knowing the courseId=5)

以下受益于我们在 SCJunction 中的综合索引之一.组合是在多个列上的索引.

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 在这个学期注册了什么?

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 不知所措，所以放弃数学课

Kim is overwhelmed, so drop drop the math class

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

运行上面的 select 语句，显示 Kim 正在采取的措施:

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.

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

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.

就 id 而言.这就是我要做的方式.这是个人喜好.它需要知道 id #'s，查找它们.其他人可以选择使用类似于 HIST101 而不是 17 的 courseId.它们的可读性更高(但索引速度较慢(勉强).所以做最适合你的.

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.

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

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.

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

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.

对于在 studentId only 之后的查询，首先列出 studentId 的键(最左边) 使用.在courseId only 之后的查询中，使用最左侧具有courseId 的键.在同时跟踪 studentId 和 courseId 的查询中，数据库引擎可以决定使用哪个组合键.

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.

当我说追求"时，我的意思是在 on 子句 或 where 子句 条件中.

When I say "go after", I mean in the on clause or where clause condition.

如果没有这两个组合键(其中的第 1 列和第 2 列翻转)，那么在查找的列未最左侧 索引的查询中，您将不会受益键使用，并遭受缓慢的表扫描以返回数据.

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

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

重要的要点是，联结表有助于快速索引检索，以及对数据与以逗号分隔的数据(数组思维)塞进一列的理智管理，以及使用这样的所有痛苦构造.

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.

这篇关于如何在单个表中存储多个选项?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！