CODEWITHSUNDEEP
mysqlrelationship
somejkuser
somejkuser

Reputation: 9030

Mysql Multiple tables relationship structure

I have a users table, an asset table (contains for example a record with value equal to favorite_movie), and than a user_to_asset table (containing userid,assetid,and value, such as 'american history x').

I was thinking if there would be a point to have a type id also, along with a type table ofcourse, for example a type could be user preferences or user details.... so in the end, the user_to_asset table would then contain a userid,typeid,assetid, and value (ofcourse i would have to change the table name bc weve now included typeid within the user_to_asset table).. maybe it would optimize my code since for instance i can query all preferences for a user by just querying the user_to_assets table by typeid equal to user preference and user_id equal to the user.

What do you think?

Upvotes: 1

Views: 452

Answers (1)

AllInOne
AllInOne

Reputation: 1450

I think you want the following tables:

  • user (user_id, user_name, email, theme_id)
  • asset (asset_id, asset_type_id, asset_name, etc)
  • user_to_asset (user_id, asset_id)
  • theme (theme_id, themestuff)
  • asset_type (asset_type_id, asset_type)

This allows you to say:

Show me all the assets of type "movie" for user "Jonathan" while applying Jonathan's theme to the result.

Upvotes: 1

Related Questions