Schema

Jump to bottom

wcorona269 edited this page May 30, 2022 · 24 revisions

Postgres Database Schema

`users`

column		data type		details
`id`		integer		not null, primary key
`first_name`		string		not null, indexed
`last_name`		string		not null, indexed
`dname` (display name)		string
`email`		string		not null, indexed, unique
`phone_number`		integer		not null, indexed, unique
`city_id`		integer
`password_digest`		string		not null
`session_token`		string		not null, indexed, unique
`created_at`		datetime		not null
`updated_at`		datetime		not null

has_many reviews
has_many reservations
has_many saved_rests (saved restaurants)
index on username, unique: true
index on email, unique: true
index on session_token, unique: true

`restaurants`

column		data type		details
`id`		integer		not null, primary key
`city_id`		integer		not null
`name`		string		not null, unique
`description`		string		not null
`cuisines`		string		not null
`price_range`		integer		not null
`address`		string		not null
`phone`		integer		not null
`latitude`		float		not null
`longitude`		float		not null
`created_at`		datetime		not null
`updated_at`		datetime		not null

has_many reviews
has_many reservations
validates :price_range, numericality: { in: 1..4 }

`photos`

column		data type		details
`id`		integer		not null, primary key
`rest_id`		string		not null, unique
`image_url`		string		not null

`reviews`

column		data type		details
`id`		integer		not null, primary key
`overall_rating`		integer		not null
`food_rating`		integer		not null
`service_rating`		integer		not null
`ambiance_rating`		integer		not null
`body`		string		not null
`author_id`		integer		not null, foreign key
`rsvp_id`		integer		not null, foreign key
`created_at`		datetime		not null
`updated_at`		datetime		not null

author_id and rsvp_id associate review to user and restaurant
index on rsvp_id, scope: author_id, unique: true
belongs to user
belongs to restaurant
validates :overall_rating, numericality: { in: 1..5 }
validates :food_rating, numericality: { in: 1..5 }
validates :service_rating, numericality: { in: 1..5 }
validates :ambiance_rating, numericality: { in: 1..5 }

`reservations`

column		data type		details
`id`		integer		not null, primary key
`user_id`		integer		not null, foreign key
`rest_id`		integer		not null, foreign key
`res_time`		integer		not null
`res_date`		date		not null
`party_size`		integer		not null
`created_at`		datetime		not null
`updated_at`		datetime		not null

index on user_id, scope: rest_id, unique: true
user_id and rest_id associate reservation to user and restaurant
party size specifies number of people in reservation
belongs to user
belongs to restaurant

`saved_restaurants`

column		data type		details
`id`		integer		not null, primary key
`user_id`		integer		not null, indexed, foreign key
`rest_id`		integer		not null, indexed, foreign key
`created_at`		datetime		not null
`updated_at`		datetime		not null

user_id references users
rest_id references restaurants
indexed on [user_id, rest_id], unique: true
belongs to user
belongs to restaurant