表格结构-将一名学生与多个班级ID关联起来 [英] Table structure - Link one student with multiple classes id
问题描述
我目前遇到我设计的表的问题（请参阅下文）。一个学生可以属于多个班级。稍后，我开始将多个值存储在一列中，以了解这是一个很大的不。例如， class_id 在存储用逗号分隔的值时会遇到类型丢失和变成字符串的问题。我读过一些文章，它表示要为班级制作两个表格，为学生制作另一个表格，但不确定如何插入将学生与多个班级基本连接的数据。最好的方法是什么？
当前方法：
- ----------- ---------- ------------ -------------
student_fname student_id class_name class_id
------------ ---------- ------------ -------- -----
james Vre94b3JpXO数学，科学5697,5768
jim JzqQ2zRVNm1艺术，音乐7604,7528
jenny xgqv9P42eYL物理版6422
kyle QLNM0Wbyqk0计算机，jrotoc 6315， 8797
kimberly P2egAddWN0Q烹饪艺术8069
kayla EGNDjWAreAy科学，艺术5768,7604
noah bPeOyMMONGr数学，音乐5697,7528
nataly 9Op53GGmqk5 jrotc 8797
建议的方法：
- ----------- ----------
类名class ID
------------ ---- ------
数学5697
科学5768
计算机6315
实体编辑6422
音乐7528
艺术作品7604
jrotc 8797
烹饪艺术8069
------------ ----------
学生fname学生证
------------ ----------
james Vre94b3JpXO
jim JzqQ2zRVNm1
jenny xgqv9P42eYL
kyle QLNM0Wbyqk0
kimberly P2egAddWN0Q
kayla EGNDjWAreAy
诺亚bPeOyMMONGr
nataly 9Op53GGmqk5
解决方案是引入第三个表，该表用作两个域表之间的连接。该表将包含对其他表的主键的外键引用（以及该关系的任何特定数据，例如注册日期等）。
类表：
类名class_id（主键）
------------ ----------
数学5697
科学5768
学生表：
学生fname student_id（主键）
------------ ----- -----
james Vre94b3JpXO
jim JzqQ2zRVNm1
报名表：
student_id（fk to stud。）class_id（fk to class）
- ----------- ----------
Vre94b3JpXO 5697
JzqQ2zRVNm1 5697
JzqQ2zRVNm1 5768
在上一张表中，您将使用复合主键或复合主键来确保唯一性。 （不同之处在于，复合pk也将包括其他列-例如日期或学期，例如，这将允许学生在不同场合参加同一堂课。）
要查询数据，您可以将键加入表中：
选择*
来自学生的
在s.student_id = e.student_id
上注册e在b.c.class_id = e.class_id
上c类加入课程c.class_id = e.class_id
如果您想了解更多有关此的信息，请在关系数据库 <的上下文中搜索数据库规范化和规范形式 / p>
这是一个小型演示。
I am currently facing an issue with table that I designed(please see below). One student can belong to multiple classes. I started storing the multiple values in one column later to learn this is a big no no. For example, class_id faces issues with loosing in type and becoming string when storing values delimited by a comma. I have read to make articles and it indicates to make two tables one for class and another for students but unsure how to insert data that will essentially link a student to multiple classes. What would be the best approach?
Current approach:
------------ ---------- ------------ -------------
student_fname student_id class_name class_id
------------ ---------- ------------ -------------
james Vre94b3JpXO math,science 5697,5768
jim JzqQ2zRVNm1 art, music 7604,7528
jenny xgqv9P42eYL physical-ed 6422
kyle QLNM0Wbyqk0 computer,jrotoc 6315,8797
kimberly P2egAddWN0Q culinary-arts 8069
kayla EGNDjWAreAy science, art 5768,7604
noah bPeOyMMONGr math, music 5697,7528
nataly 9Op53GGmqk5 jrotc 8797
Suggested approach:
------------ ----------
class name class id
------------ ----------
math 5697
science 5768
computer 6315
physical-ed 6422
music 7528
art 7604
jrotc 8797
culinary-arts 8069
------------ ----------
student fname student id
------------ ----------
james Vre94b3JpXO
jim JzqQ2zRVNm1
jenny xgqv9P42eYL
kyle QLNM0Wbyqk0
kimberly P2egAddWN0Q
kayla EGNDjWAreAy
noah bPeOyMMONGr
nataly 9Op53GGmqk5
The solution is to introduce a third table that serves as a junction between the two domain tables. This table would hold foreign key references to the primary keys of the other tables (plus any data specific to the relation, such as enrollment date etc).
Class table:
class name class_id (primary key)
------------ ----------
math 5697
science 5768
Student table:
student fname student_id (primary key)
------------ ----------
james Vre94b3JpXO
jim JzqQ2zRVNm1
Enrollment table:
student_id (fk to stud.) class_id (fk to class)
------------ ----------
Vre94b3JpXO 5697
JzqQ2zRVNm1 5697
JzqQ2zRVNm1 5768
In the last table you would use a compound or composite primary key to ensure uniqueness. (The difference is that a composite pk would include other columns as well - like date or term, which would allow a student to take the same class on different occasions for example).
To query data you would join the tables on the keys:
select *
from student s
join enrollment e on s.student_id = e.student_id
join class c on c.class_id = e.class_id
If you want to learn more about this, search for database normalization and normal forms in the context of relational databases
And here's a small demo.
这篇关于表格结构-将一名学生与多个班级ID关联起来的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
