Search code examples
sqldatabasedatabase-designentityerd

Database relationship issue

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?

Solution

  • 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.