Postgres Database Schema

users

column name	data type	details
id	Integer	not null, primary key
username	String	not null, unique, indexed
email	String	not null, unique, indexed
password_digest	String	not null
session_token	String	not null, unique, indexed
created_at	Datetime	not null
updated_at	Datetime	not null

• index on username, unique: true

• index on email, unique: true

• index on session_token, unique: true

• has_many :buggouts

• has_many :trustees

• has_many :liked_buggouts

• has_many :buggouts_liked

supply_route

column name	data type	details
id	Integer	not null, primary key
name	String	not null
start_latitude	Decimal	not null, :precision=>10, :scale=>6
start_longitude	Decimal	not null, :precision=>10, :scale=>6
favorite	Boolean	default: false
privacy	String	inclusion: in: public, private, colony
created_at	Datetime	not null
updated_at	Datetime	not null

• has_many: buggouts
• has_many: segments
• has_many: likes
• has_many: comments

segments

column name	data type	details
id	Integer	not null, primary key
supply_route_id	Integer	not null, foreign key, indexed
buggout_id	Integer	not null, foreign key, indexed
start_waypoint_id	Integer	not null, foreign key, indexed
end_waypoint_id	Integer	not null, foreign key, indexed

|created_at | Datetime | not null| |updated_at | Datetime | not null|

• index on supply_route_id, unique: true

• index on buggout_id, unique: true

• index on start_waypoint_id, unique: true

• index on end_waypoint_id, unique: true

• belongs_to: supply_route

• has_one: start_waypoint

• has_one: end_waypoint

waypoints

column name	data type	details
id	Integer	not null, primary key
latitude	Decimal	not null, :precision=>10, :scale=>6
longitude	Decimal	not null :precision=>10, :scale=>6
created_at	Datetime	not null
updated_at	Datetime	not null

• belongs_to: segment

buggouts

column name	data type	details
id	Integer	not null, primary key
scarab_id	Integer	not null, foreign key, indexed
transportation	String	inclusion: in: Foot, Bike, Car, ATV
description	String
security	Integer	inclusion: in: 1, 2, 3, 4, 5
privacy	String	inclusion: in: public, private, colony
created_at	Datetime	not null
updated_at	Datetime	not null

• index on scarab_id, unique: true

• belongs_to: user

• has_one: supply_route

• has_many: likes

likes

column name	data type	details
id	Integer	not null, primary key
bugout_id	Integer	not null, foreign key, indexed
liker_id	Integer	not null, foreign key, indexed
created_at	Datetime	not null
updated_at	Datetime	not null

• index on buggout_id, unique: true

• index on liker_id, unique: true

• belongs_to: route

• belongs_to: liker

comments

column name	data type	details
id	Integer	not null, primary key
buggout_id	Integer	not null, foreign key, indexed
commenter_id	Integer	not null, foreign key, indexed
body	String	not null
created_at	Datetime	not null
updated_at	Datetime	not null

• index on buggout_id, unique: true

• index on commenter_id, unique: true

• belongs_to: buggout

• belongs_to: commenter

Navigate (Joins Supply Route / Segment)

column name	data type	details
id	Integer	not null, primary key
supply_route_id	Integer	not null, foreign key, indexed
segment_id	Integer	not null, foreign key, indexed
created_at	Datetime	not null
updated_at	Datetime	not null

• index on supply_route_id, unique: true
• index on segment_id, unique: true