Data Dictionary

The following tables contain descriptions of the various tables/views and fields used in the Volunteer Management module.

Volunteer Management Database Schema as of June 25th 2007,RC2

Contents

vm_access_request

Field Type Null Default Links to Comments
request_id int(11) No A unique id for each access request for system use. An access request is simply a page that a user can try to access.
act varchar(100) Yes NULL The 'act' URL parameter of the access request
vm_action varchar(100) Yes NULL The 'vm_action' URL parameter of the access request
description varchar(300) Yes NULL field that stores the textual description about the constraint


vm_access_classification_to_request

Field Type Null Default Links to Comments
request_id int(11) No The unique id for the access request to impose Sahana's data classification constraints upon
table_name varchar(200) No A database table that will be viewed/manipluated by accessing this request
crud varchar(4) No The different operations (Create, Read, Update, Delete) that will be carried out on the given database table when accessing the request

vm_access_constraint

Field Type Null Default Links to Comments
constraint_id varchar(30) No A unique id for an access constraint. An access constraint is a special VM-defined restriction (or possible ovrride) to impose on a given request.
description varchar(200) Yes NULL A description of the constraint.

vm_access_constraint_to_request

Field Type Null Default Links to Comments
request_id int(11) No The unique id of an access request
constraint_id varchar(30) No The unique id of an access constraint to impose on the given request.

vm_courier

Field Type Null Default Links to Comments
message_id bigint(40) No an id for the message sent or received
to_id varchar(60) No an id for the person who is supposed to receive it, pertaining to the p_uuid of the receiver
from_id varchar(60) No an id for the person who sent it, pertaining to the p_uuid of the sender

vm_hours

Field Type Null Default Links to Comments
shift_id int(11) No a unique id for the shift worked by a volunteer
p_uuid varchar(60) No an id for the person who is working as a volunteer
pos_id varchar(60) No an id for the position the volunteer holds under a project
shift_start datetime No the datestamp (yyyy-mm-dd hh:mm:ss) to indicate the start of a shift
shift_end datetime No the datestamp (yyyy-mm-dd hh:mm:ss) to indicate the end of a shift
status set('pending', 'approved') No field to indicate whether the shift has been approved or is pending approval from site manager or mainops

vm_image

Field Type Null Default Links to Comments
img_uuid varchar(60) No a unique id for the image uploaded
original blob No field to store the originally posted image file's size
image_data blob No field to store the normally displayed picture (resized in case if it is too big) size
thumb_data blob No field to store the size of the thumbnail generated for each image stored
p_uuid varchar(60) No the id of the person working as the volunteer
date_added datetime No a datestamp (yyyy-mm-dd hh:mm:ss) to indicate when the image was uploaded
width smallint(6) No field to store the width of the image in pixel size
height smallint(6) No field to store the height of the image in pixel size
thumb_width smallint(6) No field to store the width of the thumbnail of the actual image
thumb_height smallint(6) No field to store the height of the thumbnail of the actual image
mime_type varchar(60) No field to indicate what file format the image is saved
name varchar(60) No field to store the actual name of the image uploaded

vm_mailbox

This table introduces a bit of planned redudancy in order to preserve all vm_courier and vm_message data. Without this table, deleting a message would completely remove the fact that it ever took place. An extra table is used for the mailbox information because sending a message is a 1-to-many relationship.

Field Type Null Default Links to Comments
p_uuid varchar(60) No The p_uuid of the volunteer whose mailbox it is.
message_id bigint(40) No 0 The id of a message in the mailbox.
box int(1) No 0 The type of mailbox, 0 if it's the volunteer's inbox, 1 if it's the volunteer's outbox.
checked int(1) Yes 0 Whether or not the message has been read yet by the user, 0 if not, 1 if so.

vm_message

Field Type Null Default Links to Comments
message_id bigint(40) No unique id for each message
message text Yes field to store the test of the message
time timestamp No a timestamp (yyyy-mm-dd hh:mm:ss) to indicate when the message was sent/received

vm_position

Field Type Null Default Links to Comments
pos_id varchar(60) No the id of the position (classified by skill) under a project
proj_id varchar(60) No id for a project
ptype_id varchar(60) No id for a position type
title varchar(30) No field to hold the title of a position
slots smallint(6) No field to hold the targeted number of volunteers for a project
description text No field that textually describes the position in details
status set('active', 'retired') No active field to indicate whether the project is active or not
payrate double Yes 0 field that stores the payrate for each position

vm_positiontype

