在一个字段中存储多个值

Question

我希望用户能够选择他们的专业。

例如，A可以选择计算机科学、数学和历史作为他的专业。用户可以选择任意数量的专业。

我的数据库中有一个组织列表，这些组织只接受特定专业的学生。例如，A组织只接受计算机科学和数学专业的学生。组织可以选择任意数量的专业。

我想让学生与适合他们专业的组织相匹配。例如，我想在数据库中搜索接受一个或多个Person's A专业的组织，这些专业包括计算机科学、数学和历史。接受全部或大部分个人A专业的组织将排在第一位。因此，如果组织B接受个人A的所有三个专业，但组织A只接受个人A的两个专业，则组织B将排在第一位。

如何在mysql数据库中存储组织录取的专业？如何存储学生的专业，以便在学生信息和组织信息之间进行有效匹配？

我正在考虑将组织接受的所有专业作为序列化的值存储在数据库中。

所以我有两张桌子

组织

ID整型

名称varchar(255)

majors_accepted blob

学生

ID整型

名称varchar(255)

主要blob

我可以将组织接受的专业作为序列化的值存储在majors_accepted blob中。该领域可能有超过1个专业。

或者，我可以将学生正在考虑的专业作为序列化的值存储在学生表的专业字段中。该领域可能有超过1个专业。然后我想我可以遍历organizations表中的所有行，并将每个majors_accepted字段与学生数据进行比较。但这似乎很低效...

Answer 1

我会这样做：

1. 创建专业表-具有majorID和
   1. 学生和专业之间的关联表，具有studentID和majorID组织和专业之间的关联表，具有orgID和majorID。

在一个字段中存储多个值并不是一个好的数据库设计，就像您试图对blobs所做的那样，所以像这样分解它，您就可以通过连接表来执行所有必要的查询，以确定学生可以加入哪些组织。

假设您正在寻找某个特定学生可以加入的组织(我们将该学生的studentID设为1)：

SELECT Students.ID,
       Organizations.name
FROM Students 
INNER JOIN StudentsMajors ON Students.ID = StudentsMajors.studentID
INNER JOIN OrganizationsMajors ON StudentsMajors.majorID = OrganizationsMajors.majorID
INNER JOIN Oranizations ON OrganizationsMajors.orgID = Organizations.ID
WHERE Students.ID = 1

Answer 2

不要试图将主要列表作为blobs存储在单个列中，请使用单独的关联表：

create table organization_majors (
    organization_id int not null,
    major_id        int not null,
    primary key (organization_id, major_id)
);
create table student_majors (
    student_id int not null,
    major_id   int not null,
    primary key (student_id, major_id)
);

您可能还希望在主键中分别为每一列建立索引，但是索引(通常)取决于您可能要使用的查询类型。

然后，您可以使用标准SQL查询来检查专业是否匹配。例如，要查找major_id为1的所有学生：

select s.id, s.name
from students s join student_majors m on s.id = m.student_id
where m.major_id = 1

或者查找可以在组织11中的所有学生：

select s.id, s.name
from students s
join student_majors sm on s.id = sm.student_id
join organization_majors om on sm.major_id = om.major_id
where om.organization_id = 11
group by s.id, s.name
having count(*) = (select count(*) from organization_majors where organization_id = 11)

Answer 3

学生和专业之间是多对多的关系。正如其他响应者已经说过的那样，多对多的最佳设计是关联表。关联表将引用学生表和专业表，其中包含专业名称等数据。

在一个字段中存储多个值，使用逗号之类的分隔符，这是一个糟糕的设计。它违反了第一范式。当您违反First Normal Form时，您不能再对所有数据执行键控查找。

例如，您必须执行全表扫描，以查找具有给定专业的所有学生。这可能会导致数千个磁盘to，而不是执行三向联接的不到100个磁盘to。那就慢了十倍。

当您计划对关联表进行键控查找时，请仔细注意良好的索引设计。一个好的查询优化器和一个好的索引设计可以使您的连接获得最佳的速度。幸运的是，您可以返回并更改索引设计，而无需卸载和重载该表。