I'd like to discuss database modeling of a big music marketplace — the website Discogs.com. It's one of the largest databases of physical music media: CDs, vinyl, cassettes, and so on. It covers millions of artists, albums, and releases.
We're going to build the logical model, following the approach from my Database Design Book. That means cataloging three things: anchors, attributes, and links. For now, we won't talk about the physical schema — tables, indexes, query optimization. We'll focus purely on the business of selling physical music, and what you need to specify for implementation, as if we were building a Discogs clone.
The Logical Schema
We’ll keep the entire schema in three Google Docs tables: anchors, attributes and links. Most of the schema describes the logical level. Logical level is independent from a specific database server.
The columns on the right side, “Table name”, “Physical column/type”, “Table or column names” specify the physical level. We will talk about that much later in the series, because first we need to agree on what we need to implement.
Anchors
| Anchor | ID example | Table name |
|---|---|---|
| Band | 45467 | bands |
Attributes
| Anchor | Question | Logical data type | Example value | Physical column | Physical type |
|---|---|---|---|---|---|
| Band | What is the name of this Band? | string | “Pink Floyd” | bands.name | VARCHAR(128) |
|
|
Links
| Anchor1 : Anchor2 | Cardi-nality | Sentences | Table or column names |
|---|---|---|---|
| Band : Album | 1 : N | A Band has several Albums An Album was recorded by only one Band |
albums.band_id |
|
|
Exploring Discogs
Let's look at a well-known artist: Pink Floyd. They have 314 releases at the time of writing, with many versions of each. There's a lot of detail here, but I'll show how to handle it incrementally — start with small pieces, design the basic functionality, and then add detail as needed.
Discogs has a huge content catalog: releases and track lists. It’s the main part of the website.
It also has a social-network layer — users can mark that they own an album, that they want to purchase a copy, they can rate stuff, and write reviews. Their first album has more than 100,000 people who own it.
Finally, there's a marketplace: you can buy and sell music. For this particular album there are more than 2500 listings, from sellers all over the world, in all sorts of formats and item qualities.
We'll start with the simplest part of the schema.
Artists / Bands
Looking at the URL — https://www.discogs.com/artist/45467-Pink-Floyd — is often useful when thinking about database design. The artist page has a name, photos (there can be many), links to related sites, a list of members (some crossed out), and name variations.
Looking at the release list for this artist, we see format, label, country, and year columns:
- Format: Album, Vinyl, LP, CD, Cassette, Reissue, Remastered, etc.
- Label: EMI, Columbia, etc.
- Country: US, UK, Europe (yes, "Europe" is listed as a country), and further down the list, combinations like Australia & New Zealand, or Singapore & Malaysia.
- Year: just a list of years — straightforward.
We'll figure out how to model each of these.
Albums
Taking one album — A Saucerful of Secrets — it has a cover photo (again, potentially many), a name, a track list, credits (credits will get complicated, since this is music — more on that later), extensive notes, formats as before, and links to other versions of the same release.
Releases vs. Masters
Looking at the URL for a specific pressing: https://www.discogs.com/release/730906-Pink-Floyd-A-Saucerful-Of-Secrets. This one is a vinyl LP, stereo, published by a specific label, with a catalog number, country of release, series, genre, style, track list, track credits, involved companies, and links to other versions (e.g., pressed in France or the UK). There are also recommendations and user reviews/comments.
You can see the release's sales history — sold for as low as €11 and as high as €158 — and you can add it to your collection. There are also videos and other extras.
This specific pressing is called a release. The broader work it belongs to — with its own distinct ID in the URL — is called a master. A master can have many releases published across different years: 34 releases in '68, 16 in '94, and so on — essentially, some release of a given master comes out almost every year.
First cut at logical schema
Let’s start building the logical schema by filling in the tables.
We’ll skip the “table name” column for now — physical tables will be discussed much later in the series. We must have a reliable logical model before venturing into table design strategies.
Note that we will sometimes be intentionally naive and jump to conclusions prematurely. This is to demonstrate how modeling mistakes are handled.
Bands and albums
Band is our first anchor — there are many bands, new ones get created, and so on — a clear anchor. Later we may discover that this is not a very good name, but let’s begin with something.
| Anchor | ID example | Table name |
|---|---|---|
| Band | 45467 | |
Anchors keep track of IDs. Here we have an example of an ID that we took from the Pink Floyd URL: we know that it’s a number.
We scroll down the artist page and we see that there are many albums released by Pink Floyd. So, Album is our next anchor. In the Discogs URL scheme they call it "master." We'll start by calling it "album" and see if that name holds up — in database design, you often start with one name and later realize it was the wrong choice. We'll see whether that happens here.
| Anchor | ID example | Table name |
|---|---|---|
| Band | 45467 | |
| Album | 10352 |
Attributes
Attributes belong to anchors. They contain the actual information.
We document attributes as questions.
| Anchor | Question | Logical data type | Example value | Physical column | Physical type |
|---|---|---|---|---|---|
| Band | What is the name of this Band? | string | “Pink Floyd” | ||
| Album | What is the name of this Album? | string | “Saucerful of Secrets” |
(Again, skipping the physical column/type for now.) We can leave most other band/album details aside for a moment — a name is enough to get started.
Links
Links (also called "relationships" in classical terminology) connect two anchors. We document them as semi-formalized sentences using either "several" or "only one":
| Anchor1 : Anchor2 | Cardi-nality | Sentences | Table or column names |
|---|---|---|---|
| Band : Album | 1 : N | A Band has several Albums An Album was recorded by only one Band |
(Convention: when cardinality is one-to-many, the "one" side anchor is listed first.)
These sentences look almost like plain English but follow a strict structure — they name two anchors and use one of two fixed phrases ("several" or "only one"). Reading them aloud, and having others read them, helps confirm whether they actually match business reality.
(Note: We already know this will need revisiting — collaborations mean an album can have several bands. We'll get there. For now, we handle the most common case, then extend the model as needed. The nice part is that revising this document, before any code exists, is cheap.)
Releases
Looking at an early release of the album: URL is https://www.discogs.com/release/1382387-Pink-Floyd-A-Saucerful-Of-Secrets, with a lot more detail — label, format, country, release date, genre, style, track list, and credits.
Let’s see what we can see here.
- Release name: inherited from the album — no need to duplicate it as a new attribute. (Or are we a bit naive here?)
- Format: a comma-separated list of multiple values — since an attribute must be single-valued, this can't be an attribute as-is. We discuss formats later in the series.
- Country: also not a simple attribute — later it will become its own anchor.
Release date: this is a straightforward attribute. But first we need to define the anchor:
| Anchor | ID example | Table name |
|---|---|---|
| Release | 1382387 |
And now the attribute:
| Anchor | Question | Logical data type | Example value | Physical column | Physical type |
|---|---|---|---|---|---|
| Release | When was this Release released? | date | 1968-06-29 |
Labels
Clicking into a label — Columbia — its URL is https://www.discogs.com/label/1866-Columbia. Columbia Records is described as the oldest brand name in recorded sound. The label page has a profile, parent label, sub-labels, contact info, links, and a list of releases — a quarter of a million releases published under this label. There are also reviews (of what exactly, we'll find out later).
Label is another anchor — there are many labels, they can be counted, and new ones can be created.
| Anchor | ID example | Table name |
|---|---|---|
| Label | 1866 |
Attributes of Label:
| Anchor | Question | Logical data type | Example value | Physical column | Physical type |
|---|---|---|---|---|---|
| Label | What is the name of this Label? | string | “Columbia” |
Most attributes at this stage are conceptually very simple — "everything has a name" — but it's worth carefully entering all the simple stuff now, because it pays off once things get more complicated (and with music, they will, quickly — and it'll be fun).
More links
| Anchor1 : Anchor2 | Cardi-nality | Sentences | Table or column names |
|---|---|---|---|
| Album : Release | 1 : N | An Album can have several Releases A Release belongs to only one Album |
|
| Label : Release | 1 : N | A Label can publish several Releases A Release can be published by only one Label |
Cardinality is arguably the most important — and hardest to fix later — piece of the model. That's why we write these sentences out and read them aloud: to catch cases where they don't actually match reality.
Spoiler ahead: as we browse more of the catalog we may find out that the Label : Release link may not be an accurate representation of business requirements. It’s very common to uncover such complications as you study more cases from the real world.
The advantage of catching this now is that revising a document is trivial — we haven't written any code yet, we didn’t even create any database tables.
Where we've gotten to
We're setting aside credits and track lists for now — those will come in the follow up posts, along with the rest of the content catalog. After that, we'll cover the social-network side (users, collections, reviews) and then the commercial side (buying and selling).
So far we have:
- 4 anchors (Band, Album, Release, Label)
- 4 simple attributes (names and release date)
- 3 links
Discogs.com is genuinely complex. I’ve been using it for 20 years, there is lots of stuff visible to the user. But there is also a huge backoffice system that underlies it, with lots and lots of functionality that is necessary for operations.
My rough guess is that modeling the entirety of Discogs' functionality might come out to a couple hundred anchors, a couple of times as many attributes, and again a couple hundred or so links. We'll see how that prediction holds up as the series continues.
In the second part we’ll revisit bands, discuss tracklists and images, and talk more about recording labels.
Thank you for reading. This approach to database modeling is explained in more detail in my “Database Design Book”, published last year. It’s short (~150 pages), and to the point.
P. S. If you prefer watching YouTube, here is a playlist from my channel: https://youtube.com/playlist?list=PL1MPVszm5-apdoFvLfnrNw3X6zgxNhmWL&si=3Bny8a4lNKow3iwp
P. P. S. While I work on this series, you can read a previous tutorial: Database Design for Google Calendar, published in 2024.
Database Design Book
Top comments (1)
Calling out the Band:Album 1:N link as something you already know will need revisiting for collaborations is a great teaching moment, most tutorials would just quietly get that wrong and never mention it. Reading the cardinality sentences aloud before any table exists is such a cheap way to catch the expensive mistakes.