All we need is an easy explanation of the problem, so here it is.
Is it a bad idea to have a "kind" column on a join table or should I just create separate tables for each kind?
Table A: Person Table B: Song JoinTable A: Person_Song (Kind: Playlist, CD, Album)
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)
Table A: Person Table B: Song JoinTable A: Playlist (Person <-> Song) JoinTable B: CD (Person <-> Song) JoinTable C: Album (Person <-> Song)
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?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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)
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