1.11 data dictionary

This page is an (incomplete) documentation of the data structure in Chamilo 1.11.

Note 1: All tables starting with a c_ are course-specific tables. They always include a reference to the course as the c_id field, and generally still hold a double identifier: iid is the new, platform-unique identifier, while the combination of id+c_id is the "old" unique identifier. Queries should use the iid field, although some have been left using id+c_id by lack of time during the transition.

Note 2: Sessions are really special, and most course resources tables include a session ID which can be either 0 or NULL (depending on the age of the table), or has a non-zero value. In case it has a (numeric) value, it means the resource is available inside the session-course and not inside the base course. If it is 0 or NULL, it means it is available inside the base course and for some resources, this means it is also visible in the session-course.

Table	Table's purpose	Field	Field's purpose
access_url	List of all URLs if using multiple URLs	id	Unique identifier
		url
		...
access_url_rel_course	Link between the access_url table and the course table
		...
access_url_rel_course_category	Link between access_url and course_category
		...
access_url_rel_session	Link between access_url and session for sessions registered in a specific URL
		...
access_url_rel_user	Link between access_url and user for users registered in a specific URL
		...
access_url_rel_usergroup	Link between access_url and usergroup for groups registered in a specific URL
		...
admin	Contains a list of ID of user that are platform administrators. If a user ID is here, the user is a platform admin (access URLs change that behaviour slightly)
		...
announcement_rel_group	Link between sys_announcement and usergroup
		...
block
		...
branch_sync	Allows for the synchronisation of Chamilo portals between themselves (not used yet)
		...
branch_transaction	Allows for the registration of transactions (pending or executed) from another system, to be executed in Chamilo
		...
branch_transaction_status	Dictionary of status terms for branch_transaction
		...
c_announcement	Contains the announcements in the corresponding course tool
		...
c_announcement_attachment	Contains the attachments (files) of c_announcement
		...
c_attendance	Contains one attendance "resource". Courses can contain several attendance resources, which can each be evaluated in the gradebook. Each contains dates (c_attendance_calendar), which contain attendance records (see c_attendance_sheet)
		...
c_attendance_calendar	Each attendance datetime is registered here separately.
		...
c_attendance_calendar_rel_group	If an attendance date is specific to a users group in a course, this links the date to the c_group
		...
c_attendance_result	The global attendance "rate" of a user in an attendance resource
		...
c_attendance_sheet	Registers the attendance or lack thereof of a user to a specific calendar datetime
		...
c_attendance_sheet_log	Log table for changes in the c_attendance_sheet
		...
c_blog	Entries for each blog created in a course
		...
c_blog_attachment	File attachments to blog posts
		...
c_blog_comment	Comments on blog posts
		...
c_blog_post	Messages posted in blogs
		...
c_blog_rating	Ratings given to blog posts
		...
c_blog_rel_user	Link between c_blog and user
		...
c_blog_task	Tasks that can be configured inside a blog
		...
c_blog_task_rel_user	Assignation of tasks (c_blog_task) to users (user)
		...
c_calendar_event	Events of the calendar
		...
c_calendar_event_attachment	Attachment (files) to the c_calendar_event records
		...
c_calendar_event_repeat	Definition of repetitions for existing events
		...
c_calendar_event_repeat_not	Definition of exclusions/exceptions in repetitions for existing events
		...
c_chat_connected	Logs when a user is active in the course chat
		...
c_course_description	The sections available in the course description tool
		...
c_course_setting	Settings of the course (whether to send e-mails, when, etc) that can be access through the Settings tool inside the course
		...
c_document	The list of documents and folders of the documents tool
		...
c_dropbox_category	Folders inside the dropbox
		...
c_dropbox_feedback	Feedback sent by users on files in the dropbox
		...
c_dropbox_file	A file dropped in the dropbox
		...
c_dropbox_person	The relationships between files (c_dropbox_file) and whom they are sent to (user)
		...
c_dropbox_post	A message posted on a file (c_dropbox_file)
		...
c_exercise_category	Optional table created by manual action documented in configuration.php
		...
c_forum_attachment	File attachments to c_forum_post records
		...
c_forum_category	Forum categories (which include forums)
		...
c_forum_forum	Forums (which include threads)
		...
c_forum_mailcue	...	...
		...
c_forum_notification	...	...
		...
c_forum_post	Forum posts (in response to a thread)	...
		...
c_forum_thread	Forum threads (which give birth to posts and have more properties than posts)
		...
c_forum_thread_qualify	If a thread can be scored, the scores given by teachers or other users are stored here
		...
c_forum_thread_qualify_log	A log table of all the changes in the forum threads scores
		...
c_glossary	Terms from the course glossary
		...
