SQL Prototype | New Music App - Rae Soria SQL Music Database Prototype | Rae Soria

SQL Music Database Prototype | Case Study - Rae Soria App

A simple SQL prototype is built to show how a music catalog can be organized more clearly than a spreadsheet when songs, artists, albums, and genres start repeating across rows. This project started with a spreadsheet that looked simple on the surface but became difficult to work with once the same objects started appearing over and over again. The same artist could show up on multiple rows.... The same genre could show up under different songs. Album titles were being repeated, song titles could exist in more than one version due to covers or remixes, etc.

That kind of spreadsheet may be fine for a rough draft, but it becomes frustrating the moment you want to sort the data, update it accurately, or turn it into something customers can actually use. A music app needs structure. It needs to know which song belongs to which album, which album belongs to which artist, and which genre is tied to each track.

Our goal for the client is to let users buy songs individually instead of paying for an entire album when they only want one or two tracks. For example if someone only wants to buy the song "Good Ridance" by Green DAy, they can opt to just buy the song and it is theirs to use for as long as they own the account regardless of payment status, account status, etc. For them this might be more practical as they didn't like the remainder of songs on that album. In any case, they are likely lookin for that specific version, and our software will need to be intuititve enough that they only have to search for it once.

We used SQL to solve the problem by splitting one messy spreadsheet into a set of organized tables (note, I said TABLES, not just table.) Instead of repeating artist names, album titles, and genre labels over and over again, we store them once and connect them using virtual IDs or identifiers. It's similar to when you sign up for Netflix and use your email address - you cannot make a second account using that same email address. That's identifier. It makes the data easier to manage, easier to search, and as developers this makes it easier to scale as our catalog grows.

Okay so why not just use the spreadsheet instead of SQL?

A lot of reasons...

The same artist could appear many times across different songs.
The same genre could be repeated across many rows.
The same album title could be tied to multiple tracks.
Different versions of songs could create confusion if titles were treated as truly unique.
Updating or sorting the data became harder as the list expanded.
When we get really deep into the weeds, data will take longer to process if we don't figure out how to organize the data in a short amount of time, with multiple sorting and grouping methods. Think about any of the times you've been looking for a t-shirt on Amazon, and you have wanted the blue version, size small. You can quickly filter the results with blue, size small. You can also specify what brand. (Granted, queries can be slow depending on how the data is structured and how much of it there is.)

To make the structure more reliable, SQL creates separate tables for artists, albums, genres, and tracks but also creates an intuitive catalog. The track table acts as the center of the catalog because the business idea revolves around selling songs individually. In practice, the database uses a numeric track ID as the real unique identifier, which is safer than relying on song title alone.

My plain code prototype (no bells and whistles, just code)

Below is the SQL prototype in plain form. It creates the database, builds the tables, inserts the sample data, and shows a few join queries used to pull information back out.

CREATE DATABASE Music DEFAULT CHARACTER SET utf8mb4;

USE Music;

CREATE TABLE Artist (
  artist_id INTEGER NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255),
  PRIMARY KEY(artist_id)
) ENGINE = InnoDB;