Field Type Null Default Links to Comments
ptype_id varchar(60) No id for the type of position
title varchar(20) No field to store the title of the position
description varchar(300) No field that stores the textual description of the position
skill_code varchar(20) No field that stores the unique code for each skill type

vm_proj_position

Field Type Null Default Links to Comments
proj_id varchar(60) No unique id of the project
pos_id varchar(60) No id for a position in a project
title varchar(30) No field that holds the position title
slots smallint(6) No field to hold the number of target volunteers for a project
description text No field stroing the textual description of the position

vm_proj_skills

Field Type Null Default Links to Comments
p_uuid varchar(60) Yes NULL id unique for each volunteer
opt_skill_code varchar(100) Yes NULL field pertaining to the skill code for each skill type

vm_proj_vol

Field Type Null Default Links to Comments
p_uuid varchar(60) No id unique for each volunteer
proj_id bigint(20) No unique id of the project
pos_id varchar(60) No id for a position in a project
status set('active', 'retired') Yes active field to indicate if the project is active or not, always set to active in this table
hours bigint(20) Yes NULL field to store the number of hours put in by a volunteer
task varchar(20) Yes NULL field to store the tast the volunteer has to do
payrate double No 0 field that stores the hourly rate for each position/task

vm_projects

Field Type Null Default Links to Comments
proj_id bigint(20) No id which is unique for each project
name varchar(50) Yes NULL field to store the name of the project
location_id varchar(60) Yes NULL field that stores the location where the project is at
start_date date Yes NULL datestamp for the start date of the project (yyyy-mm-dd)
end_date date Yes NULL datestamp for the end date of the project (yyyy-mm-dd)
description text No field that stores the description for the project
status set('active', 'completed') No active field to indicate if the project is active or not, always set to active in this table

vm_vol_details

Field Type Null Default Links to Comments
p_uuid varchar(60) No id unique for each volunteer
org_id varchar(60) No the o_uuid of the organization that the volunteer is affiliated with
photo blob No field that stores the image information for a volunteer
date_avail_start date No 2006-07-06| field that stores the information regarding the starting date for a volunteer (yyyy-mm-dd)
date_avail_end date No 2006-12-31 field that stores the information regarding the date a volunteer is available until (yyyy-mm-dd)
hrs_avail_start time No 09:00:00 field that stores the starting hours for a volunteer (hh:mm:ss)
hrs_avail_end time No 17:00:00 field that stores the hours a volunteer is available until (hh:mm:ss)
status set('active', 'retired') No active field to indicate if the project is active or not, always set to active in this table
special_needs text No field that stores information regarding any restrictions or special needs a volunteer has or might have

vm_vol_position

Field Type Null Default Links to Comments
p_uuid varchar(60) No id unique for each volunteer
pos_id varchar(60) No id for a position in a project
status set('active', 'retired') Yes active field to indicate if the project is active or not, always set to active in this table
payrate double Yes NULL field to store the payrate information for the position
hours bigint(20) Yes NULL field to store the number of hours put in by a volunteer
task varchar(20) Yes NULL field to store the tast the volunteer has to do
date_assigned datetime No field that stores the date assigned for a volunteer to work (yyyy-mm-dd hh:mm:ss)

vm_vol_skills

Field Type Null Default Links to Comments
p_uuid varchar(60) Yes NULL id unique for each volunteer
opt_skill_code varchar(100) Yes NULL field pertaining to the skill code for each skill type
status set('approved', 'unapproved', 'denied') No unapproved field to indicate if a volunteer has been approved, unapproved or denied to work in a project

vm_pos_volunteercount

Table comments: VIEW

Field Type Null Default Links to Comments
pos_id varchar(60) No a unique id for each position in a project
numVolunteers bigint(21) No 0 field to store count of volunteers under each position of a project

vm_position_active

FURTHER CLARIFICATION required regarding ptype_title and title fields

Table comments: VIEW

Field Type Null Default Links to Comments
pos_id varchar(60) No the id for a position in a project
proj_id varchar(60) No the id for the project
ptype_id varchar(60) No id for a position type
title varchar(30) No field to hold the title of the position
slots smallint(6) No field to store the target number of volunteers for the project
description text No field to textually describe the position
ptype_title varchar(20) Yes NULL field that stores the same data as description, used in connecting to other tables
ptype_description varchar(300) Yes NULL field that stores the same data as description, used in connecting to other tables
status set('active', 'retired') No active field to indicate if the project is active or not, always set to active in this table
skill_code varchar(20) Yes NULL field to store the unique code for each skill type

