Skip to content

Schema

Jump to bottom
Kathryn Chu edited this page Mar 3, 2019 · 15 revisions

Database Schema

users

column name data type details
id integer not null, primary key
username string not null, indexed, unique
password_digest string not null
session_token string not null, indexed, unique

Database Constraints / Details

  • index on username, unique: true
  • index on session_token, unique: true

Associations

  • has_many owned_playlists

  • has_many album_saves

  • has_many artist_saves

  • has_many song_saves

  • has_many playlist_saves

  • has_many saved_playlists through playlist_saves (source: playlist)

  • has_many saved_songs through song_saves (source: song)

  • has_many saved_artists through artist_saves (source: artist)

  • has_many saved_albums through album_saves (source: album)

  • has_many owned_playlist_songs through owned_playlists (source: playlist_songs)

  • has_many followees

  • has_many followers

artists

column name data type details
id integer not null, primary key
name string not null, indexed

Database Constraints / Details

  • index on name

Associations

  • has_many albums
  • has_many songs
  • has_many artist_saves

songs

column name data type details
id integer not null, primary key
title string not null, indexed
album_id integer not null, indexed, foreign key
artist_id integer not null, indexed, foreign key
duration integer not null

Database Constraints / Details

  • album_id references albums
  • artist_id references artists
  • index on album_id
  • index on artist_id
  • index on title
  • duration will be length of song in seconds

Associations

  • belongs_to album
  • belongs_to artist
  • has_many song_saves
  • has_many playlist_songs

albums

column name data type details
id integer not null, primary key
title string not null, indexed
artist_id integer not null, indexed, foreign key
genre string

Database Constraints / Details

  • artist_id references artists
  • index on artist_id
  • index on title
  • genre is optional for now

Associations

  • belongs_to artist
  • has_many songs
  • has_many album_saves

playlists

column name data type details
id integer not null, primary key
name string not null, indexed
owner_id integer not null, indexed, foreign key

Database Constraints / Details

  • owner_id references users
  • index on name
  • index on owner_id

Associations

  • belongs_to owner
  • has_many playlist_saves
  • has_many playlist_songs
  • has_many songs through playlist_songs (source: song)
  • has_many artists through songs (source: artist)
  • has_many albums through songs (source: album)
  • has_many playlist_followers through playlist_saves (source: user)

The following tables are joins tables.

playlist_songs

column name data type details
id integer not null, primary key
playlist_id integer not null, indexed, foreign key
song_id integer not null, indexed, foreign key

Database Constraints / Details

  • playlist_id references playlists
  • song_id references song
  • index on playlist_id
  • index on song_id

Associations

  • belongs_to playlist
  • belongs_to song

playlist_saves

column name data type details
id integer not null, primary key
playlist_id integer not null, indexed, foreign key
user_id integer not null, indexed, foreign key

Database Constraints / Details

  • playlist_id references playlists
  • user_id references user
  • index on playlist_id
  • index on user_id

Associations

  • belongs_to playlist
  • belongs_to user

song_saves

column name data type details
id integer not null, primary key
song_id integer not null, indexed, foreign key
user_id integer not null, indexed, foreign key

Database Constraints / Details

  • song_id references songs
  • user_id references user
  • index on song_id
  • index on user_id

Associations

  • belongs_to song
  • belongs_to user

artist_saves

column name data type details
id integer not null, primary key
artist_id integer not null, indexed, foreign key
user_id integer not null, indexed, foreign key

Database Constraints / Details

  • artist_id references artists
  • user_id references user
  • index on artist_id
  • index on user_id

Associations

  • belongs_to artist
  • belongs_to user

album_saves

column name data type details
id integer not null, primary key
album_id integer not null, indexed, foreign key
user_id integer not null, indexed, foreign key

Database Constraints / Details

  • album_id references albums
  • user_id references user
  • index on album_id
  • index on user_id

Associations

  • belongs_to album
  • belongs_to user

friend_follows

column name data type details
id integer not null, primary key
followee_id integer not null, indexed, foreign key
follower_id integer not null, indexed, foreign key

Database Constraints / Details

  • index on followee_id
  • index on follower_id
  • followee_id references users
  • follower_id references users

Associations

  • Joins table: a user can have many followers and a user can follow many followees
  • belongs_to followee
  • belongs_to follower
Clone this wiki locally