CREATE TABLE Album (
  album_id INTEGER NOT NULL AUTO_INCREMENT,
  title VARCHAR(255),
  artist_id INTEGER,

  PRIMARY KEY(album_id),
  INDEX USING BTREE (title),

  CONSTRAINT FOREIGN KEY (artist_id)
    REFERENCES Artist (artist_id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB;

CREATE TABLE Genre (
  genre_id INTEGER NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255),
  PRIMARY KEY(genre_id)
) ENGINE = InnoDB;

CREATE TABLE Track (
  track_id INTEGER NOT NULL AUTO_INCREMENT,
  title VARCHAR(255),
  len INTEGER,
  rating INTEGER,
  `count` INTEGER,
  album_id INTEGER,
  genre_id INTEGER,

  PRIMARY KEY(track_id),
  INDEX USING BTREE (title),

  CONSTRAINT FOREIGN KEY (album_id) REFERENCES Album (album_id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FOREIGN KEY (genre_id) REFERENCES Genre (genre_id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB;

INSERT INTO Artist (`name`) VALUES ('The Toxic Avenger');
INSERT INTO Artist (`name`) VALUES ('Juelz');
INSERT INTO Artist (`name`) VALUES ('OVERWERK');

INSERT INTO Genre (`name`) VALUES ('EDM');
INSERT INTO Genre (`name`) VALUES ('Trap');
INSERT INTO Genre (`name`) VALUES ('Experimental');
INSERT INTO Genre (`name`) VALUES ('Metal');
INSERT INTO Genre (`name`) VALUES ('Punk');
INSERT INTO Genre (`name`) VALUES ('Hip Hop');
INSERT INTO Genre (`name`) VALUES ('Emo');

INSERT INTO Album (title, artist_id) VALUES ('Furi', 1);
INSERT INTO Album (title, artist_id) VALUES ('Floorspace', 2);
INSERT INTO Album (title, artist_id) VALUES ('Canon', 3);
INSERT INTO Album (title, artist_id) VALUES ('Vessel', 3);
INSERT INTO Album (title, artist_id) VALUES ('Globe Vol. 1', 1);

INSERT INTO Track (title, rating, len, `count`, album_id, genre_id)
    VALUES ('Tocatta', 5, NULL, NULL, 3, 3);
INSERT INTO Track (title, rating, len, `count`, album_id, genre_id)
    VALUES ('Create', 5, NULL, NULL, 3, 3);
INSERT INTO Track (title, rating, len, `count`, album_id, genre_id)
    VALUES ('Winter', 5, NULL, NULL, 3, 3);
INSERT INTO Track (title, rating, len, `count`, album_id, genre_id)
    VALUES ('Canon Pt II', 5, NULL, NULL, 3, 3);
INSERT INTO Track (title, rating, len, `count`, album_id, genre_id)
    VALUES ('Canon', 5, NULL, NULL, 3, 3);
INSERT INTO Track (title, rating, len, `count`, album_id, genre_id)
    VALUES ('Resonate', 5, NULL, NULL, 3, 3);
INSERT INTO Track (title, rating, len, `count`, album_id, genre_id)
    VALUES ('Parallel', 4, NULL, NULL, 4, 3);
INSERT INTO Track (title, rating, len, `count`, album_id, genre_id)
    VALUES ('Geist', 5, NULL, NULL, 4, 3);
INSERT INTO Track (title, rating, len, `count`, album_id, genre_id)
    VALUES ('Gossip', 5, NULL, NULL, 2, 2);
INSERT INTO Track (title, rating, len, `count`, album_id, genre_id)
    VALUES ('Stacks', 5, NULL, NULL, 2, 2);
INSERT INTO Track (title, rating, len, `count`, album_id, genre_id)
    VALUES ('Latex', 3, NULL, NULL, 2, 2);
INSERT INTO Track (title, rating, len, `count`, album_id, genre_id)
    VALUES ('Eden', 4, NULL, NULL, NULL, 2);
INSERT INTO Track (title, rating, len, `count`, album_id, genre_id)
    VALUES ('Afterhours', 4, NULL, NULL, 2, 2);
INSERT INTO Track (title, rating, len, `count`, album_id, genre_id)
    VALUES ('THE POWER!', 5, NULL, NULL, 2, 2);
INSERT INTO Track (title, rating, len, `count`, album_id, genre_id)
    VALUES ('Surrender', 5, NULL, NULL, 2, 2);
INSERT INTO Track (title, rating, len, `count`, album_id, genre_id)
    VALUES ('4''18 Till the End', 5, NULL, NULL, 5, 1);
INSERT INTO Track (title, rating, len, `count`, album_id, genre_id)
    VALUES ('Purple Eyes', 5, NULL, NULL, 5, 1);
INSERT INTO Track (title, rating, len, `count`, album_id, genre_id)
    VALUES ('Make This Right', NULL, NULL, NULL, 1, 1);
INSERT INTO Track (title, rating, len, `count`, album_id, genre_id)
    VALUES ('Make This Right - Remix', 5, NULL, NULL, 1, 1);
INSERT INTO Track (title, rating, len, `count`, album_id, genre_id)
    VALUES ('My Only Chance', 5, NULL, NULL, 1, 2);

SELECT Album.title, Artist.name FROM Album JOIN Artist ON
    Album.artist_id = Artist.artist_id;

SELECT Album.title, Album.artist_id, Artist.artist_id, Artist.name
    FROM Album JOIN Artist ON Album.artist_id = Artist.artist_id;

SELECT Track.title, Genre.name FROM Track JOIN Genre ON
    Track.genre_id = Genre.genre_id;

SELECT Track.title, Artist.name, Album.title, Genre.name
    FROM Track JOIN Genre JOIN Album JOIN Artist
    ON Track.genre_id = Genre.genre_id AND Track.album_id =
    Album.album_id AND Album.artist_id = Artist.artist_id;

What this code is doing

The Artist table stores artist names once. The Album table stores album titles once and links each album back to the correct artist. The Genre table stores genre names once. The Track table stores the songs and links each one to an album and genre.

This setup is much cleaner than forcing all of that information into one spreadsheet. It reduces repetition, makes searching easier, and gives the business a better foundation for growth. If the catalog expands from a handful of songs to thousands of tracks, the structure still holds.

The larger point is simple. A spreadsheet can list information, but a database can organize it in a way that is actually useful. For a digital music product where songs may be sold one by one, that structure matters.

rae soria - blog | portfolio

SQL Music Database Prototype | Case Study - Rae Soria App

Okay so why not just use the spreadsheet instead of SQL?

My plain code prototype (no bells and whistles, just code)

What this code is doing

Popular posts from this blog