Define tables data and uses

[ExperimentsInHonesty]

Overview

We need to define tables so that we can create an ERD for validation.

Action Items

• Review VRMS tables 0.3 & 0.4
• Review HackforLA data stores
• List tables needed
  • Add fields
  • Add accepted inputs
  • indicate where joined or association tables go
• Review VRMS and other data stores to identify if we used everything and annotate why we did not use some things.
  • Add document to resources

Resources/Instructions

• issue where we have been writing out all the tables API requirements CivicTechJobs#13

VRMS

• current VRMS data models
• VRMS 0.4 Minimum Viable Product (MVP) Requirement doc - contains proposed data models

Civic Opportunity

• repo with volunteer opportunity board post links and role descriptions

Civic Tech Jobs

• Civic Tech Jobs Figma

Hack for LA (hackforla.org) data storage

• projects
• program areas:
• communities of practice

users of role data

• hackforla.org
  • https://www.hackforla.org/projects/100-automations (Current Team)
  • https://www.hackforla.org/communities-of-practice
• civictechjobs.org
  • to provide generic details for each job post
  • to provide overview of roles to visitors
• VRMS
  • future TBD
• Guides
  • to be able to suggest guides for users

Links to current versions of db

• Spreadsheet where we are listing all the table names and fields
• Lucid chart image ERD
• lucid chart ERD

[ExperimentsInHonesty]

Project Table

projectname : Char/String
projectDescription : String
githubOrgId : number (8 characters)
githubPrimaryRepoId: number (9 characters)
githubOrgUrl : String URL
githubPrimaryUrl: String URL
currentProjectStatus: (see Status Table)
Hide: Boolean
location: string
createdDate: Date format
completedDate: Date format
lastModified:
statusHistory: (see Project Status Records Table)
slackUrl: URLString
googleDriveUrl
googleDriveId
partners: (see partner table)
programAreas: String (see program area table)
SDGs:
projectLeads: (see association table)
ProjectImageLogo: URL String // the 600x400 image for the project cards
ProjectImageHero: URL String // The image that appears on the project page
ProjectImageIcon: //a tiny image that is square for linking to the project from other sites)
leadershipType: (see Leadership Type Table)

Leadership Type

leadershipTypeId:
leadershipType:
leadershipTypeDescription:

Content for DB
#, Mentor Lead, 'Has a mentor in a leadership role'
#, Peer Led, 'Peers run the meetings'

Project URLs

projectId:
URLtypeID
URL:

URL types

URLtypeId
URLtypeName
URLtypeDescription

Content for DB
#, ReadmeUrL, (URL String), "The readme file on the project's repo"
#, hflaWebsiteUrl, https://www.hackforla.org/projects/100-automations, 'String URL (links to a specific page, such as https://www.hackforla.org/projects/100-automations)'
#, wiki, 'https://github.com/projectmame/wiki', 'A projects wiki'
#, testSite, 'https://test.example.com', 'a development site used for testing'
#, Demo Site, 'https://demo.example.com', A site used for public demonstration - usually with fake data. Primarily used by projects that don't have a live site yet.
#, Site, 'https://example.com', Live on the internet
#, Overview, '', 'The Project One sheet'
#, Other, '', ''

Project Status Records Table

projectId
changedDate
status

Project and Program Area Status

Active
OnHold
Completed
Closed

Partner and Sponsor Table

name
logo
link
active:
isSponsor

Partner Project Join Table

Many to many - some partners help with several projects and projects do have multiple partners

projectId
partnerId
dateCreated
dateEnded
isSponsor

Program Area Table

name:
description:
status:
image:

names for Program Areas are
Citizen Engagement
Civic Tech Infrastructure
Diversity / Equity and Inclusion
Environment
Justice
Social Safety Net
Vote / Representation
Workforce Development
Community of Practice

Recurring Events Schema

projectId: (number see Project Table)
name: String
location: (see location table)
type: (see event type table)
brigadeId: number
frequencyID:
startDate:
startTime:
durationInMin: Number
dateCreated:
dateLastUpdated:
videoConferenceUrl: URL String
mustRoles: Array of Role Ids (see Roles Table)
shouldRoles : Array of Role Ids (see Roles Table)
couldRoles: Array of Role Ids (see Roles Table)
additional information: String

Brigade ID Table

brigageId: Number
name: String
timeZone: String

Event type

teamMeeting
onboarding
mixer
specialEvent
communityOfPractice

User Status Table

Id, name, descriptionString
#, inactive, an inactive member is someone who has not checked into their project for 4 weeks and does not have a timeAwayHold
#, active, Any member that is checking into meetings
#, timeAwayHold, A hold placed by the member or their leader after they have announced a temporary absence
#, barred, A member who has been removed from the community

