How to design a database in the way that each class has one teacher and one TAs?

Question

I have 2 tables:

User(LoginID,Password,Email,UserType) --  Primary key(LoginID)
Class(CourseCode, Semester,Year,ClassTime,ProID) -- Primary key(CourseCode, Semester,Year) -- Foreign key(ProID)

UserType can be Teacher, Teaching Assistant(TA) or Student. Now I dont know where I can add TA attribute so that one class has one TA and one Teacher. I am thinking about putting TAID into User table as a foreign key, so that would be:

Class(CourseCode, Semester,Year,ClassTime,ProID,TAID)

But I'm not sure it is right or not. Because when I want to get teacher and TA's Name who teach a specific course, it does not work. I've tried the query below:

SELECT dbo.[User].Name
FROM  dbo.Class INNER JOIN
           dbo.[User] ON dbo.Class.ProID = dbo.[User].LoginID AND dbo.Class.TAID = dbo.[User].LoginID
WHERE CrsCode=@CrsCode and Semester=@Semester and Year=@Year

Question: Does anyone have any idea for this problem? Thanks in advance.

Edit: I wrote:

alter proc SelectFacultyNameByID
@CrsCode nvarchar(5),
@Semester nvarchar(20),
@Year int
as
SELECT 
u1.Name as Teacher,
u2.Name as Assistant
FROM
dbo.Class c
JOIN [User] u1
 ON c.ProID = u1.LoginID
JOIN [User] u2
ON c.TAID = u2.LoginID
WHERE 
c.CrsCode = @CrsCode 
AND c.Semester = @Semester 
AND c.Year = @Year

But it does not work!

score 1 · Accepted Answer · edited May 23 '17 at 12:21

1

Well, most straighforward solution would be for a Class to reference the User twice to implement a teacher and TA. So - two fields, I recommend TeacherID and AssistandID.

In the other direction, User should have a reference to Class to implement a class-membership.

However, I think your User table should be Student. Another thing is you should have another table for teachers, like Teacher, where you would stuff your teachers and MAYBE TA's. It depends if the fields that accompany your teacher/TA are the same. Otherwise there should be another table TeacherAssistant or Assistant.

At last, if you have to have some table where all your users in database reside and those are teachers, TA's and students, feel free to have Users table, but there should be a reference to it from Teachers, Assistants and Students.

To sum it up, you should probably have these tables to cover your requirements:

Class (ID, TeacherID, AssistantID, ...)
Teacher (ID, Name, ..., UserID)
Assistant (ID, Name, ..., UserID)
Student (ID, Name, ClassID, ..., UserID)
User (ID, Name, ...)

Another thing - in contemporary applications it is easier to have ID as PK. I believe your PK is a valid PK candidate, but it's easier (and is becomming a convention) to have autoincrement/identity/sequence ID as a PK in your table, and the real PK candidate as a unique key. For more info, see this discussion. SO, correct me if I'm wrong, but it really makes my ORM (Entity Framework) happy.

EDIT

In your current solution, if following...

SELECT u.Name
FROM
  dbo.Class c
  JOIN [User] u 
    ON c.ProID = u.LoginID
WHERE 
  c.CrsCode = @CrsCode 
  AND c.Semester = @Semester 
  AND c.Year = @Year

...means all teacher names of such classes in that year/semester, then...

SELECT u.Name
FROM
  dbo.Class c
  JOIN [User] u 
    ON c.ProID = u.LoginID
    AND c.TAID = u.LoginID
WHERE 
  c.CrsCode = @CrsCode 
  AND c.Semester = @Semester 
  AND c.Year = @Year

...means for such classes in that year/semester, find all classes that have same teacher AND teacher assistant and output their names.

You probably need:

SELECT 
  Teacher = u1.Name,
  Assistant = u2.Name
FROM
  dbo.Class c
  JOIN [User] u1
    ON c.ProID = u1.LoginID
  JOIN [User] u2
    ON c.TAID = u2.LoginID
WHERE 
  c.CrsCode = @CrsCode 
  AND c.Semester = @Semester 
  AND c.Year = @Year

edited May 23 '17 at 12:21

Community

1
1

answered Jul 24 '13 at 07:29

OzrenTkalcecKrznaric

5,535
4
34
57

Thank you for answering me. I have thought about that but my system is very simple. Users can login to the system. If they are students, they can see their grades. If they are Teachers/Teaching Assistants, they can add grades. If they are admins, they can manage login account and Class list. So, if I create 3 separate tables(Teacher, Assistant and Student), It would be complicated and not necessary. However, is it wrong if I just add AssistantID to the Class table as a foreign key of User table? – Tung Pham Jul 24 '13 at 07:57
It is not wrong, as a matter of fact it's completely valid. However, what if your student of class#1 is a TA of class#2? Will you get confused when you see your own query in a few months? If it looks right to you, it's right to me as well ;) – OzrenTkalcecKrznaric Jul 24 '13 at 08:01
Actually, to make it easy, I just assume that a TA cannot be a student, even thought it is not practical.:) – Tung Pham Jul 24 '13 at 08:07
it's kind of weird, when I executed it as a query, it worked. But I saved it as a procedure and executed it. it showed nothing.@@ – Tung Pham Jul 24 '13 at 09:27
I teach at a university. One of the professors took my class. How does that work in your schema? – Neil McGuigan Jul 24 '13 at 19:08
@NeilMcGuigan: whose schema, OP's or mine? In mine, you just update Class.TeacherID of a particular class, for any class you like. – OzrenTkalcecKrznaric Jul 24 '13 at 19:43
@NeilMcGuigan: If you have read all of the correspondence between OP and me (see comment 2 and 3) you would have understood why was the solution simplified. But it supports this case partially: your professor would stay the same User, just a new Student that references it should be added. – OzrenTkalcecKrznaric Jul 24 '13 at 20:08
@OzrenTkalčecKrznarić Do you know the solution that when I ran your query, it worked perfectly, but I wrote it as a procedure and ran it, it didnot work? – Tung Pham Jul 24 '13 at 20:54
@TungPham: I've noticed that, but I never saw that before. Are you sure your nvarchar(5) and nvarchar(20) parameters have proper types? – OzrenTkalcecKrznaric Jul 24 '13 at 21:25

How to design a database in the way that each class has one teacher and one TAs?

1 Answers1