1

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? 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 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!
    – Vérace
    Feb 7, 2021 at 21:58

3 Answers 3

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
2

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.

1
  • It will definitely be easier to add or remove a song from a relational design like this. You can add an index for song_id, and an index for user_id to easily get songs a user likes, or all users that like a song...not so easy to do that with the OP example design. Apr 1, 2023 at 19:31
0

I would do something like this, personally:

    --holds stats (song, artist, etc.)
    create table stat(
          stat_id serial primary key,
          name text
     );
    
    --holds values per stat
    create table stat_value(
          stat_value_id serial primary key,
          stat_id int references stat(stat_id),
          value text
     );
    
    --users
     create table user(
          user_id int primary key,
          name text
          --etc...
     );
    
    --relates users to stat values
     create table user_stat(
          user_stat_id bigserial primary key,
          user_id int references user (user_id),
          stat_id int references stat (stat_id), --not really necessary, but would speed things up if you didn't want to scan all stat_values for which stat it is
          stat_value_id int references stat_value (stat_value_id)
     );
    

This would allow you to:

  • maintain referential integrity
  • not have to create a table per stat (though you may want to)
  • quickly search all users of a given stat
  • quickly search all stats of a given user
  • more easily add and remove data (contrasted with scanning user by user and updating arrays with all previous values +/- a value)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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