2_4_Database schema, migration and model - OpenISDM/VMS GitHub Wiki

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