wiki:iPeerDatabaseSchema

Version 86 (modified by Taehyun You, 13 years ago) ( diff )

--

Present iPeer Database Schema

courses

Contains all the courses in the iPeer instance.

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
coursestringthe course number, ex: APSC 123
titlestringa very short course description, ex: Academic Writing
homepagestringa link to the course. Must start with http:// or https://
self_enrollemun(on, off)not fully implemented. The UI exists to modify this value, but no effect linked to it can be found in code
passwordstringan MD5 hash of the user password.
record_statusenum(A,I)either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_idintegerthe ID of the user who created this course.users
createddatea date entry for this entry's creation.
updater_idintegerthe last user's ID who changed this record.users
modifieddatethe last time of modification.
instructor_idintegerunused, 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.

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
evaluatorintegerThe user submitting the evaluation. They are evaluating other users.users
evaluateeintegerThe user being evaluated.users
scorefloatingThe total given by the evaluator to the evaluatee. (?)
comment_releaseintegerAre this entry's comments release to be viewed by other users?
grade_releaseintegerAre this entry's grades release to be viewed by other users?
grp_event_idintegerPoints to the group_events entry this entry is a part of.group_events
event_idintegerPoints to the event that the above group_event entry is part of.events
record_statusenum(A,I)either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_idintegerthe ID of the user who created this course.users
createddatea date entry for this entry's creation.
updater_idintegerthe last user's ID who changed this record.users
modifieddatethe 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.

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
evaluation_mixeval_idintegerThe ID of the evaluation_mixeval this question answer belongs too.evaluation_mixeval table.
question_numberintegerThe question being answered
question_commenttext A textual answer to the question, if present. NULL otherwise.
selected_lomintegerDoesn't seem to be used. All entries set at 0. (?)
gradefloatingA grade that the user gave when answering the question. Set to 0.0 if a textual question.
record_statusenum(A,I)either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_idintegerthe ID of the user who created this course. users
createddatea date entry for this entry's creation.
updater_idintegerthe last user's ID who changed this record. users
modifieddatethe last time of modification.

evaluation_rubrics

