iPeer Database Schema

Warning

This content may be partially inaccurate, but has been kept for posterity. It was copied from iPeer's legacy Trac Wiki software (formerly hosted at https://ipeer.ctlt.ubc.ca/ which has since shut down).

courses

Contains all the courses in the iPeer instance.

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
course	string	the course number, ex: APSC 123
title	string	a very short course description, ex: Academic Writing
homepage	string	a link to the course. Must start with !http:// or !https://
self_enroll	emun(on, off)	not fully implemented. The UI exists to modify this value, but no effect linked to it can be found in code
password	string	an MD5 hash of the user password.
record_status	enum(A,I)	either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_id	integer	the ID of the user who created this course.	users
created	date	a date entry for this entry's creation.
updater_id	integer	the last user's ID who changed this record.	users
modified	date	the last time of modification.
instructor_id	integer	unused, as far as I can tell. The course-instructor is a many-to-many relationship is handled by the user_courses table.

evaluation_mixevals

A student's evaluations are put here, for the mixed evaluation type. Each evaluator-evaluate pair is recorded here.

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
evaluator	integer	The user submitting the evaluation. They are evaluating other users.	users
evaluatee	integer	The user being evaluated.	users
score	floating	The total given by the evaluator to the evaluatee. (?)
comment_release	integer	Are this entry's comments release to be viewed by other users?
grade_release	integer	Are this entry's grades release to be viewed by other users?
grp_event_id	integer	Points to the group_events entry this entry is a part of.	group_events
event_id	integer	Points to the event that the above group_event entry is part of.	events
record_status	enum(A,I)	either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_id	integer	the ID of the user who created this course.	users
created	date	a date entry for this entry's creation.
updater_id	integer	the last user's ID who changed this record.	users
modified	date	the last time of modification.

evaluation_mixeval_details

It seems that for each answer to a mixed evaluation question, and entry in this table is generated.

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
evaluation_mixeval_id	integer	The ID of the evaluation_mixeval this question answer belongs too.	evaluation_mixeval table.
question_number	integer	The question being answered
question_comment	text	A textual answer to the question, if present. NULL otherwise.
selected_lom	integer	Doesn't seem to be used. All entries set at 0. (?)
grade	floating	A grade that the user gave when answering the question. Set to 0.0 if a textual question.
record_status	enum(A,I)	either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_id	integer	the ID of the user who created this course.	users
created	date	a date entry for this entry's creation.
updater_id	integer	the last user's ID who changed this record.	users
modified	date	the last time of modification.

evaluation_rubrics

Holds all the submissions of evaluatee-evaluator pairs.

