Database design

[oscarb]

The database needs to be designed to efficiently support queries such as:

• Get all non-archived links sent to a user
• Get all non-archived links sent to a user tagged with listen
• Get information about the link, such as who shared it, it's title and URL
• Get users by hashed phone number
• Sharing the same link with multiple users
• Store different kinds of meta-data for different links (e.g: artist for music links)

Less priority but still of value would be the possibility to:

• Get all links a user shared with other users
• Sort archived links after time of archival
• Add and see comments/reactions for a link (in the scope of the users friends)

[oscarb]

Relational database

Suggested tables for a relational database

User

id	phoneNumberHash

Share

id	timestamp	fromUser	linkId

User_Share

id	shareId	toUser	category

Archive

id	shareId	toUser	category	archivedTime

Link

id	url	title	suggestedCategory	hits

AudioLink

id	type	artist	duration	provider

VideoLink

id	type	channel	duration	provider

ArticleLink

id	type	author	readTime	provider

[oscarb]

NoSQL

Suggested structure for JSON documents.

User

{ id: 1,
  phoneNumberHash: 'a7fc6d998e889acf779',
  links: [ 
     {linkId: '54', sharedByUser: 5, timestamp: 4657894},
     {linkId: '37', sharedByUser: 8, timestamp: 1325489}
   ],
   archivedLinks: [ 
     {linkId: '12', sharedByUser: 5, timestamp: 4657894, archivedTime: 7894563},
     {linkId: '78', sharedByUser: 9, timestamp: 8798453, archivedTime: 4654213}
   ],
   sharedLinks: {
      {linkId: '12', timestamp: 4657894, sentToUsers: [{userId: 7}, {userId: 9}, {userId=23}]}
   ]
}
...

Link

{ id: 12,
  title: "Epic fails of summer 2016",
  url; "https://youtube.com/watch?v=4a5a3c45d",
  videoDuration: 64,
  provider: 'YouTube',
  suggestedCategory: 'watch'
  hits: 455
}
...