c_group_category	Categories of course groups (c_group_info)
		...
c_group_info	Course groups and their description
		...
c_group_rel_tutor	Tutors assigned to course groups (c_group_info)
		...
c_group_rel_user	Users registered in course groups (c_group_info)
		...
c_item_property	This gathers the definition, visibility and changelog of most learning objects in Chamilo. It is a central piece of the database structure in 1.11
		...
c_link	Course links
		...
c_link_category	Categories of course links (c_link)
		...
c_lp	Course learning paths
		...
c_lp_category	Categories for courses learning paths
		...
c_lp_category_user	Access specifications for specific users to specific learning paths categories
		...
c_lp_item	Documents inside a learning path
		...
c_lp_item_view	Each visualization by a user (user) of one document (c_lp_item) inside the learning path (c_lp). Belongs to a c_lp_view entry
		...
c_lp_iv_interaction	SCORM interactions for one specific c_lp_item_view
		...
c_lp_iv_objective	SCORM objectives for one specific c_lp_item_view
		...
c_lp_view	View of a learning path (c_lp) by a user. Contains many c_lp_view_item
		...
c_notebook	Posts in the notebook tool in the course (personal notes)
		...
c_online_connected	...
		...
c_online_link	...
		...
c_permission_group	Blog-related user permissions ...
		...
c_permission_task	Blog-releated user permissions in tasks ...
		...
c_permission_user	Blog-related user permissions ...
		...
c_quiz	List of exercises in courses
		...
c_quiz_answer	List of possible answers to a question (c_quiz_question) in a c_quiz
		...
c_quiz_question	List of questions in the exercises tool. As questions can be shared between c_quiz, the real link between questions and quizzes is through the c_quiz_rel_question table
		...
c_quiz_question_category	Categories of questions in quizzes
		...
c_quiz_question_option	...
		...
c_quiz_question_rel_category	Relationship between a c_quiz_question and a c_quiz_question_category
		...
c_quiz_rel_category	Relationship between a c_quiz and a quiz category (see c_exercise_category)
		...
c_quiz_rel_question	Relationship between a question (c_quiz_question) and a specific quiz (c_quiz)
		...
c_resource	...
		...
c_role	Blog-related roles
		...
c_role_group	Blog-related roles
		...
c_role_permissions	Blog-related roles
		...
c_role_user	Blog-related roles
		...
c_student_publication	An assignment handed over by a student
		...
c_student_publication_assignment	Assigments definition (tasks/works)
		...
c_student_publication_comment	Comments on c_student_publication
		...
c_student_publication_rel_document	Link between a document (c_document) that serves as a template to the assignments
		...
c_student_publication_rel_user	Link between users and assignments (to assign specific assignments to a reduced number of users)
		...
c_survey	Surveys
		...
c_survey_answer	Possible answers to survey questions (c_survey_question)
		...
c_survey_group	...
		...
c_survey_invitation	Invitations of users to surveys
		...
c_survey_question	Survey questions
		...
c_survey_question_option	Options on survey questions
		...
c_thematic	Highest level of thematic progress categorization
		...
c_thematic_advance	Dates related to the progress in a thematic plan
		...
c_thematic_plan	Second level of thematic progress
		...
c_tool	Entries of available tools on the course homepage
		...
c_tool_intro	Introduction sections. On the course homepage but also (if enabled) in each tool.
		...
c_userinfo_content	Deprecated: Values for user fields in courses
		...
c_userinfo_def	Deprecated: User fields in courses
		...
c_wiki	Wiki pages in course
		...
c_wiki_conf	Wiki configuration in a course
		...
c_wiki_discuss	Conversations on each wiki page
		...
c_wiki_mailcue	...
		...
career	Careers (admin panel)
		...
chat	Chat sessions
		...
chat_video	Videochat sessions (temporarily deprecated)
		...
class_item	...
		...
class_user	...
		...
course	Courses	id	Unique identified (referenced by c_id everywhere else)
		...
course_category	Courses categories
		...
course_module	...
		...
course_rel_class	Relationship between course and usergroup
		...
course_rel_user	Subscription of users to courses (if not using sessions)
		...
course_rel_user_catalogue	Special accesses to catalogue by specific users
		...
course_request	Requests for courses by users (if the course requests feature is enabled)
		...
course_type	...
		...
event_email_template	...
		...
event_sent	...
		...
extra_field	Extra fields definition. Also defines which type of resource it applies to (users, courses, quizzes, etc)
		...
extra_field_option_rel_field_option	...
		...
extra_field_options	Possible values for extra_field_values
		...
extra_field_rel_tag	Tags on extra fields
		...
extra_field_saved_search	...
		...
extra_field_values	Values stored for specific resources in specific extra fields
		...
