Database Schema

trails

Column Name	Data Type	Details
id	INTEGER	NOT NULL, PRIMARY KEY
name	STRING	NOT NULL
description	STRING
length	NUMERIC	NOT NULL
difficulty	DECIMAL	NOT NULL
state_id	INTEGER	NOT NULL, FOREIGN KEY
cross_state	BOOLEAN	NOT NULL
created_at	DATETIME	NOT NULL
updated_at	DATETIME	NOT NULL

• References state.id

states

Column Name	Data Type	Details
id	INTEGER	NOT NULL, PRIMARY KEY
state_code	STRING	NOT NULL
state_name	STRING	NOT NULL
created_at	DATETIME	NOT NULL
updated_at	DATETIME	NOT NULL

• Referenced by trails.state_id

users

Column Name	Data Type	Details
id	INTEGER	NOT NULL, PRIMARY KEY
username	STRING	NOT NULL, UNIQUE
email	STRING	NOT NULL, INDEX, UNIQUE
hashed_password	STRING, BINARY	NOT NULL
created_at	DATETIME	NOT NULL
updated_at	DATETIME	NOT NULL

• Referenced by collections.user_id

collections

Column Name	Data Type	Details
id	INTEGER	NOT NULL, PRIMARY KEY
user_id	INTEGER	NOT NULL, FOREIGN KEY
trail_id	INTEGER	NOT NULL, FOREIGN KEY
visited	BOOLEAN
want_to_visit	BOOLEAN
created_at	DATETIME	NOT NULL
updated_at	DATETIME	NOT NULL

• References users.id, trails.id

reviews

Column Name	Data Type	Details
id	INTEGER	NOT NULL, PRIMARY KEY
review	TEXT	NOT NULL
user_id	INTEGER	NOT NULL, FOREIGN KEY
trail_id	INTEGER	NOT NULL, FOREIGN KEY
created_at	DATETIME	NOT NULL
updated_at	DATETIME	NOT NULL

• References users.id, trails.id