Exericse: Newsletter Spy

2021-09-03

Newsletter Spy is a database of more than 100.000 newsletters.

Each newsletter has a title, a description, an author's name with his email address and a URL. A newsletter is written in a specific language and belongs to a category and an industry. Main information Main information 2

Newsletter Spy does als collect meta information per newsletter:

the type of Email Service Provider (ESP) like Ghost, ConvertKit or MailerLite which is used
The fame ranking between "High", "Medium" and "Low"
The buzz ranking between "High", "Medium" and "Low"

Each newsletter has number of issues. An issue can be either of type newsletter, podcast or thread. A newsletter issue has a date and an optional title. For issues of type podcast there can be also a duration with the length of the podcast.

Issues

Other meta data like Number of comments, Number of reactions and Paid ratio are not part of this exercise.

Solution

Schema

Diagram

Table author {
  id integer [primary key, not null, unique]
  name char(255) [not null]
  email char(255) [null]
}

Table newsletter {
  id integer [primary key, not null, unique]
  author_id integer [not null, ref: > author.id]
  email_service_provider_id integer [not null, ref: > email_service_provider.id]
  category_id integer [not null, ref: > category.id]
  industry_id integer [not null, ref: > industry.id]
  title char(255) [not null]
  description text [null]
  fame enum('high', 'medium', 'low') [not null]
  buzz enum('high', 'medium', 'low') [not null]
}

Table issue {
  id integer [primary key, not null, unique]
  newsletter_id integer [not null, ref: > newsletter.id]
  title char(255) [not null]
  created_at date [not null]
  type enum('newsletter', 'podcast', 'thread') [not null]
  // in seconds; application would required the duration if type == 'podcast'
  podcast_duration integer [null]
}

Table email_service_provider {
  id integer [primary key, not null, unique]
  name char(255) [not null, unique]
  icon char(255) [null]  
}

Table category {
  id integer [primary key, not null, unique]
  name char(255) [not null, unique]
}

Table industry {
  id integer [primary key, not null, unique]
  name char(255) [not null, unique]
}