storing multiple values in one field

I want users to be able to select their majors.

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

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.

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.

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.

So I have 2 tables

Organizations

ID int

name varchar(255)

majors_accepted blob

Students

ID int

name varchar(255)

majors blob

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

I would do it like this:

1. create a majors table - has a majorID and a majorName
2. create an association table between students and majors that has a studentID and a majorID.
3. create an association table between organizations and majors that has an orgID and a 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.

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

Don't try to store the major lists as blobs in a single column, use separate association tables for that:

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

You might want to index each column in primary keys individually as well but indexing depends (as usual) on what sort of queries you're likely to be using.

Then you can use standard SQL queries to check that the majors match up. For example, to find all students with a major_id of 1:

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

or to find all students that can be in organization 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)

The relationship between students and majors is many-to-many. The best design for many-to-many is an association table, as other responders have already said. The association table will refer back to the student table and to a majors table, with data like the name of the major.

Storing multiple values in one field, with a separator like comma, is bad design. It violates First Normal Form. When you violate First Normal Form, you can no longer do keyed lookup to all data.

As an example, you have to do a full table scan to find all students with a given major. This could result in thousands of disk ios, instead of less than a hundred disk ios to do a three way join. That's ten times slower.

When you plan for keyed lookup to the association table, pay careful attention to good index design. A good query optimizer along with good index design can get the best speed possible out of your joins. Fortunately you can go back and change the index design without unloading and reloading the table.