Holds all the submissions of evaluatee-evaluator pairs.

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_KeyNot used in this table (All 0's) (?)
evaluatorintegerThe user submitting the evaluation. They are evaluating other users.users
evaluateeintegerThe user being evaluated.users
general_commenttextA general comment about the evaluatee from the evaluator
scorefloatingThe total score that the evaluatee received from the evaluator.
comment_releaseintegerAre this entry's comments release to be viewed by other users?
grade_releaseintegerAre this entry's grades release to be viewed by other users?
grp_event_idintegerPoints to the group_events entry this entry is a part of.group_events
event_idintegerPoints to the event that the above group_event entry is part of.events
record_statusenum(A,I)either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_idintegerthe ID of the user who created this course. users
createddatea date entry for this entry's creation.
updater_idintegerthe last user's ID who changed this record. users
modifieddatethe 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.

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
evaluation_rubric_idintegerThe ID of the evaluation_rubric this question answer belongs too.evaluation_rubrics
criteria_numberintegerThe criteria number this entry belongs to. Not a foreign key ( :-/ Ugh... )
criteria_commenttextA textual comment about the evaluatee according to the above creteria.
selected_lomintegerThe level-of-mastery the evaluator selected for the evaluatee.
gradefloatingThe grade the above LOM gives the evaluatee.
record_statusenum(A,I)either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_idintegerthe ID of the user who created this course. Not used in this table (All 0's) (?)users
createddatea date entry for this entry's creation.
updater_idintegerthe last user's ID who changed this record. Not used in this table (All NULLS's)users
modifieddatethe 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.

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
evaluatorintegerThe user submitting the evaluation. They are evaluating other users.users
evaluateeintegerThe user being evaluated.users
scoreintegerThe score this evaluator gave the evaluatee.
eval_commenttextA comment about the evaluatee by the evaluator.
release_statusintegerUnsure (?) (vs grade_release)
grp_event_idintegerPoints to the group_events entry this entry is a part of.group_events
event_idintegerPoints to the event that the above group_event entry is part of.events
date_submitteddateThe date the evaluation was submitted. (Does this just copy the info in evaluation_submissions ?)
grade_releaseintegerUnsure (?) (vs grade_release)
record_statusenum(A,I)either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_idintegerthe ID of the user who created this course.users
createddatea date entry for this entry's creation.
updater_idintegerthe last user's ID who changed this record.users
modifieddatethe last time of modification.

evaluation_submissions

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
event_idintegerPoints to the event that the above group_event entry is part of.events
grp_event_idintegerPoints to the group_events entry this entry is a part of.group_events
submitter_idintegerThe evaluator's user IDusers
submittedintegerApparently 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_submitteddateThe date the evaluator submitted this evaluation.
record_statusenum(A,I)either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_idintegerthe ID of the user who created this record.users
createddatea date entry for this entry's creation.
updater_idintegerthe last user's ID who changed this record.users
modifieddatethe 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.

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
table_namestringThe name of the table that holds these types of evaluations.
model_namestringThe name of the model that connects the above table to cake.
display_for_selectionintegerA boolean integer. Tells if this type of evaluations should be available for selection when an instructor creates an event
record_statusenum(A,I)either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_idintegerthe ID of the user who created this record.users
createddatea date entry for this entry's creation.
updater_idintegerthe last user's ID who changed this record.users
modifieddatethe last time of modification

events

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
titlestringThe title of this event;
course_idintegerThe id of the course that this event will appear incourses
descriptiontextA textual description of this event.
event_template_type_idintegerThe type of the evaluation.event_template_types
template_idintegerThe ID of in the entry of this evaluation inside the particular evaluation type's tablesimple_evaluations or rubrics or surveys or mixevals
self_evalboolean integerDoes the user evaluate themselves as well?
com_req'boolean integerAre the comments required?
due_datedateThe due date for the event.
release_date_begindateThe when the event will open - start showing up on the student's display.
release_date_enddateThe when the event will close - stop showing up on the student's display, even if it was completed.
record_statusenum(A,I)either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_idintegerthe ID of the user who created this record.users
createddatea date entry for this entry's creation.
updater_idintegerthe last user's ID who changed this record.users
modifieddatethe last time of modification

groups

Holds the members for each group in a class.

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
group_numintegerThe Group number, no the ID. Not sure why we have it.
course_idintegerThe ID of the course this group belongs tocourses
record_statusenum(A,I)either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_idintegerthe ID of the user who created this record.users
createddatea date entry for this entry's creation.
updater_idintegerthe last user's ID who changed this record.users
modifieddatethe last time of modification

groups_members

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

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
group_idintegerThe ID of the group groups
user_idintegerThe 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.

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
group_idintegerThe ID of the group groups
event_idintegerThe ID of the event event
markedstringOne 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.
gradeintegerI have no idea... NULL in all case I've seen so far. Unused (?)
grade_release_statusstringCan the users see the grades their group members gave them, or not? Some, None and All are possible.
grade_release_statusstringCan the users see the comments their group members gave them, or not? Just like above, Some, None and All are possible.
record_statusenum(A,I)either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_idintegerthe ID of the user who created tthis record.users
createddatea date entry for this entry's creation.
updater_idintegerthe last user's ID who changed this record.users
modifieddatethe last time of modification

mixevals

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

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
namestringTitle of the evaluation
total_marksintegerThe total amount of point possible in this evaluation per member.
zero_markenum(on,off)Does the lowerst LOM count as a zero score?
total_questionintegerThe total number of questions - both Lickert and Textual Answer.
lickert_question_maxintegerThe number of Lickert(Rubric-type) questions.
scale_maxintegerThe number of LOMs for Lickert questions.
prefill_question_maxintegerThe number of Textual Questions to Add.
availabilityenum(public,private)Determines whether this rubric can be used in an event by users other than the creator.
creator_idintegerthe ID of the user who created this record.users
createddatea date entry for this entry's creation.
updater_idintegerthe last user's ID who changed this record.users
modifieddatethe last time of modification

mixevals_questions_descs

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

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
mixeval_idintegerThe ID of the mixed evaluation being described.mixevals
question_numintegerThe question this LOM description is for.
scale_levelintegerHow many points this LOM is worth.
descriptorstringThe description of this LOM.

mixevals_questions

Holds all the question titles and descriptions for this table

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
mixeval_idintegerThe ID of the mixed evaluation being described.mixevals
question_numintegerThe number of this question in the mixed evaluation.
titletextThe question itself
instructionstextA detailed description of the question.
question_typeenum(S,T)The type of question. S stands for Rubric-type, T for text-answer type.
requiredboolean integerThis question is mandatory. (I'm not sure if this is checked by code).
multiplierintegerThe weight of this question (?)
scale_levelintegerThe total number of LOMs(?)
response_typeenum(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).

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
user_idintegerThe user ID who this preference is forusers
attribute_codestringThe "key" of the attribute.
attribute_valuestringThe "value" of the attribute.
createddatea date entry for this entry's creation.
updateddatea date entry for this entry's modification.

questions

Holds Survey questions, as far as I can tell.

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
prompttextHolds the question itself.
typeenum(M,C,S,L) The type of the question.
masterenum(yes,no)Not sure.

responses

Holds the possible responses for a Survey question.

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
question_idintegerThe ID of the questionquestions
responsetextA response to this question.

rubrics

Hold the templates for the rubrics.

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
namestringThe name of the rubric
total_marksintegerThe total number of marks in this rubric (?)
zero_markenum(on,off)Consider lowest LOM to be zero (?)
lom_maxintegerThe number of LOMs in this evaluation
criteriaintegerThe number of Criteria for this evaluation
availabilityenum(public,private)are others allowed to use/see this rubric template?
templateenum(horizontal, vertical)Not sure (?)
creator_idintegerthe ID of the user who created this record.users
createddatea date entry for this entry's creation.
updater_idintegerthe last user's ID who changed this record.users
modifieddatethe last time of modification

rubrics_criterias

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key

Containts the criteria used for Rubrics

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
rubric_idintegerThe Rubric ID this criteria belongs torubrics
criteria_numintegerThe position of order this criteria is in.
criteriatextThe actual criteria text & description.
multiplierintegerThe importance of this criteria; its impact on score.

rubrics_loms

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

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
rubric_idintegerThe Rubric ID this LOM belongs torubrics
lom_numintegerThe position number of this LOM
lom_commenttextThe description of this LOM

rubrics_criteria_comments

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

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
rubric_idintegerThe Rubric ID this LOM belongs torubrics
criteria_numintegerThe position of criteria that is described, along with..
lom_numinteger...the position of the LOM that is described.
criteria_commentThe comment specific to this LOM.

simple_evaluatons

Contains the templates for Simple Evaluations

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
namestringThe name of this evaluation.
descriptiontextThe description/instructions for this evaluation
point_per_memberintegerThe points assigned to each member.
point_low_limitintegerThe minimum number of points per member, I guess, don't think it's used (?)
point_high_limitintegerThe maximum number of points per member, I guess, again. (?)
record_statusenum(A,I)either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_idintegerthe ID of the user who created this record.users
createddatea date entry for this entry's creation.
updater_idintegerthe last user's ID who changed this record.users
modifieddatethe 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.

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
course_idintegerThe ID of the course this Survey is for. All students in that course participate.courses
user_idintegerLinks to users table, but why (?) Does not seem to match to creator_id, and is usually 1.users
namestringThe name of the survey
due_datedateThe date this survey (and event) is due
release_date_begindateThe release date for this survey (and event). Students able to access the survey after this date.
release_date_enddateThe final fate for this survey (and event). Students are unable to access the survey after this date.
releasedemun(0,1)Not used. Should be deleted and replaced by release_date_begin/end
creator_idintegerthe ID of the user who created this record.users
createddatea date entry for this entry's creation.
updater_idintegerthe last user's ID who changed this record.users
modifieddatethe last time of modification

survey_questions

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

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
survey_idintegerThe survey this question belongs to.surveys
numberintegerThe question number in the survey.
question_idintegerThe question ID to put into surveyquestions

survey_inputs

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
survey_idintegerThe survey this answer belongs to.surveys
user_idintegerThe ID of the user thats responding.
question_idintegerThe question ID being answered.questions
sub_idinteger unused (?)
response_textstringIf this was a text question, the answer is recoded here
response_idintegerNon-text responses are recorded hereresponses

survey_group_sets

Holds the Group Sets created from surveys.

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
survey_idintegerThe survey this group set belongs to.surveys
set_descriptiontextThe description of this set of survey groups
num_groupsintegerthe number of groups is this group set. Deprecated since 3.0. Use virtual field group_count instead.
dateintegerdate of creation, but integer format (?)
releasedenum(0,1)Were these survey groups put into real groups in iPeer yet?

survey_groups

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
group_set_idintegerThe group set that this survey_group belongs to.survey_group_sets
group_numberintegerThe number of the group in the groups set. Just gives the groups an order, I guess.

survey_group_members

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
group_set_idintegerThe group set that the member's survey_group belongs to.survey_group_sets
group_idintegerThe survey group this member belongs tosurvey_groups
user_idintegerThe User ID of a user in this groupusers

sys_function

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

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
function_codestringA short code to ID the function
function_namestringA longer function name
parent_idintegerNot sure (?)
controller_namestringThe name for the controller this function sits in
url_linkstringThe link to CakePHP's was to call this function.
PermissionTypessetof(A,I,S)Determines who is allowed to access this function. A is Admin, I stands for Instructors, and S for Students.
record_statusenum(A,I)either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_idintegerthe ID of the user who created this record.users
createddatea date entry for this entry's creation.
updater_idintegerthe last user's ID who changed this record.users
modifieddatethe last time of modification

sys_parameters

Holds system configuration variables.

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
parameter_codestringA key used by the code to check this setting
parameter_valuestringThe value of this configuration setting.
parameter_typeenum(B,I,S)The type of the value. B is boolean, I is integer, and S means string.
descriptiontextThe description of this parameter
record_statusenum(A,I)either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_idintegerthe ID of the user who created this record.users
createddatea date entry for this entry's creation.
updater_idintegerthe last user's ID who changed this record.users
modifieddatethe last time of modification

users

A list of all of iPeer's users.

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
roleenum('A','S','I')The role of this user. A stands for Admin, I for instructor, and S for student.
usernamestringThe login username for the user. Expected to be numeric for Students, but can be anything for other user roles.
passwordstringAn md5 hash of a users password
first_namestringThe user's first name.
last_namestringThe user's last name.
student_nostringIf the user is a student, this is their student number. it should be identical to username.
titlestringThe title of the user, like mr, or ms.
emailstringThe email of the user
last_logindateunused, all NULL.
last_logoutdateunused, all NULL.
last_accesseddateunused, all NULL.
record_statusenum(A,I)either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_idintegerthe ID of the user who created this record.users
createddatea date entry for this entry's creation.
updater_idintegerthe last user's ID who changed this record.users
modifieddatethe last time of modification

user_enrols

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

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
course_idThe ID of the course the student is enrolled in.courses
user_idThe ID of the student that's enrolled in this course.users
record_statusenum(A,I)either 'A' or 'I', representing active or inactive. Not always checked by code.
creator_idintegerthe ID of the user who created this record.users
createddatea date entry for this entry's creation.
updater_idintegerthe last user's ID who changed this record.users
modifieddatethe last time of modification

user_courses

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

ColumnTypeDescriptionForeign Key to table...
idintegerPrimary_Key
user_idThe ID of the student that's enrolled in this course.users
course_idThe ID of the course the student is enrolled in.courses
access_rightenum(O,A,R)The access rights to this course:
O means owner, A means all rights, R means read only. record_statusenum(A,I)either 'A' or 'I', representing active or inactive. Not always checked by code. creator_idintegerthe ID of the user who created this record.users createddatea date entry for this entry's creation. updater_idintegerthe last user's ID who changed this record.users modifieddatethe last time of modification

Attachments (2)

Download all attachments as: .zip

Note: See TracWiki for help on using the wiki.