Database Schema

Schema for PostgresQL using Sequelize ORM

Users

Attribute Name	Attribute Type	Constraints
id	int	not null, primary key
username	string	not null, unique, 20 chars or less
email	string	not null, unique, 255 chars or less
hashedPassword	string	not null, STRING.BINARY
createdAt	datetime	not null
updatedAt	datetime	not null

Model Associations:

• Sequelize User hasMany models.Review
• Sequelize User hasMany models.Tree
• Sequelize User belongsToMany models.Tree through ForestConnection join table, otherKey: 'treeId', foreignKey: 'userId' as forestTrees
• Sequelize User belongsToMany models.Tree through Review join table, otherKey: 'treeId', foreignKey: 'reviewerId' as reviewedTrees

Other details

• hashedPassword - type of STRING.BINARY in sequelize

Trees

Attribute Name	Attribute Type	Constraints
id	int	not null, primary key
name	string	not null, unique, 30 chars or less
cityState	string	not null, 40 chars or less
detLocation	text	not null, unique
coordinates	string	unique, 255 chars or less
description	text	not null
adderId	int	not null, references Users table
createdAt	datetime	not null
updatedAt	datetime	not null

Model Associations:

• Sequelize Tree belongsTo models.User
• Sequelize Tree hasMany models.Review
• Sequelize Tree belongsToMany models.User through ForestConnection join table, otherKey: 'userId', foreignKey: 'treeId' as forestUsers
• Sequelize Tree belongsToMany models.User through Review join table, otherKey: 'reviewerId', foreignKey: 'treeId' as reviewAuthors

Other details

• cityState is for the city and/or state the tree is in (or closest city and/or state)
• detLocation is for a detailed description of where it is
• avgDifficulty frontend representations: easy, medium, hard, danger
• avgFunFactor frontend representations: boring, okay, enjoyable, amazing
• avgViewFromTop frontend representations: dull, good, great, mesmerizing
• coordinates is a stretch goal

ForestConnections

Attribute Name	Attribute Type	Constraints
id	int	not null, primary key
climbStatus	boolean	not null
favStatus	boolean	not null, defaults false
userId	int	not null, references Users table
treeId	int	not null, references Trees table
createdAt	datetime	not null
updatedAt	datetime	not null

Model Associations

• This is the User - Tree join table, associations are taken care of in those associations

Other details

• climbStatus frontend representations: true = "climbed", false = "want to climb"
• Stretch goal: favStatus is available to toggle after a tree is in your forest
  • Frontend representations: true = "favorite", false = "no indication"

Reviews

Attribute Name	Attribute Type	Constraints
id	int	not null, primary key
reviewText	text
treeId	int	not null, references Trees table
reviewerId	int	not null, references Users table
difficulty	int	not null, 1 - 4
funFactor	int	not null, 1 - 4
viewFromTop	int	not null, 1 - 4
createdAt	datetime	not null
updatedAt	datetime	not null

Model Associations:

• Sequelize Review belongsTo models.User
• Sequelize Review belongsTo models.Tree

Other details

• difficulty frontend representations in form dropdown: easy, medium, hard, danger
• funFactor frontend representations in form dropdown: boring, okay, enjoyable, amazing
• viewFromTop frontend representations in form dropdown: dull, good, great, mesmerizing