I am building a music application. In my database I have an "Artist" table, related to an "Album" table, related to a "Track" table.
Each user of my application can "like" (thumbs up) or "dislike" (thumbs down) an artist/album/track. Thus, I have to create Many-to-many relationships between users and the artists/albums/tracks with an argument "vote" which can be set to 1/-1.
My question is : Would it be more appropriate to create three "Like/Dislike" tables (user_artist_like, user_album_like, user_track_like) or only one table "user_like" with three columns (artist_id, album_id, track_id) ? Knowing that I will often have to fetch all the likes of a user.
The first option is better, because putting the data in one table implies that data points in the same row are related, which is not true. Multiple tables allows you to manage the data more easily without getting confused by the rows. For instance, how would you structure a INSERT INTO statement for the single table? It couldn't use the space effectively.