Database Schema

[heyhuyen]

Task: Design the database schema, keeping in mind the optional and bonus features.

[heyhuyen]

Tables

user story story_place story_place_item story_favorite friend (mapping of friendships) shared (mapping of story to shared users) tag (mapping of user to tagged content)*

• These are purely for relationship mappings. I think MongoDB (what Parse uses) supports lists, in which case just imagine for example instead of a friends table, each user has a friends column with a list of ids. Parse has ParseRelation for mappings as well. Will explore that further when implementing the models.

user

Columns

id - primary key, auto-increment (int/long) uid - unique user id from Facebook auth (int/long) name - user’s name (string) username - screen name/handle for tagging (string) profile_pic_url - url to user profile pic (string) cover_pic_url - url to user cover photo (string)

Relationships

• A user has 0 to many stories
• A user has 0 to many friends
• A users has 0 to many stories shared with them by other users
• A user has 0 to many tags (other users tagged this user in something)

story

Columns

id - primary key, auto-incrementing (int/long) user_id - foreign key to user table (int/long) title - story title (string) cover_pic_url - url to story cover photo (string) destination_place_id - the API place id (i.e. https://developers.google.com/places/android-api/place-details) (string) start_date - trip’s start date (timestamp/string) end_date - trips end date (timestamp/string)

Relationships

• A story belongs to a user
• A story has 0 to many story places
• A story has 0 to many story favorites (optional)
• A story can be shared with 0 to many other users
• A story can have 0 to many tagged users

story_place

This is a place that is saved to a story, not to be confused with the “place” we get from the API

Columns

id - primary key, auto-incrementing (int/long) place_id - API place id (string) story_id - foreign key to story table (int/long) name - name of place (string) cover_pic_url - url to cover photo (string) rating - user’s rating of this place (int) latitude - latitude (double) longitude - longitude (double) type - category type (int/string) order_position - the order this place appears in the story; (by default we’ll just use the id) (int)

Relationships

• A story place belongs to a story
• A story place has an associated API place id
• A story place has 0 to many story place items
• A story place can have 0 to many tagged users

story_place_item

any text + media items associated with a story place

Columns

id - primary key, auto-incrementing (int/long) story_place_id - foreign key to story_place table (int/long) type - TEXT, REVIEW, PHOTO, VIDEO (string) caption - text (string) data_url - if applicable, url to photo, video, or review submitted to API (string)

Relationships

• A story place item belongs to story place
• A story place item can have 0 to many tagged users

story_favorite

optional, these are places the user has bookmarked for their story but not added

Columns

id - primary key, auto-incrementing (int/long) place_id - API place id (string) story_id - foreign key to story table (int/long)

Relationships

• A story favorite belongs to a story
• A story favorite has an associated API place id

friend*

1 to 1 mappings of friendships id - primary-key, auto-increment (int/long) user_id - foreign key to user table (int/long) friend_id - foreign key to user table (int/long)

shared*

1 to 1 mappings of a story shared with a user id - primary-key, auto-increment (int/long) story_id - foreign key to story table (int/long) friend_id - foreign key to user table (int/long)

tag*

id - primary-key, auto-increment (int/long) story_id - foreign key to story table (int/long) type - story, story_place, story_place_item content_id - story_id, story_place_id, story_place_item_id

[heyhuyen]

A drawing, if it helps:

[heyhuyen]

@adikakade31 @prav90: let me know what you think!

[adikakade31]

It looks good. Does this schema adhere to what Google Places API provide? Also, is story_place good enough to contain all types categories or should we have separate table for different categories and just a reference of it in story table?

[heyhuyen]

I think we only need to store a few things from the Google Places API. I was looking at this for reference. Basically, I think we'll just need the following from the Place object:

• the name
• the "place id"
• the lat/long values
• categories (they have a bunch defined [here])(https://developers.google.com/android/reference/com/google/android/gms/location/places/Place)

[prav90]

@heyhuyen Looks great. A user can favorite stories too right? (Think of a tab like my favorite stories/trips). Can we rename the friends relation to followers & following? (This is similar to the demo which aditi shared of the travel journal app someone did last year)

Add a visited_time/checked_in time field to the story_place table. When we shown the story view, we might end up showing it like a timeline based on the visited time. The order_position field helps but it's also better to have the time of visit, just in case.

My thoughts on the naming, I guess we can name the Story table as Trip. A trip becomes a story when it's complete. Can we rename the tables to be like,

Story -> Trip Story places -> places Story place item -> media (text/video/image) Story favorite -> favorite places

Having a lot of story prefix's on the model is kind of confusing :) Thoughts? @adikakade31

[adikakade31]

Yes I agree on having a visited_time field which we can use to represent the story as sequence of events in timeline. Lets talk more on database schema this evening :)

[heyhuyen]

Closing this since we discussed last night. Feedback will be incorporated in: https://github.com/codepath-android-fall16-group-8/travel/issues/5