DB Scheme for Who’s Hungry?

There’s two strong entity, User, Restaurant.
Any other tables are weak entities.

Abstract

User table hold data of user, including it’s Facebook id, name, profile picture, contact number. Device table belongs to User table, and saves User’s device data in case user has many devices, mainly exists for push notification. Session table exists for security issue, belongs to User table. Location table exists for saving user’s location, reason this table is separated is, for encrypting issue. Group table exists to re-use the group that people made. Crew table exists holding data between User and Group, for N to N relationship. Vote table is for every single information of votes besides information of restaurant. Choice table holds data between Restaurant and Vote table, for counting issue. (to be updated)

User
Device
Restaurant
Group
Vote
Location
Auth
Subscription
Crew
Rsvp
Chat
Choice
Rating

User

id : PK
fb_id : string indexed Facebook_id*
name : string Name of the user
picture : string URL of the user’s profile picture
contact : string indexed Phone number of the user*
created_at : datetime
updated_at : datetime

Device

Belongs to User

id : PK id of device in our server
os_type : string {ios, android, web} default : web
push_id : string push_id
(user_id)
created_at
updated_at

Restaurant

id : PK id of restaurant in our server, not Google Place API
place_id : string indexed id of restaurant in Google Place API
lat : float Latitude of the restaurant location
lng : float Longitude of the restaurant location
name : string Name of the restaurant
rating : float Rating of the restaurant
created_at
updated_at

Group

id : PK
lead_user_id : user_id Leader of the group
name : string NULL
created_at
updated_at

Vote

id : PK
(group_id) :
type : string {lunch, dinner, cafe, bar}
name : string Name of the vote
expires_in : integer Expires in # minute
winner: restaurant_id FK to restaurant(id)
expires_at : datetime
created_at
updated_at

Location

id : PK
(user_id)
lat : float
lng : float

Auth

Belongs to User

user_id
login_type
token
created_at
updated_at

Subscription (User-User)

Use to look up mutual friends, invitation, etc..
Recursive relations between User

id : PK
friend_from : user_id indexed
friend_to : user_id

Crew (User- Group)

Belongs to User and Group

id
user_id
group_id

Rsvp (User-Vote)

Belongs to User and Vote

id
user_id
vote_id
rsvp : integer {1: going, -1: not going, 0: Dunno}

Chat (User-Vote)