Basecamp Schema

Basecamp Schema

users

column name	data type	details
id	bigint	not null, primary key
first_name	string	not null
last_name	string	not null
email	string	not null, indexed, unique
zipcode	integer
session_token	string	not null, indexed, unique
password_digest	string	not null
created_at	datetime	not null
updated_at	datetime	not null

• index on email, unique: true
• index on session_token, unique: true
• has_many reviews
• has_many bookings
• has_one hosted_campground

campgrounds

column name	data type	description
id	bigint	not null, primary key
name	bigint	not null, indexed, unique
location	string	not null
state	string	not null
description	text	not null
num_sites	integer	not null
num_cabin	integer	not null
num_rv	integer	not null
num_tent	integer	not null
activities	array	not null
natural_feat	array	not null
create_at	datetime	not null
updated_at	datetime	not null

• indexed on name
• has many campsites
• location signifies the area/city the campground belongs to ("Yosemite" "Napa", etc)
• activities is an array that can only include certain values ("biking", "boating", "fishing", etc)

campsites

column name	data type	**description **
id	bigint	not null, primary key
host_id	bigint	not null, indexed, foreign key
campground_id	bigint	not null, indexed, foreign key
name	string	not null
description	string	not null
price	float	not null
latitude	float	not null
longitude	float	not null
num_sites	integer	not null
max_guests	integer	not null
min_nights	integer	not null
cabin	boolean	not null
rv_park	boolean	not null
tree_house	boolean	not null
parking	boolean	not null
toilets	boolean	not null, default: false
pets	boolean	not null, default: false
wifi	boolean	not null, default: false
campfire	boolean	not null, default: false
showers	boolean	not null, default: false
potable_water	boolean	not null, default: false
kitchen	boolean	not null, default: false
garbage	boolean	not null, default: false
picnic_table	boolean	not null, default: false
activities	array	not null
checkin_time	string	not null
checkout_time	string	not null
created_at	datetime	not null
updated_at	datetime	not null

• host_id references users
• review_ids references reviews
• index on host_id
• has_many reviews
• has_many bookings
• belongs_to user
• checkin_time and checkout_time are times of day users can check in/out ("after 2pm", "before 11am")

bookings

column name	data type	**description **
id	bigint	not null primary key
campsite_id	bigint	not null, indexed, foreign key
user_id	bigint	not null, indexed, foreign key
num_guests	integer	not null
price	float	not null
checkin_date	datetime	not null
checkout_date	datetime	not null
created_at	datetime	not null
updated_at	datetime	not null

• campsite_id references campsite
• user_id references users
• index on campsite_id
• index on user_id
• belongs_to campsite
• belongs_to user

reviews

column name	data type	description
id	bigint	not null, primary key
author_id	bigint	not null, indexed, foreign key
campsite_id	bigint	not null, indexed, foreign key
title	string	not null, indexed
body	text	not null
recommended	boolean	not null
created_at	datetime	not null
updated_at	datetime	not null

• author_id references users
• campsite_id references campsites
• indexed on campsite_id, title
• indexed on [:user_id, :campsite_id], unique: true
• belongs_to user
• belongs_to campsite