2

I am having a issue in identifying the relationship I have the following scenario

  • A teacher can review many paper
  • A paper can be review by 4 teachers
  • A teacher can create a paper
  • A paper can have statuses (under review, approved)
  • A subject head who is also a teacher will select the teachers to review the paper.
  • A teacher can have many types (i.e. can be author of the paper, reviewer, subject head, etc)

I have identified the following tables:

  • Teacher
  • Paper
  • TeacherType

Now as teacher can be of many types and many teachers can have same type, the relation is many to many, this will be broken into one to many, so there will be a table TEACHER_TYPE (the junction table). I have to identify each roll of the teacher differently how will I use this, for e.g. a teacher authoring a paper and the same time reviewing paper by some other teacher. So will I use the primary keys in TEACHER_TYPE to record the reviewing and authoring process differently? or the same id in Teacher table with some different approach?

RThomas
  • 10,702
  • 2
  • 48
  • 61
mdanishs
  • 1,996
  • 8
  • 24
  • 50
  • Information unclear in your specification: does a paper have one status at a time, or several at a time; is historical tracking of paper statuses required; and are all subject heads also teachers. – Larry Lustig Oct 18 '12 at 19:25

2 Answers2

0

At first glance, one thing that seems to be missing is a link or association table to represent the many to many relationship between teachers and papers. The table could look something like...

review         <-- this name can be descriptive of the purpose (review)
----------         but is also often just the name of the association
fk_paper_id    <-- foreign key to paper
fk_teacher_id  <-- foreign key to teacher performing review
review_status  <-- one or more columns indicating the status of this review

The teacher types are likely just attributes of the teacher and/or the paper.

Since there is a hierarchy between teachers (subject head, etc) that could also be represented as an attribute of the teacher, and not a separate type. The table can be self referencing... for example, an employee table....

employee
--------------
pk_employee_id
name, etc
fk_manager_id     <-- foreign key referencing back to the managing employee
Tevo D
  • 3,351
  • 21
  • 28
0

I would make a 4th table PAPER_TEACHER, this would make you question much easier.

TABLE TEACHER T_ID TABLE PAPER P_ID TABLE TEACHER_TYPE T_ID and TT_Type TABLE PAPER_TEACHER T_ID, P_ID, AND PT_Status

SELECT * FROM PAPER t1 INNER JOIN PAPER_TEACHER t2 on t1.P_ID = t2.P_ID AND t2.PT_status = "under review" INNER JOIN TEACHER t3 on t2.T_ID = t3.T_ID INNER JOIN TEACHRE_TYPE t4 on t3.T_ID = = t4.T_ID AND t4.TT_Type = "reviewer"

Where you ask for the PP_status, you can use all kind of things.

Jacob Buter
  • 26
  • 1