Column	Type	Description	Foreign Key to table...
id	integer	Primary_KeyNot used in this table (All 0's) (?)
evaluator	integer	The user submitting the evaluation. They are evaluating other users.	users
evaluatee	integer	The user being evaluated.	users
general_comment	text	A general comment about the evaluatee from the evaluator
score	floating	The total score that the evaluatee received from the evaluator.
comment_release	integer	Are this entry's comments release to be viewed by other users?
grade_release	integer	Are this entry's grades release to be viewed by other users?
grp_event_id	integer	Points to the group_events entry this entry is a part of.	group_events
event_id	integer	Points to the event that the above group_event entry is part of.	events
record_status	enum(A,I)	either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_id	integer	the ID of the user who created this course.	users
created	date	a date entry for this entry's creation.
updater_id	integer	the last user's ID who changed this record.	users
modified	date	the last time of modification.

evaluation_rubrics_details

It seems that for each answer to a mixed evaluation question, and entry in this table is generated.

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
evaluation_rubric_id	integer	The ID of the evaluation_rubric this question answer belongs too.	*evaluation_rubrics *
criteria_number	integer	The criteria number this entry belongs to. Not a foreign key ( :-/ Ugh... )
criteria_comment	text	A textual comment about the evaluatee according to the above creteria.
selected_lom	integer	The level-of-mastery the evaluator selected for the evaluatee.
grade	floating	The grade the above LOM gives the evaluatee.
record_status	enum(A,I)	either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_id	integer	the ID of the user who created this course. Not used in this table (All 0's) (?)	users
created	date	a date entry for this entry's creation.
updater_id	integer	the last user's ID who changed this record. Not used in this table (All NULLS's)	users
modified	date	the last time of modification.

evaluation_simples

Every time an evaluator saves their progress in simple evaluation for an evaluatee, an entry is saved into this table.

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
evaluator	integer	The user submitting the evaluation. They are evaluating other users.	users
evaluatee	integer	The user being evaluated.	users
score	integer	The score this evaluator gave the evaluatee.
eval_comment	text	A comment about the evaluatee by the evaluator.
release_status	integer	Unsure (?) (vs grade_release)
grp_event_id	integer	Points to the group_events entry this entry is a part of.	group_events
event_id	integer	Points to the event that the above group_event entry is part of.	events
date_submitted	date	The date the evaluation was submitted. (Does this just copy the info in evaluation_submissions ?)
grade_release	integer	Unsure (?) (vs grade_release)
record_status	enum(A,I)	either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_id	integer	the ID of the user who created this course.	users
created	date	a date entry for this entry's creation.
updater_id	integer	the last user's ID who changed this record.	users
modified	date	the last time of modification.

evaluation_submissions

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
event_id	integer	Points to the event that the above group_event entry is part of.	events
grp_event_id	integer	Points to the group_events entry this entry is a part of.	group_events
submitter_id	integer	The evaluator's user ID	users
submitted	integer	Apparently determined whether the evaluation was submitted or not. I don't see any 0's in the prod2009 database though. Might have something to do with re-releases. Not sure (?).
date_submitted	date	The date the evaluator submitted this evaluation.
record_status	enum(A,I)	either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_id	integer	the ID of the user who created this record.	users
created	date	a date entry for this entry's creation.
updater_id	integer	the last user's ID who changed this record.	users
modified	date	the last time of modification.

event_template_types

I think this holds the the entries for the rubric types in iPeer. However, the code often assumes it knows the meanings of evaluation type id's (1,2,3,4), and hard-codes those into the code, sometimes ignoring this table.

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
table_name	string	The name of the table that holds these types of evaluations.
model_name	string	The name of the model that connects the above table to cake.
display_for_selection	integer	A boolean integer. Tells if this type of evaluations should be available for selection when an instructor creates an event
record_status	enum(A,I)	either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_id	integer	the ID of the user who created this record.	users
created	date	a date entry for this entry's creation.
updater_id	integer	the last user's ID who changed this record.	users
modified	date	the last time of modification

events

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
title	string	The title of this event;
course_id	integer	The id of the course that this event will appear in	courses
description	text	A textual description of this event.
event_template_type_id	integer	The type of the evaluation.	event_template_types
template_id	integer	The ID of in the entry of this evaluation inside the particular evaluation type's table	simple_evaluations or rubrics or surveys or mixevals
self_eval	boolean integer	Does the user evaluate themselves as well?
com_req'	boolean integer	Are the comments required?
due_date	date	The due date for the event.
release_date_begin	date	The when the event will open - start showing up on the student's display.
release_date_end	date	The when the event will close - stop showing up on the student's display, even if it was completed.
record_status	enum(A,I)	either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_id	integer	the ID of the user who created this record.	users
created	date	a date entry for this entry's creation.
updater_id	integer	the last user's ID who changed this record.	users
modified	date	the last time of modification

groups

Holds the members for each group in a class.

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
group_num	integer	The Group number, no the ID. Not sure why we have it.
course_id	integer	The ID of the course this group belongs to	courses
record_status	enum(A,I)	either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_id	integer	the ID of the user who created this record.	users
created	date	a date entry for this entry's creation.
updater_id	integer	the last user's ID who changed this record.	users
modified	date	the last time of modification

groups_members

Connects groups to group members (many to many relationship):

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
group_id	integer	The ID of the group	groups
user_id	integer	The user ID of a group member	users

That's it :-)

group_events

Connects groups to events, holding other data about the group submission, etc. (many to many relationship).
I think that the extra columns are just to cache other data from the table, and display it to the user.

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
group_id	integer	The ID of the group	groups
event_id	integer	The ID of the event	event
marked	string	One of not reviewed,to review, reviewed. Determined whether the instructor reviewed the comments for any offensive contents or the like. As I understand it, "Not reviewed" means not all submissions are yet complete from group members. "To review" implies that all submissions came in, but are not yet reviewed. "Review" means all submissions were turned in and reviewed by instructor.
grade	integer	I have no idea... NULL in all case I've seen so far. Unused (?)
grade_release_status	string	Can the users see the grades their group members gave them, or not? Some, None and All are possible.
grade_release_status	string	Can the users see the comments their group members gave them, or not? Just like above, Some, None and All are possible.
record_status	enum(A,I)	either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_id	integer	the ID of the user who created tthis record.	users
created	date	a date entry for this entry's creation.
updater_id	integer	the last user's ID who changed this record.	users
modified	date	the last time of modification

mixevals

Contains a list of Mixed Evaluation (Templates - referenced by evaluation events)

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
name	string	Title of the evaluation
total_marks	integer	The total amount of point possible in this evaluation per member.
zero_mark	enum(on,off)	Does the lowerst LOM count as a zero score?
total_question	integer	The total number of questions - both Lickert and Textual Answer.
lickert_question_max	integer	The number of Lickert(Rubric-type) questions.
scale_max	integer	The number of LOMs for Lickert questions.
prefill_question_max	integer	The number of Textual Questions to Add.
availability	enum(public,private)	Determines whether this rubric can be used in an event by users other than the creator.
creator_id	integer	the ID of the user who created this record.	users
created	date	a date entry for this entry's creation.
updater_id	integer	the last user's ID who changed this record.	users
modified	date	the last time of modification

mixevals_questions_descs

Holds the LOM descriptions of Lickert (Rubric type) mixed evaluation question.

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
mixeval_id	integer	The ID of the mixed evaluation being described.	mixevals
question_num	integer	The question this LOM description is for.
scale_level	integer	How many points this LOM is worth.
descriptor	string	The description of this LOM.

mixevals_questions

Holds all the question titles and descriptions for this table

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
mixeval_id	integer	The ID of the mixed evaluation being described.	mixevals
question_num	integer	The number of this question in the mixed evaluation.
title	text	The question itself
instructions	text	A detailed description of the question.
question_type	enum(S,T)	The type of question. S stands for Rubric-type, T for text-answer type.
required	boolean integer	This question is mandatory. (I'm not sure if this is checked by code).
multiplier	integer	The weight of this question (?)
scale_level	integer	The total number of LOMs(?)
response_type	enum(S,L,NULL)	S denotes a short, single-line answer. L denotes a longer, multi-line answer.

personalizes

This table was use to store preferences for iPeer Lists, and course Home display. Ajax List doesn't use this table (but this could be added in the future).

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
user_id	integer	The user ID who this preference is for	users
attribute_code	string	The "key" of the attribute.
attribute_value	string	The "value" of the attribute.
created	date	a date entry for this entry's creation.
updated	date	a date entry for this entry's modification.

questions

Holds Survey questions, as far as I can tell.

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
prompt	text	Holds the question itself.
type	enum(M,C,S,L)	The type of the question.
master	enum(yes,no)	Not sure.

responses

Holds the possible responses for a Survey question.

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
question_id	integer	The ID of the question	questions
response	text	A response to this question.

rubrics

Hold the templates for the rubrics.

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
name	string	The name of the rubric
total_marks	integer	The total number of marks in this rubric (?)
zero_mark	enum(on,off)	Consider lowest LOM to be zero (?)
lom_max	integer	The number of LOMs in this evaluation
criteria	integer	The number of Criteria for this evaluation
availability	enum(public,private)	are others allowed to use/see this rubric template?
template	enum(horizontal, vertical)	Not sure (?)
creator_id	integer	the ID of the user who created this record.	users
created	date	a date entry for this entry's creation.
updater_id	integer	the last user's ID who changed this record.	users
modified	date	the last time of modification

rubrics_criterias

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key

Containts the criteria used for Rubrics

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
rubric_id	integer	The Rubric ID this criteria belongs to	rubrics
criteria_num	integer	The position of order this criteria is in.
criteria	text	The actual criteria text & description.
multiplier	integer	The importance of this criteria; its impact on score.

rubrics_loms

The very top descriptions of LOM, general, not per-question

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
rubric_id	integer	The Rubric ID this LOM belongs to	rubrics
lom_num	integer	The position number of this LOM
lom_comment	text	The description of this LOM

rubrics_criteria_comments

A per-question description of a Criteria's the LOMs

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
rubric_id	integer	The Rubric ID this LOM belongs to	rubrics
criteria_num	integer	The position of criteria that is described, along with..
lom_num	integer	...the position of the LOM that is described.
criteria_comment	The comment specific to this LOM.

simple_evaluatons

Contains the templates for Simple Evaluations

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
name	string	The name of this evaluation.
description	text	The description/instructions for this evaluation
point_per_member	integer	The points assigned to each member.
point_low_limit	integer	The minimum number of points per member, I guess, don't think it's used (?)
point_high_limit	integer	The maximum number of points per member, I guess, again. (?)
record_status	enum(A,I)	either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_id	integer	the ID of the user who created this record.	users
created	date	a date entry for this entry's creation.
updater_id	integer	the last user's ID who changed this record.	users
modified	date	the last time of modification

surveys

Holds the Surveys. There's some confusion here, since a survey has all the info of an event in it as well. I believe both entries are created for a single survey.

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
course_id	integer	The ID of the course this Survey is for. All students in that course participate.	courses
user_id	integer	Links to users table, but why (?) Does not seem to match to creator_id, and is usually 1.	users
name	string	The name of the survey
due_date	date	The date this survey (and event) is due
release_date_begin	date	The release date for this survey (and event). Students able to access the survey after this date.
release_date_end	date	The final fate for this survey (and event). Students are unable to access the survey after this date.
released	emun(0,1)	Not used. Should be deleted and replaced by release_date_begin/end
creator_id	integer	the ID of the user who created this record.	users
created	date	a date entry for this entry's creation.
updater_id	integer	the last user's ID who changed this record.	users
modified	date	the last time of modification

survey_questions

Connects the surveys table to the questions table (Many-to-many relationship). Also, gives the questions a number.

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
survey_id	integer	The survey this question belongs to.	surveys
number	integer	The question number in the survey.
question_id	integer	The question ID to put into survey	questions

survey_inputs

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
survey_id	integer	The survey this answer belongs to.	surveys
user_id	integer	The ID of the user thats responding.
question_id	integer	The question ID being answered.	questions
sub_id	integer	unused (?)
response_text	string	If this was a text question, the answer is recoded here
response_id	integer	Non-text responses are recorded here	responses

survey_group_sets

Holds the Group Sets created from surveys.

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
survey_id	integer	The survey this group set belongs to.	surveys
set_description	text	The description of this set of survey groups
num_groups	integer	the number of groups is this group set. Deprecated since 3.0. Use virtual field group_count instead.
date	integer	date of creation, but integer format (?)
released	enum(0,1)	Were these survey groups put into real groups in iPeer yet?

survey_groups

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
group_set_id	integer	The group set that this survey_group belongs to.	survey_group_sets
group_number	integer	The number of the group in the groups set. Just gives the groups an order, I guess.

survey_group_members

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
group_set_id	integer	The group set that the member's survey_group belongs to.	survey_group_sets
group_id	integer	The survey group this member belongs to	survey_groups
user_id	integer	The User ID of a user in this group	users

sys_function

Holds the access permissions, and the location of some iPeer functionality (used sparsely for that).

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
function_code	string	A short code to ID the function
function_name	string	A longer function name
parent_id	integer	Not sure (?)
controller_name	string	The name for the controller this function sits in
url_link	string	The link to CakePHP's was to call this function.
PermissionTypes	setof(A,I,S)	Determines who is allowed to access this function. A is Admin, I stands for Instructors, and S for Students.
record_status	enum(A,I)	either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_id	integer	the ID of the user who created this record.	users
created	date	a date entry for this entry's creation.
updater_id	integer	the last user's ID who changed this record.	users
modified	date	the last time of modification

sys_parameters

Holds system configuration variables.

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
parameter_code	string	A key used by the code to check this setting
parameter_value	string	The value of this configuration setting.
parameter_type	enum(B,I,S)	The type of the value. B is boolean, I is integer, and S means string.
description	text	The description of this parameter
record_status	enum(A,I)	either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_id	integer	the ID of the user who created this record.	users
created	date	a date entry for this entry's creation.
updater_id	integer	the last user's ID who changed this record.	users
modified	date	the last time of modification

users

A list of all of iPeer's users.

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
role	enum('A','S','I')	The role of this user. A stands for Admin, I for instructor, and S for student.
username	string	The login username for the user. Expected to be numeric for Students, but can be anything for other user roles.
password	string	An md5 hash of a users password
first_name	string	The user's first name.
last_name	string	The user's last name.
student_no	string	If the user is a student, this is their student number. it should be identical to username.
title	string	The title of the user, like mr, or ms.
email	string	The email of the user
last_login	date	unused, all NULL.
last_logout	date	unused, all NULL.
last_accessed	date	unused, all NULL.
record_status	enum(A,I)	either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_id	integer	the ID of the user who created this record.	users
created	date	a date entry for this entry's creation.
updater_id	integer	the last user's ID who changed this record.	users
modified	date	the last time of modification

user_enrols

Connects courses and students (many-to-many relationship)

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
course_id	The ID of the course the student is enrolled in.	courses
user_id	The ID of the student that's enrolled in this course.	users
record_status	enum(A,I)	either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_id	integer	the ID of the user who created this record.	users
created	date	a date entry for this entry's creation.
updater_id	integer	the last user's ID who changed this record.	users
modified	date	the last time of modification

user_courses

Connects courses and instructors & admins (many-to-many relationship)

Column	Type	Description	Foreign Key to table...
id	integer	Primary_Key
user_id	The ID of the student that's enrolled in this course.	users
course_id	The ID of the course the student is enrolled in.	courses
'access_right	enum(O,A,R)	The access rights to this course: O means owner, A means all rights, R means read only.
record_status	enum(A,I)	either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_id	integer	the ID of the user who created this record.	users
created	date	a date entry for this entry's creation.
updater_id	integer	the last user's ID who changed this record.	users
modified	date	the last time of modification