Brigade/GMT Time Bucket Table

BrigadeId, name, startTime, endTime
1, earlyMorning, 2:00PM, 5:00PM
1, morning, 5:00PM, 8:00PM
1, afternoon, 8:00PM, 1:00AM
1, evening, 1:00PM, 5:00AM

Association table (join table)

1 record for every permission granted example
Bonnie is on 10 teams, she has at least 10 records, more if she got upgraded permission for any of those teams. When permissions are upgraded, it creates a new record, and updates the old permission record to add date ended.
each record contains:
userId
projectId
permissionId (see Permission Table)
roleName (see Roles table)
dateGranted
dateEnded

Association History Table

All records that have dateEnded from the Association table will be moved here 1:12 Pacific Time

Permissions Table

permissionId
permissionName
permissionDescription
permissionResources

Content for DB
adminVrms
adminBrigade
adminProject
member

Referrer Table

referrerId:
name:
url:
category:
contactName:
contactEmail:

Example content:
Springboard, https://springboard.com, bootcamp, Joe, [email protected]
UCLA Cognitive Psychology
Friend
Mentor
Google
Meetup
Code for America
Code for All

Technologies Table

name:
description:
url:
logo: URL String
dateUpdated:
active: Boolean

Languages Table

name:
description:
url:
logo: URL String
dateUpdated:
active: Boolean

Technology Join Table

projectId:
technologyId:
dateCreated:
dateRemoved:

Languages Join Table

// this table might not be needed because we can pull the languages via the GitHub API. Disucss with each team if they need it.
projectId:
langugageId:
dateCreated:
dateRemoved:

SDGs Table

sdgId:
name:
description

SDGs Target Table

sdgTargetId:
sdgId:
name:
description:

Example
2, 2, 2.1, 'By 2030, end hunger and ensure access by all people, in particular the poor and people in vulnerable situations, including infants, to safe, nutritious and sufficient food all year round

2.1.1 Prevalence of undernourishment
2.1.2 Prevalence of moderate or severe food insecurity in the population, based on the Food Insecurity Experience Scale (FIES)'

SDGs Project Join Table

sdgId:
projectId:
createdDate
removedDate:

Accomplishments Table

accomplishmentId
title:
projectId
accomplishmentUrl
accomplishedOn:
description:
createdDate
updatedDate
hide:

Accomplishment Table

userId
teams: (see project table)
roles: (see roles table)
description; String
typesOfAccomplishment (see Accomplishment Type Table)
canUsePhoto: Boolean

Accomplishment Type Table

accomplishmentId
accomplishmentName
description
createdDate
updatedDate

Description Content for DB
I got a new job
I produced something for my portfolio
I improved my LinkedIn
I learned how to work better on a team
I increased the number of commits on my Github profile
I learned a new language
I set up 2FA
I worked on an enterprise project
I worked on a project that will help the people of Los Angeles
I worked on two or more projects
I taught or mentored a person on my team
I taught or mentored a person on another team
My team delivered software to a stakeholder
My team launched our MVP
Other:

Checkins Table

EventId (see Event Table)
UserId (see User Table)
dateTime date and time of check-in

Events

EventId
ProjectId: (see Project Table) //active project an event is a child of, if any (structured)
Description event’s sentence-length description
videoConferenceUrl: event’s Zoom/Google Meet/etc. Link (uses default link from recurring and can be changed)
Location (see location table)
altLocationName Name of event location
altAddress1 Address L1 of event location
altAddress2 Address L2 of event location
altLocCity City of event location
altLocState State of event location (structured)
altLocZip Zipcode of event location
Start time event’s date and time to start
durationOfMeetingIn Min
recurringEventId event’s join ID connecting it to recurring instances for future editing
creationDateTime time of event creation
checkInReady: Boolean // a script runs every 15 min to identify which events are ready to be checked into

Events History Table

Same schema as Events with data from events that have passed except for checkInReady field

Canceled Events

recurringEventId:
projectId:
dateToBeCanceled:
reason:
userID:

FAQ Table

faqId
question
answer
toolTipName
dateAdded
lastUpdated

FAQ viewed Table

faqId
projectID
Date

Location Table

locationId
name
address
Street 1
Street2
City
State
Zip
phone

Referrer Category Table

Bootcamp
College Career Center
Mentor
Friend
Active Volunteer
Inactive Volunteer

Frequency Table

frequencyId
frequencyType

Content for DB will be
daily
weekly
fortnightly
monthly
1st
2nd
3rd
4th
5th
1st & 3rd
2nd & 4th
last

[ExperimentsInHonesty]

outstanding questions

practiceAreaCurrent: (see Practice Area Table)
practiceAreaTarget: (see Practice Area Table) - can choose multiple

[Neecolaa]

