1

I have 4 basic models which are stored in following tables:

  • users
  • images
  • videos
  • posts

In application logic user may "love" image or url or post (for example user clicks little heart next to image or video etc). So I have to keep all "loves" in database. My idea is to use table loves with following columns:

id - love id, primary_key
user_id - id of user who loves something, foreign_key
entity_type - type of entity which is loved by user, enum('image', 'video', 'post')
entity_id - id of entity which is loves (either image_id, video_id, post_id)

unique index on (user_id,entity_type,entity_id) -> user can love unique entity only once

In this solution I can't declare foreign_key on loves.entity_id because I dont know which table it refers to...

Is this a good approach?

Is there better solution than this?

I am using postgres but I tagged question with mysql because maybe is general problem in relational dbs?

user606521
  • 14,486
  • 30
  • 113
  • 204
  • 1
    `relational-database` and `database-design` are better tags than MySQL (because there are much more DBMS out there than just Postgres and MySQL) if you want a more "general" discussion –  Apr 16 '14 at 20:35
  • 1
    What you want is a de-nomarmalized schema. In some occasions is ok, but you need to be carefull. Imagin that your love object is pointing to an object that gets deleted. Your application have to control all this stuff (if can't find love object -> Love object = null) – ericpap Apr 16 '14 at 20:36
  • I use this type of schema for instance to save addresses (with all is fields) for different objects on my database in a single table. – ericpap Apr 16 '14 at 20:37
  • I don't see a reason to store also the entity_type unless you have an admin screen that shows all loves for a type. – user2920788 Apr 16 '14 at 20:38
  • @user2920788 Storing both the id and type allows you to backtrack and sanity check the id, the (id,type) pair is also needed to support the unique constraint. – mu is too short Apr 16 '14 at 20:48
  • 2
    What your trying to do is an [anti-pattern](http://stackoverflow.com/a/20873843/533120). Some alternatives are presnted [here](http://stackoverflow.com/a/13317463/533120). You may also be interested in [Implementing comments and Likes in database](http://stackoverflow.com/a/8113064/533120). – Branko Dimitrijevic Apr 16 '14 at 20:52

0 Answers0