0

I am trying to figure out the best way to store user statistics data in a table. For each user property, there can be anywhere from one to a couple thousand integer values (realistically, these would would average to a number in the hundreds). I know that PostgreSQL allows columns to contain integer[] values, but is this really the best solution when I could potentially be storing a a couple hundred or a thousand integers in one cell?

Here is example table with a few of the user properties I might store in it:

| UserStatistics                                                                                      |
|-----------------------------------------------------------------------------------------------------|
| user_id | user_song_likes | user_album_likes | user_artist_likes | user_followers | user_following  |
|-----------------------------------------------------------------------------------------------------|
| 1       | [12,56,34,11,7] | [1,154,32,33,84] | [5,14,54,43,10,2] | [55,63,23,2,4] | [43,78,2,4,22]  |
|-----------------------------------------------------------------------------------------------------|
| 2       | [1,2,3,4,87,89] | [62,71,4,6,12,2] | [13,17,35,76,1,2] | [91,17,7,87,9] | [21,17,79,98,1] |
|-----------------------------------------------------------------------------------------------------|
| 3       | [45,53,42,19,2] | [7,14,144,67,93] | [13,14,2,23,3,73] | [11,56,5,12,1] | [34,91,23,10,5] |
|-----------------------------------------------------------------------------------------------------|

In this example, the user_song_likes array will realistically hold hundreds of values (I scaled down the array for the sake of this example). So will the user_album_likes array. user_artist_likes will most likely come close to a hundred values, but it will definitely surpass that for some users. user_followers will average a between 20 and 80 values, but there are going to be a few select users with tens of thousands of followers. user_following is going to average between 100 and 2,000 values for almost every user.

All this considered, I think it would be very inefficient to store user statistics with integer[] columns. What are my alternative options? What would be a reasonably efficient way to store this data?

5
  • Have you considered a separate table for user_song_likes etc?
    – Lennart
    Feb 7, 2021 at 19:34
  • normalizing the database, helps you pon the long run
    – nbk
    Feb 7, 2021 at 20:20
  • @Lennart I am not seeing how separate table would help. Each cell would still have to contain multiple values, unless I somehow created a new column for every liked song. Feb 7, 2021 at 21:09
  • No, each time a user likes a song that would be represented as a row. (1,12),(1,56),.. I was curious whether you considered it and discarded it, and the reason for it
    – Lennart
    Feb 7, 2021 at 21:50
  • Hi, and welcome to the forum! Under no circumstances should you have comma separated lists of anything in an RDBMS. Datums should be atomic - search on 1st Normal Form. In an RDBMS, "things" (users, songs, artists) are represented by tables. You can represent the relationships between them using Associative Entities - linking/joining tables (again, search). Any other way is not good! User likes Artist - have a u_likes_a table with a user_id field and an artist_id field! Feb 7, 2021 at 21:58

2 Answers 2

1

As others have mentioned, I'd look to normalize the data in your UserStatistics table. You could do something stupid simple as a generic Likes table and a Follows table (don't take my poor names, I'm sure you can come up with better ones :).

The Likes table would have its own primary key, and a foreign key field referencing user_id, and it could have a column called like_id and a column called like_type. The like_id would store one row per ID from the user_song_likes, user_album_likes, and user_artist_likes columns of your previous schema, and the like_type would represent if it's of type song, album, or artist.

You could then implement the same idea for your Follows table (e.g. store one row per ID of each follower and following in a generic follow_id column with a follow_type column to determine which is which). Using a more normalized schema (even though this one is generic to keep things simple) is a more relational form of storing your data and will be more efficient for querying.

2
  • 1
    I see. I had to research what it means to "normalize" a database, but once I understood 4th normal form I was able to see how this helps. +1 Feb 8, 2021 at 20:48
  • 1
    @CardinalSystem Sorry, I almost always link a good doc on it whenever I drop that word, but forgot to this time. Will update my answer when I get a chance for future readers, but glad you understand it now. :)
    – J.D.
    Feb 8, 2021 at 23:34
0

I'll add an answer instead.

CREATE TABLE user_song_likes
( user_id... NOT NULL REFERENCES users (user_id)
, song_id... NOT NULL REFERENCES songs (song_id)
- - if order is important a timestamp can be used
, PRIMARY KEY (user_id, song_id)
);

You may have valid objections against a relational design like that, I was curious whether you considered it and rejected it.

For such a design you may want to have a look at a columnar store, I have not used one in postgres but I Googled and found Citus and Swarm64. Perhaps there are others as well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.