Make own like system for various content

Question

There are three types of content in my database. They are Songs, Albums and Playlists. Albums and Playlists are just collections of songs. And I want to let the user put like for each of them. I made table with columns

LikeId UserId SongId PlaylistId AlbumId

for storing likes. For example if user puts like to song, I put song's id into SongId column and user's id into UserId column. Other columns will be null. It's working good,but I don't like this solution because it's not normalized. So I want to ask if there are better solutions for this.

score 0 · Answer 1 · answered Jan 12 '14 at 20:34

0

How about

LikeId  UserId LikeType TargetId

Where LikeType can be "Song", "Playlist" or "Album" ?

answered Jan 12 '14 at 20:34

FJT

1,923
1
15
14

Thanks for answer.It's interesting and more compact than my solution. Maybe I will use it if i don't find better. But I will search more normalized solution. – romandemidov Jan 12 '14 at 20:58
This solution would allow you to add new like types (e.g. "artist") without further database changes. A lookup table for LikeType would complete the design. – FJT Jan 13 '14 at 10:55

score 0 · Answer 2 · answered Jan 12 '14 at 21:43

Your solution is fine. It has the nice feature that you can set up explicit foreign key relationships to the other tables. In addition, you can verify that exactly one of the values is set by adding a check constraint:

check ((case when SongId is null then 0 else 1 end) + 
       (case when AlbumId is null then 0 else 1 end) +
       (case when PlayListId is null then 0 else 1 end)
      ) = 1

There is an overhead incurred, of storing NULL values for all three. This is fairly minimal for three values.

You can even add a computed column to get which value is stored:

WhichId = (case when SongId is not null then 'Song'
                when AlbumId is not null then 'Album'
                when PlayListId is not null then 'PlayList
            end);

score 0 · Answer 3 · answered Jan 13 '14 at 00:26

As a glutton for punishment, I would use three tables: UserLikesSongs, UserLikesPlaylists and UserLikesAlbums. Each contains a UserId and an appropriate reference to one of the other tables: Songs, Albums or Playlists.

This also allows adding additional type-specific information. Perhaps Albums will support a favorite track in the future.

You can always use UNION to combine data from the various entity types.

score 0 · Accepted Answer · edited May 23 '17 at 12:03

You should just create 3 tables - one for User paired with each of Playlist, Song, and Album. They'd look something like:

CREATE TABLE PlaylistLikes
(
    UserID INT NOT NULL, 
    PlaylistID INT NOT NULL, 
    PRIMARY KEY (UserID, PlaylistID), 
    FOREIGN KEY (UserID) REFERENCES Users (UserID), 
    FOREIGN KEY (PlaylistID) REFERENCES Playlists (PlaylistID)
);
CREATE TABLE SongLikes
(
    UserID INT NOT NULL, 
    SongID INT NOT NULL, 
    PRIMARY KEY (UserID, SongID), 
    FOREIGN KEY (UserID) REFERENCES Users (UserID), 
    FOREIGN KEY (SongID) REFERENCES Songs (SongID)
);
CREATE TABLE AlbumLikes
(
    UserID INT NOT NULL, 
    AlbumID INT NOT NULL, 
    PRIMARY KEY (UserID, AlbumID), 
    FOREIGN KEY (UserID) REFERENCES Users (UserID), 
    FOREIGN KEY (AlbumID) REFERENCES Albums (AlbumID)
);

Here, having both columns in the primary key prevents the user from liking the song/playlist/album more than once (unless you want that to be available - then remove it or maybe keep track of that in a 'number of likes' column).

You should avoid putting all 3 different types of likes in the same table - different tables should be used to represent different things. You want to avoid "One True Lookup Table" - here's one answer detailing why: OTLT

If you want to query against all 3 tables, you can create a view which is the result of a UNION between the 3 tables.

Make own like system for various content

4 Answers4