Data Model For the TA Assignment app (app_grad_tas database)

• activeDates
  • Contains the dates that the system goes active and inactive
  • The system will only accept changes to preferences when the system is active
  • If activeDate < inactiveDate
    • then the system is active if active < now < inactive
  • If inactive < active
    • then the system is active if now < inactive OR now > active
  • Only affects Students, not Admins
  • Should only contain one row with activeDatesId = 1
  • Fields:
    • activeDatesId - dummy primary key value
    • activeDate - Date that the system will go active(ie accept changes from students)
    • inactiveDate - Date that the system will go inactive(ie stop accepting changes from students
  • Primary Key: activeDatesId

• hireMethod
  • Code table mapping hireMethod strings to hireMethodIds(integer values)
  • The hire method of a position indicates the method used to choose applicants for the position
  • Interview positions go through a full job interview process before student(s) are hired
  • Assigned positions have students assigned to them by admins without any interview process
    • Notation note: A student can be assigned to any type of job; not just Assigned positions
  • Students can only indicate their preferences for Assigned positions; the interview process is seperate from the Grad TA Assignments application
  • Fields
    • hireMethodId - generated primary key
    • description - description of the hire method(ie Assigned or Interview)
  • Primary Key: hireMethodId

• position
  • Represents positions(assigned and interview)
  • Fields
    • jobId - synthetic primary key
    • effective - date that the position becomes visible in the system
    • expires - date after which the position is no longer available(because a course is no longer offered, for instance)
    • name - the name of the position
    • comment - option description of the position
    • hireMethodId - indicates the method used to hire students(see hireMethod table)
  • Primary Key: jobId

• positionAssignment
  • Represents the assignment of a student to a position in a particular term
  • For a student to have an assignment in a particular term, they must have an entry in studentTerm for that term (not enforced by DB)
  • For a position to be assigned in a particular term, it must have an entry in positionTerm for that term (not enforced by DB)
  • Fields
    • jobId - foreign key representing the position being assigned
    • term - represents the term in which the assignment is made(using an integer code like 1061 for W06)
    • student - foreign key containing the userid of the student being assigned
    • units - the number of units the student will recieve credit for
  • Primary Key: (jobId, term, student)

• positionRanking
  • represents a student's preferences for a given job(1 = highest preference)
  • rankings must be in order -- a ranking can't be skipped
    • eg 1, 2, 3, 4 is allowed; 1, 2, 4, 6 is not
  • ties are not allowed
  • Fields
    • jobId - foreign key representing the position being ranked
    • student - foreign key containing the userid of the student doing the ranking
    • term - the term for which this ranking is valid
    • ranking - the ranking the student gives the job
  • Primary Key: (jobId, student, term)

• positionTerm
  • Indicates that a position is available in the specified term
  • A position cannot be assigned or chosen for preferences in a given term unless there is an entry for that position and term
    • Note that we plan for terms one term ahead of time
    • For example, if it's currently term 1061, we are planning for term 1065, so the position cannot be chosen as a preference unless there is an entry under 1065
  • Fields
    • jobId - foreign key representing the position in question
    • term - represents the term being planned for(using the integer code, like 1065)
    • units - the number of units that we plan to offer
    • supervisor - Optional field describing the supervisor for the given position. Nullable. Must contain a valid UW userid(enforced by app, not by DB)
  • Primary Key: (jobId, term)

• studentTerm
  • Indicates that the given student is expected to work as a TA in the given term
  • The tasOffered field indicates the number of units we plan to offer to the student
    • if it's 0, we don't intend to give them any units this term but we still expect to give them units in future terms
  • Fields
    • student - foreign key containing the userid of the student
    • term - represents the term we're planning for(using an integer term code)
    • tasMinRequested - the minimum number of units requested by this student for this term (set by student)
    • tasMaxRequested - the maximum number of units requested by this student for this term (set by student)
    • tasOffered - the number of units the admin plans to offer the student (set by admin)
  • Primary Key: (student, term)

• term
  • Contains the tentative and final dates for a given term
  • The tentative date is the date after which tentative assignments for the term are visible to students
  • The final date is the date after which assignments should be final
    • Any change to assignments after this date requires notifying the student by email
  • Fields
    • term - the numeric code of the term being planned for
    • tentativeDate - The tentative date. Must occur in term or the term before term, and must come before the final date(neither constraint is enforced by the DB)
    • finalDate - The final date. Must occur in term or the term before term, and must come after the tentative date(neither constraint is enforced by the DB)
  • Primary Key: term

• userGroups
  • mapping of users to groups(for restricting/granting access to certain parts of the application)
  • currently there is only one group -- app_grad_tas_admin, the group of administrators of the application
  • Fields
    • groupName - name of the group the user is a part of
    • userid - the user who is granted access to the given group
  • Primary Key: (groupName, userid)

-- Main.rstone - 22 Feb 2006