2_4_Database schema, migration and model

VMS backend uses a relational database, like MySQL and MariaDB for storing data.

Database Schema and Migration

The database schema defines in database/migrations/ (the directory). It helps create, drop, alter database tables by Laravel Database Migration.

For example, The volunteers table is created by database/migrations/2015_09_15_055905_create_volunteers_table.php. The up() function contains the columns, types and relation in volunteers table.

Model

Model implements by Laravel Eloquent ORM for manipulating the database. Each table has a corresponding Model which MUST inherits Illuminate\Database\Eloquent\Model and MAY defines the relationship with other models/tables. Models exists in app/*.php, like app/Volunteer.php

Volunteer (or User) Model

Purpose

The Volunteer model represents a volunteer. It also stores user's credentials including username and hashed password for authentication. The is_actived and is_locked mean that the volunteer was verified by email or locked.

Relationships

• City: The volunteer lives in a city.
• VerificationCode: The verification code belongs to the volunteer for email verification.
• Skill: The volunteer has skills.
• Education: The volunteer's educations.
• Experience: The volunteer's experience.
• Project: There are two relationships with Project including that the project is managed by the volunteer and the volunteer attends the project.

Project Model

Purpose

The Project model represents a crowdsourcing project which project managers (Volunteer model) manage and volunteers attend.

It includes the following main fields:

• name and description: It provides useful information for volunteers.
• is_published: It controls if the project is viewable by volunteers or not.
• permission: It defines whom are able to view the project.

Relationships

• Hyperlink: The hyperlinks are shown in the project for volunteers.
• Volunteer: There are two relationships with Volunteer model including project managers and project members. They are defined in managers() and members().
• ProjectCustomField: VMS makes project managers create custom fields in the project for recording more information about project members flexibly.
• MemberCustomFieldData: Project members input the custom field data in the project. The relationship with MemberCustomFieldData is through ProjectCustomField by foreign keys.

ProjectCustomField Model

Purpose

The ProjectCustomField model represents a custom field in a project. It includes the following main fields:

• name: The custom field name displays for volunteer's.
• description: The description of the custom field.
• required: It defines if the custom field is mandatory or not.
• type: The custom field type is defined in custom_field_type in config/constants.php and the value MUST be one of the custom_field_type.*.number
• is_published: It controls the visibility of the custom field.
• metadata: It stores the serialization of the custom_field_type.*.metadata. For example, the App\CustomField\RadioButtonMetadata class provides the options in radio button.
• order: The sequence of the custom field.

Relationships

• Project: It defines that the custom field belongs to Project model.
• MemberCustomFieldData: It defines the relationship between data from that project members input and custom field.

MemberCustomFieldData Model

Purpose

The MemberCustomFieldData model represents project members' input data.

The main fields include:

• data: The input data from project member is serialized via Payload class.

Relationships

• ProjectCustomField: Each MemberCustomFieldData model corresponds to ProjectCustomField model. It represents the meaning of the data.
• ProjectMember: The relationship represents that the data belongs to project member.

ApiKey Model

Purpose

Relationships