Proposal: Database Schema

[mfong4151]

Users Table

column name	type	details
user_id	int	primary key, auto_increment
type	enum	not null, values: 'admin', 'employer', 'apprentice'
first_name	varchar(255)	nullable (for admins and apprentices)
last_name	varchar(255)	nullable (for admins and apprentices)
company_name	varchar(255)	nullable (only for employers)
state	varchar(255)	not null
city	varchar(255)	not null
tech_stack	varchar(255)	nullable (used for apprentice, employers)
level	int	nullable (used for apprentices)

Issues Table

column name	type	details
issue_id	int	primary key, auto_increment
employer_id	int	foreign key, references Users(user_id)
title	varchar(255)	not null
description	text
tech_stack	varchar(255)

Subscribers Table

column name	type	details
subscription_id	int	primary key, auto_increment
employer_id	int	foreign key, references Users(user_id)
apprentice_id	int	foreign key, references Users(user_id)
subscription_date	datetime	not null

Here's my general initial proposal for the database. I imagine that Auth0 will take care of handling the username and password, so theres no need to actively store it.

This is mostly a follow up to my other issue, mainly to state that we will have a database that has joins so using a relational database seems to make sense to me. I don't know if it would be a better idea to separate users into a table for apprentices/admins and companies respectively. There are ways where that makes more sense to me, as we probably need to store some sort of reference to their Stripe account as well. And a company wouldn't have a first name or last name.