grade_components	...
		...
grade_model	...
		...
gradebook_category	Highest level of the gradebook structure (attributing certificates and skills)
		...
gradebook_certificate	Generated certificates
		...
gradebook_evaluation	External evaluation scored in the gradebook
		...
gradebook_link	Internal evaluation (Chamilo resources) scored in the gradebook
		...
gradebook_linkeval_log	...
		...
gradebook_result	Results of users on gradebook_link or gradebook_evaluation
		...
gradebook_result_log	...
		...
gradebook_score_display	...
		...
gradebook_score_log	...
		...
hook_call	Internal hooks definition
		...
hook_event	Internal hooks definition
		...
hook_observer	Internal hooks definition
		...
language	The language supported for the user interface, and sublanguages
		...
legal	Terms and conditions
		...
mail_template	...
		...
message	User messages on the social network + all messages sent by the system by e-mail and saved for the local inbox
		...
message_attachment	File attachments for message
		...
message_likes	Likes given to messages on the social network
		...
notification	...
		...
openid_association	...
		...
personal_agenda	Personal events stored in the agenda
		...
personal_agenda_repeat	Repetition on personal_agenda
		...
personal_agenda_repeat_not	Repetition exclusions on personal_agenda_repeat
		...
plugin_*	All plugin tables (see plugin documentation)
		...
promotion	Promotions are contained in careers and contain sessions.
		...
room	Rooms are an item for a future feature in Chamilo allowing you to define a physical or virtual room for a synchronous lesson.
		...
scheduled_announcements	If the scheduled announcements for sessions are enabled, contains the messages scheduled to be sent at specific dates to session users
		...
search_engine_ref	Internal data on indexer with Xapian
		...
sequence	Sequences for course/session access
		...
sequence_condition	Conditions for sequence
		...
sequence_formula	Formula for sequence
		...
sequence_method	Method for sequence
		...
sequence_resource	Method for resource
		...
sequence_row_entity	...
		...
sequence_rule	Rules for sequence
		...
sequence_rule_condition	...
		...
sequence_rule_method	...
		...
sequence_type_entity	...
		...
sequence_valid	...
		...
sequence_value	...
		...
sequence_variable	...
		...
session	Course sessions definitions	id	Unique identifier
		...
session_category	Categories of sessions
		...
session_rel_course	Relationship between sessions and courses (which courses are contained in which sessions)
		...
session_rel_course_rel_user	Subscription of users to a course in a session
		...
session_rel_user	Subscription of users in a session
		...
settings_current	Platform settings
		...
settings_options	Platform settings' possible option
		...
shared_survey	...
		...
shared_survey_question	...
		...
shared_survey_question_option	...
		...
skill	Skills
		...
skill_level	Possible levels for a skill
		...
skill_profile	Groups of skills levels (level profiles)
		...
skill_rel_gradebook	Linking betweek gradebooks (gradebook_category)
		...
skill_rel_profile	Linking of skill with skill_profile
		...
skill_rel_skill	Hierarchy of skills between themselves
		...
skill_rel_user	When a user gets a skill, this table registers it
		...
skill_rel_user_comment	Users having obtained skills can get feedback from other users. They are stored here.
		...
specific_field	...
		...
specific_field_values	...
		...
sys_announcement	Global announcements
		...
sys_calendar	Global agenda events (not repetition possible)
		...
system_template	Global document templates
		...
tag	Tags
		...
templates	...
		...
ticket_assigned_log	...
		...
ticket_category	...
		...
ticket_category_rel_user	...
		...
ticket_message	...
		...
ticket_message_attachments	...
		...
ticket_priority	...
		...
ticket_project	...
		...
ticket_status	...
		...
ticket_ticket	...
		...
track_course_ranking	Ranking based on score voted by users on courses
		...
track_e_access	Log of all accesses to courses, tools and sessions in the platform. It does not track all actions, though. Only "entering" each course and tool
		...
track_e_attempt	Log of all attempts (answers to questions) in quizzes (c_quiz). These are sub-elements of track_e_exercises.
		...
track_e_attempt_coeff	...
		...
track_e_attempt_recording	(experimental) Log of historical changes to answers. This is not working properly yet in 1.11.x
		...
track_e_course_access	Logs all login/logout actions to courses. This can be confusing when users open tabs to different courses at the same time.	course_access_id	unique ID
		c_id
		user_id
		login_course_date	datetime field for when the user logged in
		logout_course_date	datetime field for when the user logged out
		counter	number of actions the user took in this course between login and logout
		session_id
		user_ip	IP address (v4 or v6) of the user when logged in
track_e_default	Log of important actions in the system. This can be considered an audit table, as it records actions like user/course/session creations/deletions
		...
