search

hackforla / peopledepot

A project to setup a datastore for people and projects at HackforLA. The link below takes you to the code documentation
https://hackforla.github.io/peopledepot/
GNU General Public License v2.0
7 stars 26 forks source link

Define tables data and uses #1

Open ExperimentsInHonesty opened 2 years ago

ExperimentsInHonesty commented 2 years ago

Overview

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

Action Items

Resources/Instructions

Civic Opportunity

Civic Tech Jobs

Hack for LA (hackforla.org) data storage

users of role data

Links to current versions of db

ExperimentsInHonesty commented 2 years ago
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, joe@springboard.com 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 commented 2 years ago

table to review with Ava

User Table nameFirst: String nameLast: String gmail: String (email validation) preferedEmail: createdDate: UserStatus: (see User Status Table) practiceAreaCurrent: (see Practice Area Table) jobTitleCurrent: user’s current professional/non-HfLA job practiceAreaTarget: (see Practice Area Table) - can choose multiple jobTitleTarget: String DesiredRoles user’s desired contributor roles at HfLA (structured and comes from the CivicTechJobs data) - Talk to Ava about it. CurrentSkills user’s current skills they want to use as a volunteer TargetSkills user’s target skills to acquire as a volunteer LinkedinAccount user’s public linkedin profile URL referredBy: (see Referrer Table) firstAttended: Date githubHandle: (make sure it does not include https://github.com/) phone: textingOk: Boolean slackID: Number (specific number of chars) isHflaGithubMember: Boolean github2FA: Boolean githubMembershipPublic: Boolean availability: Array of buckets - (see Brigade/GMT Time Bucket Table) This is translated into GMT on the way in. (see #13 (comment)) TimeZone: TimeZoneObj Conduct: Boolean GoogleOrgCheck: DateObj SlackOrgCheck: DateObj GitHubOrgCheck: DateObj Last Modified: DateObj Password: Hashed String
Community of Practice Area Table -We think we don't need it. copId: copName: Community of Practice Area Names Data Science DevOps Engineering Marketing Product Management Revenue UI/UX
Roles roleID roleName roleDescription roleResponsibilities roleQualifications created lastUpdated
Role and Community of Practice Join Table roleId projectId (limited to CoPs) dateCreated
ExperimentsInHonesty commented 2 years ago

outstanding questions

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

Neecolaa commented 2 years ago

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 commented 2 years ago

remaining items to talk to Ava about:

ExperimentsInHonesty commented 2 years ago

Tables I need to update

ExperimentsInHonesty commented 2 years ago

Nicole - practice area

David

Bonnie

ExperimentsInHonesty commented 2 years ago

@drubgrubby Review Alex's analysis and see if it informs how we should ask the questions or structure the database https://github.com/hackforla/civic-opportunity/issues/139

ExperimentsInHonesty commented 2 years ago

Current Status of project

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

ExperimentsInHonesty commented 2 years ago

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

drubgrubby commented 2 years ago

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

@ExperimentsInHonesty, we have => Questions:

Neecolaa commented 2 years ago

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 commented 1 year ago

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 commented 2 days ago

@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?