performance - How to efficiently store and search large lists of user preferences in a SQL database? - Stack Overflow

admin管理员组

文章数量:1314411

I’m working on a web application where users can select multiple preferences (e.g., favorite foods, music genres, hobbies, etc.). A user can have many preferences, and I need to store this information in a SQL database while keeping searches fast.

I need to:

• Store multiple preferences for each user.
• Quickly find users who have a specific preference (e.g., all users who like "rock music").
• Avoid performance issues as the number of users and preferences grows.

I’m thinking of storing preferences as a comma-separated list in a single column, but I heard this is not a good practice.

Should I use a separate table with relationships, JSON storage, or something else?

I’m working on a web application where users can select multiple preferences (e.g., favorite foods, music genres, hobbies, etc.). A user can have many preferences, and I need to store this information in a SQL database while keeping searches fast.

I need to:

• Store multiple preferences for each user.
• Quickly find users who have a specific preference (e.g., all users who like "rock music").
• Avoid performance issues as the number of users and preferences grows.

I’m thinking of storing preferences as a comma-separated list in a single column, but I heard this is not a good practice.

Should I use a separate table with relationships, JSON storage, or something else?

• sql
• performance
• database-design
• query-optimization

Share Improve this question Follow edited Feb 2 at 13:37 Freddy Alexander asked Jan 31 at 13:59 Freddy AlexanderFreddy Alexander 144 bronze badges 2

• if you are going to use a relational database, stick to normal forms (avoid composite fields) – mr mcwolf Commented Jan 31 at 14:20
• "...storing preferences as a comma-separated list..." -- that's not relational since it breaks 1NF. By "SQL database" I assume you mean a "relational database". You did study normal forms at school, didn't you? – The Impaler Commented Jan 31 at 16:59

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 3

The normal relational database way would be to use three tables. For example, Activities, Users, and UserActivities with a unique primary index ID for each row, ActivitiesID, UsersID, and UserActivitiesID respectively. UserActivities needs UserID and ActivitiesID as columns to link a UserID with perhaps many ActivityIDs. If 'Rock Music' is ActivityID 5, and you want to list all UsersIDs linked with it, you can just use the simple query below.

SELECT UserID
FROM UserActivities 
WHERE UserActivityID = 5;

You can join to the other tables to get User or Activity information in the query. For large tables, you can index UserID and/or ActivitiesID in UserActivities. You can have another column UserActivityType in UserActivities to indicate if the relation is a like/dislike or whatever. You can also find all Activities related with a UserID = 10 with the simple query below.

SELECT ActivityID
FROM UserActivities
WHERE UserID = 10;

and join with the other tables for User or Activity information.

本文标签：