How do I use primary keys in SQL server?

75 pts.
Tags:
Primary keys
SQL
SQL Server
SQL tables
I have a table called course,and course_id is primary key in this table.
I created another table subject.I mapped these 2 tables by course_id as foreign key,I  created another table teacher,i want to map this with subject,
only subject_id to teacher_id mapping is required,how i mapp this,can i use course_id?  


Software/Hardware used:
software
ASKED: September 8, 2010  7:03 AM
UPDATED: September 9, 2010  10:06 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

No you can’t use the course to map to the teacher. You’ll need to do a many to many mapping as some teachers may be able to teach more than one subject. This would be a separate table, typically called SubjectTeacher or TeacherSubject, which has two columns subject_id and teacher_id. For each subject that a teacher can teach you put one row into this table.

—- kccrosser —
Classically, this problem would use a structure where:
Teacher teaches 0..n Classes
(Teacher can associate to 0..n Classes, Classes can associated with 0..n Teachers)
Classes have Subjects (Class can have only one Subject, but a Subject may associate to multiple Classes)

I would usually structure this with:
Table Subjects
Field SubjectID (primary key)
Field SubjectName
Field SubjectDescription

Table Classes
Field ClassID (primary key)
Field SubjectID (foreign key)
FIelds ClassSchedule (when, where, etc.)

Table Teachers
Field TeacherID (primary key)
Fields TeacherInfo (name, title, etc.)

Table TeacerClasses
Field TeacherID (foreign key)
Field ClassID (foreign key)
– Both columns combined form the primary key

If you were trying to build a class management package, you might want to extend the concept of “Classes” to include the concept of a “Class Definition” (title, description, prerequisites, etc.) and “Course Instance” (specific instance of a Class, beginning on a particular date/time and ending on another date/time). If you wanted to build a real system, you would actually wind up with the Course Instance connecting further to a collection of Course Sessions – each with an individual scheduled date/time/duration, since you need to handle irregular schedules, holidays, etc.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    It's reasonable that a given SUBJECT might relate to more than one COURSE. A TEACHER might relate to a single SUBJECT; and through the relationship to SUBJECT, multiple COURSEs might be reached. However, there is no way to know what keys might be needed as either primary keys or foreign until the rules of relationships are established. How many SUBJECTs can a TEACHER teach? Only one? More than one? Is every TEACHER required to have a related SUBJECT? Can a SUBJECT be taught by more than one TEACHER? How many COURSEs can a SUBJECT have? Can a COURSE be part of more than one SUBJECT? Are TEACHERs related to SUBJECTs or to COURSEs? The structure should be set up to match reality as far as possible. One aspect of reality is that it can change with very little notice ahead of time. That means that some flexibility might be useful. Today, each TEACHER might teach only a single COURSE. Tomorrow, a TEACHER might need to teach every COURSE within a SUBJECT. Next week, a TEACHER might need to teach multiple SUBJECT. Start by determining what actually happens today. Then start questioning today's rules to see how unbreakable they are. Tom
    125,585 pointsBadges:
    report
  • Shamu
    [...] 10. Shamu got some in-depth feedback from Mr. Denny, Kccrosser and TomLiotta: How do I use primary keys in SQL Server? [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following