How to design a database?

Show and playlist being one to many seems fine.

Playlist to track should be a many-to-many relationship. If you're using an ORM, usually they do this boilerplate for you; otherwise, manually you make another table, in which each entry is a relationship (i.e a row would be a primary key, a Playlist foreign key, a track foreign key).

f I did go with 4 tables only, presumably there would be performance implications given the potential amount of data in any one of those tables, particularly tracks.

Trust me, there won't be. If there any, add an index. There are few things in this world more rigorously optimized than SQL engines at this point.

There is no way there would ever be enough tracks in your table to ever reach any limitations.

edit:

Just as an anecdote with regards to performance, once when I helped out on lab code, there was 3 joins between 3 tables each with 500 million+ rows, one with 1.5 billion, on sqlite3. After I made on index on the 3 columns that were being joined, queries took < 0.2 seconds.

And that was on some dude's personal computer.

Trust me, do not worry about performance.

edit2:

The bigger issue w.r.g to performance is if OP is intending for this to be used by multiple users. In which case he should switch to Postgres or something other than sqlite. Sqlite has single-threaded writes. It's fine if you're almost always doing reads (which can be threaded).

To be able to reference a playlist belonging to a particular show. I gave those playlists the same uuid as the show they belong to.

You mean as a show_id column on the playlist table, right? I would hope the playlists have their own uuid that is different from a show's uuid.

As a track could potentially belong to multiple playlists, I can't take the same approach as I do for (show/playlist) How would be best to handle this? Ideally I would like to have a single "Track" table containing all tracks for all playlists.

@stu2b50 is right, it should be a many-to-many relationship. You would keep your Track and Playlist tables, and then you would set up another Playlist_Track table to define the mapping between them. This is sometimes called a 'linker' or a 'joiner' table. You would have at least two columns, one for the playlist id and then one for the track id. Each row would represent a track belonging to a playlist, and you would be able to have as many entries as you like. You'll probably want to set up the primary key for this table as a multiple column primary key to avoid duplicate entries (unless you want duplicate entries).

CREATE TABLE playlist_track (
  playlist_id NOT NULL, 
  track_id NOT NULL, 
  PRIMARY KEY (playlist_id, track_id)
);

@stu2b50 is also right on performance not mattering -- unless you know you'll need to worry about it, you don't need to worry about it.

Edit: the points other have made about there being "no one right way" are also correct. There are as many ways as you can imagine to structure data. However, for the use case you've described, your approach will work perfectly fine until you find it doesn't.

There's no one right way. You could continue with your design, a traditional relational one, by having a many-to-many relationship between Playlists and Tracks.

However, depending on your use case, traditional relationships can be tricky for expanding functionality. Suppose you want to add genre for both playlists and tracks, where each could be in multiple genres. You then have two additional many to many relationships, and things start to get hairy for query complexity and performance.

Enter NoSQL databases. There are many varients, but the common theme is being able to query and index in an unstructured fashion. You could store each playlist as a document with no formal relationship to a track, and then in the user document store a list of their playlists. It can improve performance, but reporting ala traditional RDBMS can be tricky sometimes.

Enter graph databases, which are a sub-set of NoSQL ones. The query language they use is designed primarily around traversing relationships. This is most useful when the relationship between points of data is just as, or more important than the data itself.

There's no one right answer. With clever design, you can do anything in any of them. For your use case, you could likely use any of the three.

One point would be if data integrity is especially important, ACID compliance is very important...don't want accidental losses of data. Initially NoSQL databases had this problem, but that is being rectified by some now.

There is no universally correct answer, but I would read through the manual for the particular database you are using as it occasionally has some hints as to what would offer the best performance (which, in many cases, also means choosing the right datatype for the specific job). I don't have any experience with sqlite, so I won't be able to help you outside of generic advice.

First, I assume you already know about normalization and also know why you generally don't want to make the data super granular.

Without knowing anything about your data, I would assume it would be easiest to store Show, Playlist, and Track in the same table. You'll be repeating a lot of data, especially if tracks are repeated in a playlist, but it makes querying very easy and generally fast. You may also want to index the Show and Playlist columns since it seems like those are the ones you'll be matching against the most often.

Edit: for the second time in a post by you, I find myself feeling @stu2b50's advice is probably a bit better than mine....