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)

Belongs to User and Vote

user_id
vote_id
chat : text
created_at
*updated_at

Choice (Vote-Restaurant)

Belongs to User and Restaurant

  • restaurant_id
  • user_id
  • count

Rating (User-Restaurant)

Belongs to User and Restaurant

  • restaurant_id
  • user_id
  • rating