wiki:iPeerDatabaseSchema

Version 52 (modified by Serge Okon, 14 years ago) ( diff )

--

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.
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 course.
createddatea date entry for this entry's creation.
updater_idintegerthe last user's ID who changed this record.
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 course.
createddatea date entry for this entry's creation.
updater_idintegerthe last user's ID who changed this record.
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 course.
createddatea date entry for this entry's creation.
updater_idintegerthe last user's ID who changed this record.
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 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

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

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

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

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. (?)

survey_group_members

survey_group_sets

survey_groups

survey_inputs

survey_questions

surveys

sys_function

sys_parameters

user_course

user_enrols

users

phew ...

Attachments (2)

Download all attachments as: .zip

Note: See TracWiki for help on using the wiki.