SQL Database layout/design

Question

I'm creating a music player that is backed by a SQLite database. There's a songs table that has an id, title, artist, album, etc. I'm currently trying to make playlists and I'd like to know whether my design will be efficient. Initially I wanted to make a table of playlists and each playlist entry would have a list of song ids. I would then query the songs table for the list of song ids. Something along the lines of SELECT * FROM songs where id=this OR id=that OR id=..... However, I've just read up on joins so now I'm thinking that each playlist should be its own table and entries for a playlist table would just be ids from the songs table and I can do an inner join on the song id column between a specific playlist table and the songs table. Which method would be more efficient? Are they equivalent?

Answer 1

When you find yourself considering creating multiple identical tables holding similar data you probably should take a step back and rethink your design as this is contrary to the idea underlying the relational model. The same goes for the idea of storing "lists of ids", which I interpret to mean some kind of array of data, something that also is a bad fit for a good model as every item in a row (or tuple) should only store one value.

One possible design for your domain could be this:

Songs (SongID PK, SongAttributes (name, length etc) ...)
Playlists (PlaylistID PK, PlaylistAttributes (like name, owner etc) ...)
PlaylistSongs (PlaylistID FK, SongID FK)
PK = Primary Key, FK = Foreign Key

To select all songs songs for a certain playlist:

select songs.name 
from songs 
join playlistsongs on songs.songid = playlistsongs.songid 
join playlist on playlist.playlistid = playlistsongs.playlistid 
where playlist.name = '???'

As for your questions:
Which method would be more efficient? Are they equivalent?

Neither would be good, and they're not equivalent. In the first example you would probably get problems with retrieving and updating data, and in the other the number of tables would be linear to the number of playlists, and you would have to inject every query you make with the suitable table name at run-time - this is really something that you do not want.

• Andrew Orobator: How would I go about selecting all songs that belong to a certain playlist?

Answer 2

OR statements become inefficient very quickly, so a model with playlist and a playlist id, is an option.

Example: SONG ----> PLAYLIST WITH LIST OF SONGIDs

Pseudo code:

CREATE TABLE SONG (
song_id INT,
name VARCHAR,
other attributes);
CREATE TABLE playlists (
playlist_id INT,
song_id INT REFERENCES FOREIGN KEY song(song_id),
other playlist attributes);

Then you can join the list:

SELECT a.*, b.*
FROM playlists a 
INNER JOIN song b ON a.song_id=b.song_id AND a.playlist_id=?;

which will give you the playlist of a certain person owning playlist_id X.

• Andrew Orobator: I'm confused by what a.* and b.* are. Do you mean SONG.* and playlists.*? I'm assuming that's all columns from a specific table? What does from playlists a mean? Does that mean a is now the playlists table? What's the reason for this obfuscation?
• Norbert: a and b are aliases so I can type less when executing joins, selects and where statements. In this case it states: FROM playlists a That can also be written as: FROM playlists AS a So a is the alias for playlists in this case. You can assign any alias you want, and you will need them often when using joins, especially if you start joining tables on themselves (which does happen in more complex data models or with more complex queries).