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

Edit | Attach | Watch | Print version | History: r2 < r1 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r2 - 2006-03-03 - rstone
 
This site is powered by the TWiki collaboration platform Powered by PerlCopyright © 2008-2019 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback