Why is PlaylistTrack the only association table in the Chinook DB?

Tea Tree 0
Viewed 21

So I'm taking a look at the well-known Chinook DB. The PlaylistTrack table is obviously an association table because it just contains keys from two tables.

But what I don't quite understand is why certain other tables like Invoice are not association tables despite also containing at least two keys. Invoice, for example, contains InvoiceId and CustomerId (as well as some other fields).

I know that InvoiceId is the primary key for the invoice table and CustomerId is a foreign key pointing to another table. But isn't that the same for the PlaylistTrack table?

Conceptually speaking, what is it about the PlaylistTrack table that makes it an association table? And what is it that is different about the Invoice table that doesn't make it an association table? Or is it an association table?

sqlite sql
1 Answers

It is because that it is a many-many relationship, all the other relationships are one-many.

A one-many relationship can be achieved by the child storing a unique reference/map association with it's ONE and ONLY parent (the parent potentially have 0-MANY children).

However, this embedding of the reference will not facilitate, the playlist track as a track can be in many playlists (so a track could have many parents). That is where the association tables comes in, it caters for many-many relationships (by that nature if can obviously also cater for one-one relationships).

However, I believe that the InvoiceLine table is also an associative table but with additional values (Unit price and Quantity) specific to the actual association/relationship.

MikeT0
Related