Database Schema

Diagram

Tables

User

Fields

• id integer [primary key]
• first_name string [not null]
• last_name string [not null]
• email string [not null, unique]
• password binary [not null]
• salt binary [not null]
• is_admin boolean [not null]

Client

Fields

• id integer [primary key]
• owner user [not null]
• creator user [not null]
• date_added timestamp [not null] -> generated on creation
• date_updated timestamp [not null] -> generated on creation and all future PUT requests
• name string [not null]
• email string
• phone_number string
• status enum('active', 'r_and_i', 'closed')
• closure_date timestamp
• status_at_exit enum('employed', 'training', 'no_results')
• status_at_3_months enum('employed', 'training', 'no_results')
• status_at_6_months enum('employed', 'training', 'no_results')
• status_at_9_months enum('employed', 'training', 'no_results')
• status_at_12_months enum('employed', 'training', 'no_results')

Job Lead

Fields

• id integer [primary key]
• owner user [not null]
• creator user [not null]
• title string [not null]
• employer Employer [not null]
• compensation_min integer
• compensation_max integer
• employment_type enum('Full Time', 'Part Time', 'Casual', 'On-Call')
• hours_per_week integer
• noc integer
• creation_date timestamp [not null]
• expiration_date timestamp
• num_of_positions integer [not null]
• job_description string [not null]

Employer

Fields

• id integer [primary key]
• owner user [not null]
• creator user [not null]
• date_added timestamp [not null] -> generated on creation
• name string [not null]
• legal_name string
• phone_number string
• fax string
• email string
• website string
• naics_code integer
• address string
• city string
• province string
• postal_code string
• secondary_address string
• secondary_city string
• secondary_province string
• secondary_postal_code string

Employer Contact

Fields

• id integer [primary key]
• name string [not null]
• email string [not null]
• job_title string
• phone_number string [not null]
• alt_phone_number string

Employer to Employer Contact

One-to-Many conjunction table

Fields

• id integer [primary key]
• employer Employer [not null]
• employer_contact EmployerContact [not null]

Employer to Job Leads

One-to-many conjunction table

Fields

• id integer [primary key]
• employer Employer [not null]
• job_lead JobLead [not null]

Timelines

Client

• id integer [primary key]
• date_added timestamp
• type enum('update', 'contact', 'placement', 'note') [not null]
• title string [not null]
• body string [not null]
• client client
• job_lead job_lead

Job Lead

• id integer [primary key]
• date_added timestamp
• type enum('placement', 'note') [not null]
• title string [not null]
• body string [not null]
• client client
• job_lead job_lead

Employer

• id integer [primary key]
• date_added timestamp [not null] -> generated on creation
• type enum('contact', 'job', 'placement', 'note') [not null]
• title string [not null]
• body string [not null]
• employer_contact employer_contact
• client client
• job_lead job_lead

Job Posts

Fields

• id integer [primary key]
• title string [not null]
• employer string
• location string
• hours_per_week int
• rate_of_pay_min decimal(10,0)
• rate_of_pay_max decimal(10,0)
• rate_of_pay_frequency enum('Hourly','Weekly','Annually','Commission','Base_Commission')
• job_type json
• close_date date_time
• job_description text
• custome_questions json
• creator int [not null]
• state enum('Draft','Active','Inactive') [not null] (Default-> Draft)
• createdAt datetime [not null] -> generated on creation
• updatedAt datetime [not null] -> generated on creation