在一个字段中存储多个值 [英] storing multiple values in one field

问题描述

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

I want users to be able to select their majors.

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

For example, person A could select computer science, mathematics, and history as his majors. Users can select any number of majors.

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

I have a list of organizations in my database that would only accept students if they are of a particular major. For example, Organization A only accepts computer science and mathematics majors. Organizations can select any number of majors.

我想将学生与适合自己专业的组织相匹配。例如，我想在数据库中搜索接受一个或多个人的A专业，即计算机科学，数学和历史的组织。接受个人A专业的全部或大部分的组织将首先列出。所以如果组织B接受人A专业的三个人，而组织A只接受人的A专业的两个，组织B将首先列出。

I want to match students to the organizations that fit their majors. For example, I want to search the database for organizations that accept one or more of Person's A majors, which are computer science, mathematics, and history. Organizations that accept all or most of Person's A majors would be listed first. So if Organization B accepts all three of Person's A majors but Organization A only accepts two of Person's A majors, Organization B would be listed first.

如何将组织接受的专业存储在mysql数据库中？我如何存储学生的专业，以便学生信息和组织信息之间的有效匹配？

How can I store the majors that the organizations accept in the mysql database? How can I store students' majors to allow for efficient matching between student information and organization information?

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

I was considering storing all the majors that organizations accept as a serialized values in the database.

所以我有2个表

组织

ID int

name varchar（255）

majors_accepted blob

Organizations
ID int
name varchar(255)
majors_accepted blob

学生

ID int

名称varchar（255）

majors blob

Students
ID int
name varchar(255)
majors blob

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

I could store the majors that organizations accept as serialized values in the majors_accepted blob. There could be more than 1 major in that field.

或者我可以将学生考虑的专业存储在学生表中的专业领域中作为序列化值。在这个领域可能有一个以上的专业。那么我想我可以去组织表中的所有行，并将每个主题的字段和学生的数据进行比较。但这似乎效率不高...

Or I could store the majors that students are considering as serialized values in the majors field in the Students table. There could be more than 1 major in that field. Then I guess I could go over all the rows in the organizations table and compare each majors_accepted field with the students data. But that seems inefficient...

推荐答案

我会这样做：

1. 创建一个专业表 - 有一个majorID和一个majorName


2. 创建一个学生和专业有一个studentID和majorID的专业关联表。 / li>
   
3. 在具有orgID和majorID的组织和专业之间创建一个关联表。

不太好的数据库设计，可以在一个字段中存储一个以上的值，就像你想要做的那样，你可以像这样打破它，你可以做所有的查询，以确定学生可以通过哪些组织加入加入表。

It's not good database design to store more than one value in a field like what you're trying to do with your blobs, so break it out like this, and you can do all the queries necessary to determine which organizations a student can join by just joining tables.

假设您正在寻找一名特定学生可以加入的组织（我们会说studentID为这个学生是1）：

Let's say you're looking for the organizations that one particular student can join (we'll say studentID for this student is 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

这篇关于在一个字段中存储多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！