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

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 teacher, teaching assistant(ta) or student. dont know can add ta attribute 1 class has 1 ta , 1 teacher. thinking putting taid user table foreign key, be:

class(coursecode, semester,year,classtime,proid,taid) 

but i'm not sure right or not. because when want teacher , ta's name teach specific course, not work. i've tried query below:

select dbo.[user].name  dbo.class inner join            dbo.[user] on dbo.class.proid = dbo.[user].loginid , dbo.class.taid = dbo.[user].loginid crscode=@crscode , semester=@semester , year=@year  

question: have idea problem? in advance.

edit: wrote:

alter proc selectfacultynamebyid @crscode nvarchar(5), @semester nvarchar(20), @year int select  u1.name teacher, u2.name assistant dbo.class c join [user] u1  on c.proid = u1.loginid join [user] u2 on c.taid = u2.loginid  c.crscode = @crscode  , c.semester = @semester  , c.year = @year 

but not work!

well, straighforward solution class reference user twice implement teacher , ta. - 2 fields, recommend teacherid , assistandid.

in other direction, user should have reference class implement class-membership.

however, think user table should student. thing should have table teachers, teacher, stuff teachers , maybe ta's. depends if fields accompany teacher/ta same. otherwise there should table teacherassistant or assistant.

at last, if have have table users in database reside , teachers, ta's , students, feel free have users table, there should reference teachers, assistants , students.

to sum up, should have these tables cover 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 easier have id pk. believe pk valid pk candidate, it's easier (and becomming convention) have autoincrement/identity/sequence id pk in table, , real pk candidate unique key. more info, see discussion. so, correct me if i'm wrong, makes orm (entity framework) happy.

edit

in current solution, if following...

select u.name   dbo.class c   join [user] u      on c.proid = u.loginid    c.crscode = @crscode    , c.semester = @semester    , c.year = @year  

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

select u.name   dbo.class c   join [user] u      on c.proid = u.loginid     , c.taid = u.loginid    c.crscode = @crscode    , c.semester = @semester    , c.year = @year  

...means such classes in that year/semester, find classes have same teacher , teacher assistant , output names.

you need:

select    teacher = u1.name,   assistant = u2.name   dbo.class c   join [user] u1     on c.proid = u1.loginid   join [user] u2     on c.taid = u2.loginid    c.crscode = @crscode    , c.semester = @semester    , c.year = @year