Implementing Comments and Likes in database

by

The most extensible solution is to have just one “base” table (connected to “likes”, tags and comments), and “inherit” all other tables from it. Adding a new kind of entity involves just adding a new “inherited” table – it then automatically plugs into the whole like/tag/comment machinery.

Entity-relationship term for this is “category” (see the ERwin Methods Guide, section: “Subtype Relationships”). The category symbol is:

Category

Assuming a user can like multiple entities, a same tag can be used for more than one entity but a comment is entity-specific, your model could look like this:

ER Diagram

BTW, there are roughly 3 ways to implement the “ER category”:

  • All types in one table.
  • All concrete types in separate tables.
  • All concrete and abstract types in separate tables.

Unless you have very stringent performance requirements, the third approach is probably the best (meaning the physical tables match 1:1 the entities in the diagram above).

More Related Contents:

  1. What does character set and collation mean exactly?
  2. Auto Generate Database Diagram MySQL [closed]
  3. MySQL stored procedures use them or not to use them
  4. In SQL, is it OK for two tables to refer to each other?
  5. Modeling Product Variants
  6. How to design a multi tenant mysql database
  7. DOs and DONTs for Indexes [closed]
  8. MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?
  9. Difference between two table structure
  10. Proper database model for a user feedback system (an interesting case)
  11. Strings as Primary Keys in MYSQL Database [closed]
  12. MySQL: how to do row-level security (like Oracle’s Virtual Private Database)?
  13. Inventory management with stock options
  14. ‘IF’ in ‘SELECT’ statement – choose output value based on column values
  15. How to do a batch insert in MySQL
  16. Count the occurrences of DISTINCT values
  17. Swapping column values in MySQL
  18. Is there a Profiler equivalent for MySql? [closed]
  19. Cast int to varchar
  20. Import SQL file into mysql
  21. How to skip certain database tables with mysqldump?
  22. “where 1=1” statement [duplicate]
  23. How do you debug MySQL stored procedures?
  24. How to set a MySQL row to READ-ONLY?
  25. MySQL – Supertype/Subtype design
  26. mySQL – Create a New Table Using Data and Columns from Three Tables
  27. Case Sensitive collation in MySQL
  28. Can’t import database through phpmyadmin file size too large
  29. What is the best way to do incremental backups in MySQL?
  30. MySQL Relationships

Leave a Comment