Should I split this table into two?

Question

I am trying to wrap my head around database normalization. This is my first time trying to create a working database so please forgive me for my ignorance. I am trying to create an automated grad Check system for a class project. The following table keeps track of all options for a major for a set number of catalog years. The table is as follows

PID    Title    Dept    Courses    Must_have

Some options give the user a choice of a set number of classes out of the total listed (hence the Must_have attribute). A completed row would look like this:

PID    Title    Dept    Courses    Must_have
--------------------------------------------
 1      bis     acct    201|202      NULL

Title is the name of the option that can come with the major. If bis (business information systems) had a choice of classes, one row would have a number in the Must_have for only one row.

My question is should I split this table into two different tables? I know the way I currently have it seems somewhat... well wrong. Any help would be greatly appreciated.

Answer 1

I would split it into three tables. The first would be majors and would contain PID, Title, Dept, the second would be courses, containing the course ID, course name and any other information, and the last would be a mapping between majors and courses (perhaps named courses_majors). The courses_majors table would contain the ID of the major, the ID of a course and a flag to show whether or not it is required by that major.

(This is assuming that one course could be used in multiple majors)

Answer 2

I would break dept into a separate table and associate it with a numeric ID. Then break your "courses" field into a "join table". Something like this:

majors

Id   Title       DepartmentID

major_courses

Id   MajorId     CourseId      MustHave

departments

Id   Title

So that, you may have a major like:

1    bis          1

a major_course like:

1    1           201            0
1    1           202            0
1    1           203            1 -- must have 203

then departments like:

1    bis

So now, to get a list of courses for the first major you can do this:

SELECT major_courses.CourseId, major_courses.MustHave, departments.Title 
FROM majors 
RIGHT JOIN major_courses ON major_courses.CourseId = majors.Id 
INNER JOIN departments ON departments.Id = majors.DepartmendID 
WHERE major.id = 1