track_e_downloads	Log of all documents downloads (only logs documents that exist in the c_document table)
		...
track_e_exercises	Log of all quiz (c_quiz) attempts by a user. For the detail of each answer, check track_e_attempt
		...
track_e_hotpotatoes	Log of scores at quizzes of type HotPotatoes
		...
track_e_hotspot	Log of additional data regarding questions of HotSpot type (questions on images).	hotspot_id	unique id
		hotspot_user_id	User ID
		hotspot_course_code	Course code (not ID)
		c_id	Course ID
		hotspot_exe_id
		hotspot_question_id
		hotspot_answer_id
		hotspot_correct
		hotspot_coordinate
track_e_item_property	Log of changes in the c_item_property table	id	Unique ID
		course_id
		item_property_id	ID in the c_item_property table (FK)
		title
		content
		progress
		lastedit_date	datetime
		lastedit_user_id	User ID
		session_id
track_e_lastaccess	Log of accesses to tools in courses	access_id	unique ID
		access_user_id	User ID
		access_date	datetime (in UTC)
		c_id	Course ID
		access_tool	name of the tool
		access_session_id	Session ID
track_e_links	Log of accesses to links in the course's links tool (c_link)	links_id	unique id
		links_user_id	User Id
		links_date	datetime
		c_id
		links_link_id	iid from c_link (FK)
		links_session_id
track_e_login	Log all login and logout actions, with no differentiation of what the user did (see track_e_lastaccess for that)	login_id	unique ID
		login_user_id	User ID
		login_date	datetime
		user_ip	IP address (v4 or v6)
		logout_date	datetime
track_e_online	Log online presence of users. Used to tell if the user is online by applying a difference to the current datetime	login_id	unique ID
		login_user_id	User ID (FK)
		login_date	datetime
		user_ip	IP address (v4 or v6)
		c_id
		session_id	Session ID
		access_url_id	ID from access_url
track_e_open	deprecated
		...
track_e_uploads	Log uploads	upload_id	unique ID
		upload_user_id	User ID (FK)
		upload_date	datetime
		upload_cours_id	deprecated
		c_id
		upload_work_id	iid from c_student_publication
		upload_session_id	Session ID (FK)
track_stored_values	deprecated
		...
track_stored_values_stack	deprecated
		...
user	Users of the system	id	Unique ID
		user_id	deprecaed
		username	The username used to login
		username_canonical	a cleaned version of the username
		email_canonical	a cleaned version of the email
		email	the user e-mail
		locked	1 if the user account has been locked from the system
		enabled	1 if the user account has been enabled
		expired	1 if the user account has expired
		credentials_expired	(not yet used) 1 if the user's credentials have expired
		credentials_expire_at	(not yet used) expiry datetime for the credentials
		expires_at	(not used yet) datetime at which the user account expires - see expiration_date field below
		lastname	User's lastname
		firstname	User's firstname
		password	Password (hashed/encrypted following the method defined by $_configuration['password_encryption'] in app/config/configuration.php
		phone	Phone number
		address	(not yet used) Address
		salt	Value used to encrypt the password if the corresponding encryption method requires it
		last_login	The datetime of the last login for this user
		created_at	(not used yet) The datetime of creation - see registration_date field below
		updated_at	The datetime of last update of the user account
		confirmation_token	String used for the password change confirmation
		password_requested_at	Datetime at which the password regen was requested
		roles	an internal array with some information. Not really used at this point.
		profile_completed	1 if the profile has been completed
		auth_source	'platform' by default. If using LDAP or other authentication methods, this will vary based on the system used.
		status	User default role. 1 if the user is a teacher, 5 if the user is a student. Check main/inc/lib/api.lib.php for more roles
		official_code	Optional code
		picture_uri	URL of the user picture
		creator_id	The user ID of the person who created this user
		competences	Part of portfolio fields
		diplomas	Part of portfolio fields
		openarea	Part of portfolio fields
		teach	Part of portfolio fields
		productions	Part of portfolio fields
		language	Interface language for this user (this is a string but references a language in the language table)
		registration_date	Registration datetime
		expiration_date	Expiration datetime
		active	1 if the user account is active, 0 otherwise. An inactive user will not be able to connect to the platform.
		openid	A token if using early versions of the OpenID implementation in Chamilo
		theme	Default theme
		hr_dept_id	deprecated
user_api_key
		...
user_course_category
		...
user_friend_relation_type
		...
user_rel_course_vote
		...
user_rel_event_type
		...
user_rel_tag
		...
user_rel_user
		...
usergroup
		...
usergroup_rel_course
		...
usergroup_rel_question
		...
usergroup_rel_session
		...
usergroup_rel_user
		...
usergroup_rel_usergroup
		...