Is it a bad idea to have a "kind" column on a join table?

Question

Is it a bad idea to have a "kind" column on a join table or should I just create separate tables for each kind?

For example:

Table A: Person
Table B: Song
JoinTable A: Person_Song (Kind: Playlist, CD, Album)

DB columns:

Table: person
Column: id, PK

Table: song
Column: id, PK

Table: person_song
Column: id, PK
Column: person_id, FK
Column: song_id, FK
Column: kind, enum(playlist, cd, album)

or

Table A: Person
Table B: Song
JoinTable A: Playlist (Person <-> Song)
JoinTable B: CD (Person <-> Song)
JoinTable C: Album (Person <-> Song)

DB columns:

Table: person
Column: id, PK

Table: song
Column: id, PK

Table: Playlist
Column: id, PK
Column: person_id, FK
Column: song_id, FK

Table: cd
Column: id, PK
Column: person_id, FK
Column: song_id, FK

Table: album
Column: id, PK
Column: person_id, FK
Column: song_id, FK

Could someone please explain which way is better (or correct) and why?

Answer 1

For the simple example given the answer is meh. Either will work equally well for what's described. Each can be converted to the other using views with a WHERE clause, or a UNION.

For a real-world example you have to dig into the precise definition of playlist, album and CD. Are they "really" the same thing, just with different strings in some inconsequential tag? Or are they fundamentally distinct concepts that happen to each have a list of people & songs? This is no longer science. This is now craft, a bit of philosophy and a deep understanding of the business domain.

I would separate them if

• they have very different processes operate on them
• they have very different lifetimes (a playlist exists for hours, an album for decades)
• they have separate use cases
• they have disjoint user communities

Again, this is a judgement call. I'm not saying all those criteria must apply, only that these are the sorts of considerations that would tend to take my decision in that direction.

If the kinds are significantly different consider a subtype pattern. There is a single intersection table to hold the song mapping and separate tables related to it to hold subtype specific attributes. Tables would be Collection (collectionid, kind) SongList (collectionid, songid, personid) Playlist( collectionid, .. playlist specific attributes) Album( collectionid, .. album specific attributes)