Database Schema - maxwelljmckee/scare_bnb GitHub Wiki

Schema for PostgreSQL using SQLAlchemy and Alembic

users

column name data type constraints
id integer primary key, not null
last_name varchar(50) not null
first_name varchar(50) not null
bio varchar(300)
email varchar(50) unique
hashed_password varchar(6-20) not null
profile_pic_url varchar(500)
is_host boolean not null
created_at datetime not null
updated_at datetime not null
  • unique index on email
  • SQLAlchemy has_many houses relationship
  • SQLAlchemy has_many reviews relationship
  • SQLAlchemy has_many bookings relationship

houses

column name data type constraints
id integer primary key, not null
host_id integer foreign key, not null
name varchar(250) unique, not null
street_1 varchar(100) not null
street_2 varchar(100)
city varchar(50) not null
state_id integer foreign key,not null
postal_code varchar(5) not null
house_pic_url varchar(500)
description text not null
max_guests integer not null
num_bedrooms integer not null
num_beds integer not null
num_baths integer not null
price integer not null
smoking boolean not null
pets_allowed boolean not null
wifi boolean not null
created_at datetime not null
updated_at datetime not null
  • unique index on name
  • SQLAlchemy belongs_to users relationship (fkey owner_id)
  • SQLAlchemy belongs_to states relationship
  • SQLAlchemy has_many reviews relationship
  • SQLAlchemy has_many bookings relationship
  • SQLAlchemy belongs_to_many tags relationship via house_tags

states

column name data type constraints
id integer primary key, not null
state_name varchar(2) unique, not null
created_at datetime not null
updated_at datetime not null
  • unique index on state_name
  • SQLAlchemy has_many houses relationship

house_tags

column name data type constraints
id integer primary key, not null
house_id integer foreign key, not null
tag_id integer foreign key, not null
created_at datetime not null
updated_at datetime not null
  • SQLAlchemy belongs_to houses relationship
  • SQLAlchemy belongs_to tags relationship

tags

column name data type constraints
id integer primary key, not null
tag_name varchar(20) unique, not nul
created_at datetime not null
updated_at datetime not null
  • unique index on tag_name
  • SQLAlchemy belongs_to_many houses relationship via house_tags

bookings

column name data type constraints
id integer primary key, not null
house_id foreign key, integer not null
guest_id foreign key, integer not null
checkin datetime not null
checkout datetime not null
num_guests integer not null
created_at datetime not null
updated_at datetime not null
  • SQLAlchemy belongs_to houses relationship
  • SQLAlchemy belongs_to users relationship

reviews

column name data type constraints
id integer primary key, not null
house_id integer foreign key, not null
user_id integer foreign key, not null
rating integer(1-5) not null
comment varchar(500) not null
created_at datetime not null
updated_at datetime not null
  • SQLAlchemy belongs_to houses relationship
  • SQLAlchemy belongs_to users relationship