vm_position_full

Table comments: VIEW

Field Type Null Default Links to Comments
pos_id varchar(60) No the id for a position in a project
proj_id varchar(60) No the id for the project
ptype_id varchar(60) No id for a position type
title varchar(30) No field to hold the title of the position
slots smallint(6) No field to store the target number of volunteers for the project
description text No field to textually describe the position
ptype_title varchar(20) Yes NULL field that stores the same as
ptype_description varchar(300) Yes NULL field that stores the same data as description, used in connecting to other tables
status set('active', 'retired') No active field to indicate if the project is active or not, always set to active in this table
skill_code varchar(20) Yes NULL field to store the unique code for each skill type
payrate double Yes field to store the payrate for the position

vm_projects_active

Table comments: VIEW

Field Type Null Default Links to Comments
proj_id bigint(20) No 0 id which is unique for each project
name varchar(50) Yes NULL field to store the name of the project
location_id varchar(60) Yes NULL field that stores the location where the project is at
start_date date Yes NULL datestamp for the start date of the project (yyyy-mm-dd hh:mm:ss)
end_date date Yes NULL datestamp for the end date of the project (yyyy-mm-d hh:mm:ss)
description text No field that stores the description for the project
status set('active', 'completed') No active field to indicate if the project is active or not, always set to active in this table

vm_vol_active

Table comments: VIEW

Type Null Default Links to Comments
p_uuid varchar(60) No id unique for each volunteer
org_id varchar(60) No the o_uuid of the organization that the volunteer is affiliated with
photo blob No field that stores the image information for a volunteer
date_avail_start date No 2006-07-06 field that stores the information regarding the starting date for a volunteer (yyyy-mm-dd)
date_avail_end date No 2006-12-31 field that stores the information regarding the date a volunteer is available until (yyyy-mm-dd)
hrs_avail_start time No 09:00:00 field that stores the starting hours for a volunteer (hh:mm:ss)
hrs_avail_end time No 17:00:00 field that stores the hours a volunteer is available until (hh:mm:ss)
status set('active', 'retired') No active field to indicate if the project is active or not, always set to active in this table

vm_vol_assignment

Table comments: VIEW

Field Type Null Default Links to Comments
p_uuid varchar(60) No id unique for each volunteer
proj_id varchar(60) Yes NULL id which is unique for each project
pos_id varchar(60) No id for a position in a project
status set('active', 'retired') Yes active field to indicate if the project is active or not, always set to active in this table
payrate double Yes NULL field to store the payrate information for the position
hours bigint(20) Yes NULL field to store the number of hours put in by a volunteer
task varchar(20) Yes NULL field to store the tast the volunteer has to do
ptype_id varchar(60) Yes NULL id for the type of position
title varchar(30) Yes NULL field to store the title of the position
slots smallint(6) Yes NULL field to hold the number of target volunteers for a project
description text Yes NULL field stroing the textual description of the position
ptype_title varchar(20) Yes NULL field that stores the title of the type of position within a project
ptype_description varchar(300) Yes NULL field that stores the same data as description, used in connecting to other tables
skill_code varchar(20) Yes NULL field to store the unique code for each skill type
project_name varchar(50) Yes NULL field that stores the name of the project
project_description text Yes NULL field that stores the textual description of the project

vm_vol_assignment_active

Table comments: VIEW

Field Type Null Default Links to Comments
p_uuid varchar(60) No id unique for each volunteer
proj_id varchar(60) Yes NULL id which is unique for each project
pos_id varchar(60) No id for a position in a project
status set('active', 'retired') Yes active field to indicate if the project is active or not, always set to active in this table
payrate double Yes NULL field to store the payrate information for the position
hours bigint(20) Yes NULL field to store the number of hours put in by a volunteer
task varchar(20) Yes NULL field to store the tast the volunteer has to do
ptype_id varchar(60) Yes NULL id for the type of position
title varchar(30) Yes NULL field to store the title of the position
slots smallint(6) Yes NULL field to hold the number of target volunteers for a project
description text Yes NULL field stroing the textual description of the position
ptype_title varchar(20) Yes NULL field that stores the title of the type of position within a project
ptype_description varchar(300) Yes NULL field that stores the same data as description, used in connecting to other tables
skill_code varchar(20) Yes NULL field to store the unique code for each skill type
project_name varchar(50) Yes NULL field that stores the name of the project
project_description text Yes NULL field that stores the textual description of the project