My First Schema

Okay, it’s not really my very first. It’s already been revised 2-3 times.

schema.png

Questions

  • Can I and/or should I remove the ArtistID column from the Tracks table? It’s already connected to an album which is connected to the artist.
  • Is dumping the artists, albums, and tracks all in a single Popularity table the best idea?
  • Or, should their be — forgive the newbie — a popularity table for each artist, album, and track?
  • Should I add a column that indicates whether each item is an artist, album, or track? Specifically, I’m wondering about the popularity table.
  • My big(gest) question is how do I best measure popularity over time — is doing it merely by adding rows to whatever version of a popularity table I create? Is it really that simple? I just want to compare the popularity of a track/album/artist over time and compare those numbers to other artists’ info.
  • Is this the correct and/or best way to store the popularity each day or week so that I can create some visualizations like bar or line graphs?

UPDATE:

And the final(?) schema as of December 15, 2017. I just added the genres table last night but below is how it’s been for a long time.

popRock_schema

Advertisements

About jotascript

Aiming to please. Seeking to impress.
This entry was posted in Databases. Bookmark the permalink.

3 Responses to My First Schema

  1. codeinfig says:

    what i truly hate about these things, is i will never be truly happy until i find a way to organize data without them. im stubborn that way.

    this doesnt really answer your question, but at least it sort of explains why not. its also an open comment to anyone that can suggest real alternatives, if they exist.

    Like

  2. Chris says:

    I’m numbering the questions to make it easier to track… 1) What if a track has multiple people performing on it, do you want to track them individually? Or does that “group” of people become an artist? (I don’t mean a band but a track could have a primary artist and a featuring artist) 2) Not saying this is the best option but what about just listing the track, date, and popularity? You then can use the track details to get artist or album details. 3) I think this answer follows along with #2 4) If you only show tracks in that table, this question goes away. 5) IMO, yes it is that easy. Just keep adding rows.

    Here is how I would lay it out: TracksOnAlbums and ArtistsOnTracks would be a “lookup” tables that are just foreign keys back to the applicable tables. Then you just need to store the popularity of a track. Which can be traced back to albums or artist.

    [image: pasted1]

    Like

    • jotascript says:

      I’m only tracking single artists. The primary artist of the album. Because of my end goal — what I’ll be tracking with the data — names of guest artists and the like aren’t relevant. I think I’m definitely going to create lookup tables. If for nothing else then for what tracks go on each album. Thanks for all your feedback and support.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s