Database Schema - xiaoyuny/Airquq GitHub Wiki

users

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

• index on email, unique: true
• index on session_token, unique: true

listings

column name	data type	details
id	integer	not null, primary key
name	string	not null
host_id	integer	not null, foreign key, indexed
description	text	not null
price	float	not null
room_num	integer	not null
bed_num	integer	not null
bath_num	integer	not null
air_conditioning	boolean	not null
heating	boolean	not null
essentials	boolean	not null
wifi	boolean	not null
tv	boolean	not null
washer	boolean	not null
kitchen	boolean	not null
free_parking	boolean	not null
hair_dryer	boolean	not null
shampoo	boolean	not null
address	string	not null
city	string	not null
state	string	not null
country	string	not null
zipcode	string
lat	float	not null
lng	float	not null
timestamps	datetime	not null

• host_id references users
• index on host_id, city, country

bookings

column name	data type	details
id	integer	not null, primary key
listing_id	integer	not null, foreign key, indexed
guest_id	integer	not null, foreign key, indexed
checkin_date	date	not null
checkout_date	date	not null
guest_num	integer	not null
timestamps	datetime	not null

• listing_id references listings
• guest_id references users
• index on listing_id & guest_id

reviews

column name	data type	details
id	integer	not null, primary key
author_id	integer	not null, foreign key, indexed
listing_id	integer	not null, foreign key, indexed
body	text	not null
accuracy	integer
location	integer
communication	integer
check_in	integer
cleanliness	integer
value	integer
timestamps	datetime	not null

• author_id references users
• listing_id references listings
• index on author_id & listing_id

messages

column name	data type	details
id	integer	not null, primary key
sender_id	integer	not null, foreign key, indexed
receiver_id	integer	not null, foreign key, indexed
body	text	not null
timestamps	datetime	not null

• sender_id & receiver_id refenrece users
• index on sender_id & receiver_id