Begginner needs advice

[nunos]

Hi. I am complete beginner in databases, so please bear with me stupid questions.

I am just doing for fun a simple exercise. A faculty have students and each student have different subjects.

My question is, how to store the different subjects in the db?

I could make up some elaborate system like a field subjects and insert the different subjects id's this student has, separating them with "|"s.

Even though my knowledge in db is terrible limited, this seems to me like a very programming oriented approach. Is this the right way to go about it? If not, what is?

Thanks.

[apegram]

Off the top of my head, I can come up with several database entities to represent a college environment. This only took a couple of minutes to layout, so it can be expanded further and obviously not all fields are represented for each table.

Classes
CourseID
CourseTitle
CourseDescription

Teachers
TeacherID
FirstName
LastName
HireDate
Tenured
TenureDate

Buildings
BuildingID
BuildingName

Classroooms
RoomID
BuildingID
RoomNumber
RoomFloor
RoomOccupancy

CourseSections
SectionID
CourseID
TeacherID
RoomID
MeetingDays
MeetingTime
MaxStudents

Students
StudentID
FirstName
LastName
Gender
Ethnicity

CourseSectionRoster
SectionID
StudentID

[nunos]

Thanks for your reply. However, I haven't been able to solve my question with it.

Repharsing, how can I store the subjects a student will have? Is it a good idea to have subject_id1 and subject_id 2 separated with "|" ?

[apegram]

You wouldn't want to do that, no.

You may want to look into a database concept called normalization. The quick, quick summary is that you cut down on data duplication by creating meaningful entities to describe your data and then connecting that data with other data using only what's necessary to make that link.

Look at my hypothetical table structure above. In it, you've got classes as a table. It will contain whatever is relevant the class itself. You'll notice what is not in that table is the teacher or the time it meets. Why? Because, for example, Computer Science 101 is going to be a popular class. So popular, in fact, that there will be multiple sections of it, running at different times in different locations, taught by a whole bunch of different people. The class table itself cannot and should not contain all of that data. It should just describe the unique specifics about the class itself, and let other tables describe all the places where the class takes place and who may be teaching.

The teachers table's only job is to describe teachers. Not what they teach, as teachers can teach many different subjects. Same for buildings, and the rooms within buildings. Many different classes will be in those rooms. The students table should also not be concerned with what classes a student may be taking, because there will be many. It should only describe the student.

That's where the CourseSections and SectionRoster tables come into play. The CourseSections table is the one that links all of the relevant information about a specific instance of a class. Meaning, the dreaded Monday-Wednesday-Friday 8:00 AM section of CSC 101 is described here. We match it to the classes table via the ClassID field, the teachers table with the TeacherID field, and Rooms table with the RoomID field, and then we can describe the meeting schedule and time and how man openings the section may have.

Students that register for the section will be added to the SectionRoster table. It will contain rows that simply map a Student to a Section with the StudentID and SectionID fields, respectively.