ERD: https://lucid.app/publicSegments/view/7531ed46-77d2-4b91-a09c-b18f9f210980/image.png
How ERDs work / how to read one: https://www.lucidchart.com/pages/ER-diagram-symbols-and-meaning#section_1

Implied many-to-many relationships are connected using a pale line. These fields are arrays of IDs, which can't be restricted in the same way that foreign keys can.

The red warning marks are because some of the tables' fields aren't being recognized as filled. I'm not sure why some are and some aren't.

Needs editing: We have information for an Accomplishment table and an Accomplishments table. Currently, I've changed Accomplishment to Win on the ERD.

To-do:
Make table names consistent (some are singular while others are plural)
Make data types consistent (mostly consistency using varchar instead of string)
Make join table naming consisted (leaning towards join[table1]_[table2])
Figure out how to clear the red warning marks

[ExperimentsInHonesty]

Tables I need to update

• Brigade/GMT Time Bucket Table
  • new lookup table called dayofweek (with id, name, abbreviation)
  • new table called userAvailability (id, userid, dayofweekid, a column for each half hour - totalling 48)
• Frequency Table - add content to it
• SDGs Target - change to array for indicators
  • https://en.wikipedia.org/wiki/List_of_Sustainable_Development_Goal_targets_and_indicators#Goal_1._End_poverty_in_all_its_forms_everywhere
• Referrer Table
• Languages Join Table - pull nightly in order to combo languages from multiple github repos
• Accomplishment Type - decided to create a short name and change description to displayText

[ExperimentsInHonesty]

Nicole - practice area

• see https://www.bls.gov/oes/current/oes_stru.htm
• find data https://download.bls.gov/pub/time.series/oe/oe.occupation
• try to find all the descriptions for the lowest level jobs on the outline
• create table structure table
• populate with the data

David

• export vrms data of job titles

Bonnie

• SDGs
• annotating which tables I have created
• which tables I have populated
• Make a check type tab based on this page of the sheet

[ExperimentsInHonesty]

@drubgrubby Review Alex's analysis and see if it informs how we should ask the questions or structure the database
hackforla/civic-opportunity#139

[ExperimentsInHonesty]

Current Status of project

• Nicole
  • going to make the employment and wage statistics tables(employment categories and names tables) and show us something next time.
• David
  • the info from Alex and determined we need data about change in employment status including (job they have now, job they get during or post hfla). We will revisit this once Nicole creates the employment categories and name tables. Also, demographic information would be helpful, we will add that on v2 or later.
• Bonnie
  • SDGs
  • annotating which tables I have created
  • which tables I have populated
  • Make a check type tab based on this page of the sheet

Next agenda when Nicole finishes the tables is to do the following:

• Review the new tables
• Figure out how we are going to do mapping between modern titles and archaic government categories and sub categories

[ExperimentsInHonesty]

add fields to people depot recording if people visisted CTJ before onboarding, after onboarding

[drubgrubby]

Nicole and David met on this day, February 27, 2022

• Added user_employment_change table to track jobs that people get while or after HfLA.
• Added table modern_job_title to match the soc job id to a modern job title.

@ExperimentsInHonesty, we have =>
Questions:

• Are the users.practice_area_current and users.practice_area_target pulled from the soc tables?
• Are the users.job_tittle_current and users.job_title_target free-entered or pulled from a table/drop-down?
• Re: adding when they visit CTJ - Do we track when they first visit civic tech jobs? If so, we can use the date of their onboarding and the date they sign up for ctj to find out which they did first.

[Neecolaa]

Bonnie's answers to questions:

Are the users.practice_area_current and users.practice_area_target pulled from the soc tables?

No, we want to use more modern titles/categories.

Are the users.job_tittle_current and users.job_title_target free-entered or pulled from a table/drop-down?

Ideally, pulled from a dropdown with the option to select "other" and write in an answer.
Fang suggests we use something like this advanced example

Re: adding when they visit CTJ - Do we track when they first visit civic tech jobs? If so, we can use the date of their onboarding and the date they sign up for ctj to find out which they did first.

Account creation will track which site was used for account creation (CTJ, VRMS) as well as specific event if from VRMS.

[Neecolaa]

This was our initial issue for planning the structure of the new database. There are times where I come back to this issue in order to help figure out why something is structured the way it is.

That said, this issue can be closed after the still relevant data is brought to the wiki (if it's not already there). The current size label reflects the amount of time I believe it'll take to accomplish that.

[shmonks]

@Neecolaa - I'm creating an issue for updating the wiki. Once that's done, I wonder whether we should add that issue as a dependency to this one, move this to the Icebox (given your comment above, on Mar 24 2023), and then close once the wiki issue is completed?

[ExperimentsInHonesty]

We are closing this issue.