CODEWITHSUNDEEP
sqldatabasepostgresqldatabase-designdatabase-schema
JoeK
JoeK

Reputation: 1

Design SQL Schema for User Interests

I'm trying to find the best approach for designing sql schema for the following scenario:
-A User can have multiple interests
-The platform provides predefined interests from which the user can select from
-The User can create his own custom interests and select them

I'm suggesting the following schema:

interests(id, name)
custom_interests (id, name, user_id)
user_interests (id, interest_id, custom_interest_id, user_id)
// Where (interest_id is NULL and custom_interest_id IS NOT NULL) OR (interest_id is NOT NULL AND custom_interest_id IS NULL)

in this approach users in user_interests table , user would be able to select from custom_interests items that dont belong to him.

it sure can be restricted with proper backend validation, but does anyone has a better suggestion for this type of scenario ?

Thanks! :)

Upvotes: 0

Views: 95

Answers (1)

quyentho
quyentho

Reputation: 663

I would suggest you separate system-provided interests and user-custom interests. Because what if different users choose the same name for their custom interests? You don't want to only store one row of user-custom interest in the table. Who knows what they might want to do in the future, like update the name or delete the custom interest they created? Even if you perceive them as the same structure, they are actually different entities representing different things in the system (by displaying them in different colors, you are actually acknowledging that there are differences).

The table design I would suggest:

system_interests: id, name
custom_interests: id, name
user_system_interests: user_id, system_interest_id (user can have multiple interests)
user_custom_interests: user_id, custom_interest_id

This design opens up a more flexible approach for your system. Because someday you might find that the structure of custom_interests is not actually the same as system_interests (or vice versa), so you can easily modify them separately.

Upvotes: 0

Related Questions