問題描述

我想設計一個用于結果計算的應用程序.

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模板網！