- Get link
- X
- Other Apps
This image is an Entity-Relationship Diagram (ERD) for a music-store database, very similar to the PostgreSQL Chinook database. It shows how the different tables are connected.
Think of it as a map of the database: each box is a table, and each line shows how tables relate to each other.
1. The tables
Each rectangle represents a database table:
| Table | What it stores |
|---|---|
invoice | Customer purchases/invoices |
invoice_line | Individual items/tracks purchased |
customer | Customer information |
employee | Employees/support representatives |
track | Songs/tracks |
album | Albums |
artist | Artists |
genre | Music genres |
media_type | Types of media/file formats |
playlist | Playlists |
playlist_track | Connects playlists to tracks |
2. Start with customer
At the top:
customer | | support_rep_id v employee
A customer has a support_rep_id.
That means the database can determine which employee is responsible for supporting that customer.
For example:
Customer: customer_id = 25 name = "John Smith" support_rep_id = 3
Then:
Employee: employee_id = 3 name = "Jane Doe"
So Jane is John's assigned support representative.
3. Employee reports to another employee
You can see:
employee ↻ reports_to
This is called a self-referencing relationship.
An employee can report to another employee.
For example:
Manager employee_id = 1 ↑ | reports_to Employee employee_id = 5
Employee #5 reports to employee #1.
This allows the database to represent an organizational hierarchy:
General Manager | +--- Sales Manager | | | +--- Sales Employee | +--- IT Manager | +--- IT Employee
4. invoice and invoice_line
This is one of the most important relationships in the diagram.
invoice | | invoice_id v invoice_line
An invoice represents a customer's purchase.
An invoice_line represents an individual item on that invoice.
For example:
Invoice
invoice_id: 100 customer_id: 5 total: $3.98
The invoice might contain two tracks:
Invoice lines
invoice_id | track_id | price -----------|----------|------ 100 | 25 | 1.99 100 | 30 | 1.99
So:
Invoice #100 | +-- Track #25 | +-- Track #30
This is why invoice_line connects both to invoice and track.
5. invoice_line → track
The diagram shows:
invoice_line | | track_id v track
This means each invoice line identifies which song/track was purchased.
For example:
invoice_line ---------------- invoice_id = 100 track_id = 25 unit_price = 0.99
The track_id points to:
track ---------------- track_id = 25 name = "Song Name"
6. track → album
The diagram shows:
track | | album_id v album
A track belongs to an album.
For example:
Album album_id = 10 title = "Greatest Hits"
Then:
Track track_id = 25 name = "Song A" album_id = 10
So Song A belongs to Greatest Hits.
An album can contain many tracks:
Album | +-- Track 1 +-- Track 2 +-- Track 3 +-- Track 4
7. album → artist
This relationship is:
album | | artist_id v artist
An album belongs to an artist.
For example:
artist ---------------- artist_id = 5 name = "The Band"
Then:
album ---------------- album_id = 10 title = "Greatest Hits" artist_id = 5
Therefore:
Artist ↓ Album ↓ Tracks
This gives you a hierarchy:
Artist | +--- Album | +--- Track +--- Track +--- Track
8. track → genre
The diagram also shows:
track | | genre_id v genre
This tells you what type of music a track is.
For example:
genre ---------------- genre_id = 1 name = "Rock"
And:
track ---------------- track_id = 25 name = "Example Song" genre_id = 1
Therefore:
Example Song = Rock
9. track → media_type
There's another relationship:
track | | media_type_id v media_type
This describes the format/type of the track.
For example:
media_type ---------------- media_type_id = 1 name = "MPEG audio file"
Then a track can reference that media type:
track ---------------- track_id = 25 media_type_id = 1
10. playlist and playlist_track
This is another very important database concept.
You have:
playlist | | playlist_id v playlist_track ^ | | track_id | track
playlist_track is a junction/bridge table.
Why do we need it?
Because:
- One playlist can contain many tracks.
- One track can appear in many playlists.
That's a many-to-many relationship.
For example:
Playlist: Workout | +--- Track A +--- Track B +--- Track C
But Track A could also be in another playlist:
Playlist: Workout | +--- Track A Playlist: Favorites | +--- Track A
The database uses playlist_track to store those connections:
playlist_id | track_id ------------|--------- 1 | 25 1 | 30 1 | 45 2 | 25
Notice Track 25 is in both playlists.
11. The entire database flow
The easiest way to understand the diagram is to follow a song from the artist all the way to a purchase:
ARTIST ↓ ALBUM ↓ TRACK ↓ INVOICE_LINE ↓ INVOICE ↓ CUSTOMER ↓ EMPLOYEE
More specifically:
Artist │ │ artist_id ▼ Album │ │ album_id ▼ Track │ ├──────────────► Genre │ ├──────────────► Media Type │ └──────────────► Invoice Line │ │ invoice_id ▼ Invoice │ │ customer_id ▼ Customer │ │ support_rep_id ▼ Employee
And separately:
Playlist │ ▼ Playlist_Track ▲ │ Track
12. What the IDs mean
You'll notice names such as:
artist_id album_id track_id invoice_id customer_id employee_id
These are generally keys used to connect tables.
For example:
artist -------------------- artist_id name 1 AC/DC 2 Aerosmith 3 Metallica
Then:
album ------------------------- album_id artist_id 10 1 11 1 12 2
artist_id in album is a foreign key pointing to artist.artist_id.
So:
album.artist_id ↓ artist.artist_id
That's what the lines in your diagram are showing.
The big picture
You can think of this database as a music store system:
Artists create albums → albums contain tracks → tracks have genres/media types → customers purchase tracks → purchases are stored in invoices → customers are assigned employees.
And playlists provide another way of organizing tracks:
┌───────────┐ │ ARTIST │ └─────┬─────┘ │ ┌─────▼─────┐ │ ALBUM │ └─────┬─────┘ │ ┌─────▼─────┐ │ TRACK │ └──┬──┬──┬──┘ │ │ │ ┌─────┘ │ └──────┐ ▼ ▼ ▼ GENRE MEDIA PLAYLIST TYPE │ ▼ PLAYLIST_TRACK TRACK │ ▼ INVOICE_LINE │ ▼ INVOICE │ ▼ CUSTOMER │ ▼ EMPLOYEE
The most important concept
If you're learning PostgreSQL, this diagram is essentially teaching you how foreign keys connect tables.
For example, you could write:
SELECT t.name AS track, a.title AS album, ar.name AS artist, g.name AS genre FROM track t JOIN album a ON t.album_id = a.album_id JOIN artist ar ON a.artist_id = ar.artist_id JOIN genre g ON t.genre_id = g.genre_id;
That query follows the relationships in your diagram:
Track → Album → Artist and Track → Genre.
This is the foundation you'll need for building your FreightFlow/PostgreSQL application too: customers, shipments, carriers, invoices, drivers, etc. can be structured and connected in the same way.
PandaDoc
Contract Generation Software
Fast, Simple and Secure Way to Manage & Sign Your Contracts.
In PostgreSQL, VACUUM FULL is a command used to clean up a table and physically shrink the table's storage space.
Simple explanation
When you UPDATE or DELETE rows in PostgreSQL, PostgreSQL doesn't immediately remove the old data from the table's physical storage.
For example:
Before DELETE: customers table ---------------- John Mike Sarah David
You delete Mike:
DELETE FROM customers WHERE name = 'Mike';
Logically:
John [deleted] Sarah David
But PostgreSQL may still have the old space occupied internally.
Running:
VACUUM FULL customers;
rebuilds the table and removes that unused space:
John Sarah David
VACUUM vs VACUUM FULL
| Command | What it does |
|---|---|
VACUUM | Cleans up dead rows and makes their space reusable |
VACUUM FULL | Rewrites the table and actually returns unused space to the operating system |
ANALYZE | Updates statistics PostgreSQL uses to optimize queries |
VACUUM ANALYZE | Cleans the table and updates query-planner statistics |
VACUUM FULL ANALYZE | Rebuilds the table, frees disk space, and updates statistics |
⚠️ Important difference
VACUUM FULL is much more disruptive than normal VACUUM.
It needs to rewrite the table and takes a strong lock on it, which means applications may have to wait while it runs.
So you generally shouldn't run VACUUM FULL routinely just because you have PostgreSQL.
Usually PostgreSQL's automatic vacuum system handles normal cleanup:
INSERT / UPDATE / DELETE ↓ Dead rows ↓ Autovacuum cleans them ↓ Space becomes reusable
When would you use VACUUM FULL?
A common situation is when you had a very large table, deleted a huge amount of data, and want to actually reclaim the disk space.
For example:
DELETE FROM logs WHERE created_at < '2025-01-01';
If that removes millions of rows, you might investigate the table's size and, if appropriate during a maintenance window:
VACUUM FULL logs;
For a development database like your FreightFlow PostgreSQL project, you probably won't need VACUUM FULL frequently. VACUUM ANALYZE or letting autovacuum do its job is normally the better default.
- Get link
- X
- Other Apps
Comments
Post a Comment