Facebook like notifications tracking (DB Design)

I dont know if this is the best way to do this, but since I got no ideas from anyone else, this is what I would be doing. I hope this answer might help others as well.

We have 2 tables

notification
-----------------
id (pk)
userid
notification_type (for complexity like notifications for pictures, videos, apps etc.)
notification
time


notificationsRead
--------------------
id (pk) (i dont think this field is required, anyways)
lasttime_read
userid

The idea is to select notifications from notifications table and join the notificationsRead table and check the last read notification and rows with ID > notificationid. And each time the notifications page is opened update the row from notificationsRead table.

The query for unread notifications I guess would be like this..

SELECT `userid`, `notification`, `time` from `notifications` `notificationsRead`
WHERE 
`notifications`.`userid` IN ( ... query to get a list of friends ...) 
AND 
(`notifications`.`time` > (
    SELECT `notificationsRead`.`lasttime_read` FROM `notificationsRead` 
    WHERE `notificationsRead`.`userid` = ...$userid...
))

The query above is not checked.
Thanks to the idea of db design from @espais

Related Contents:

  1. How to store multiple options in a single table?
  2. What is the ideal data type to use when storing latitude / longitude in a MySQL database?
  3. What does character set and collation mean exactly?
  4. How to do Inheritance Modeling in Relational Databases?
  5. MySQL – Conditional Foreign Key Constraints
  6. How to pivot a MySQL entity-attribute-value schema
  7. Implementing Comments and Likes in database
  8. MySQL: multiple tables or one table with many columns? [closed]
  9. Is it possible to query a tree structure table in MySQL in a single query, to any depth?
  10. single fixed table with multiple columns vs flexible abstract tables
  11. Auto Generate Database Diagram MySQL [closed]
  12. Unique constraint that allows empty values in MySQL
  13. Entity-Attribute-Value Table Design
  14. In SQL, is it OK for two tables to refer to each other?
  15. MySQL stored procedures use them or not to use them
  16. Storing SHA1 hash values in MySQL
  17. MySQL ENUM type vs join tables
  18. Remove Primary Key in MySQL
  19. Relational database design multiple user types
  20. Modeling Product Variants
  21. How to design a multi tenant mysql database [closed]
  22. DOs and DONTs for Indexes [closed]
  23. Proper database model for a user feedback system (an interesting case)
  24. MySQL – Supertype/Subtype design
  25. Database design for user settings
  26. MySQL datatype to store month and year only [closed]
  27. Strings as Primary Keys in MYSQL Database [closed]
  28. MySQL Creating tables with Foreign Keys giving errno: 150
  29. Find nearest latitude/longitude with an SQL query
  30. Which is fastest? SELECT SQL_CALC_FOUND_ROWS FROM `table`, or SELECT COUNT(*)
  31. How does MySQL process ORDER BY and LIMIT in a query?
  32. MySQL “WITH” clause
  33. Storing Data in MySQL as JSON
  34. How to change collation of database, table, column?
  35. Group mysql query by 15 min intervals
  36. Are column and table name case sensitive in MySQL?
  37. What’s the difference between using INDEX vs KEY in MySQL?
  38. MySQL/Writing file error (Errcode 28)
  39. Case Sensitive collation in MySQL
  40. Update row with data from another row in the same table
  41. Is it possible to GROUP BY multiple columns using MySQL?
  42. MySQL incorrect key file for tmp table when making multiple joins
  43. MySQL ‘UPDATE ON DUPLICATE KEY’ without a unique column?
  44. How to retrieve the current version of a MySQL database management system (DBMS)?
  45. #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’
  46. How to move columns in a MySQL table?
  47. Where to change the value of lower_case_table_names=2 in XAMPP on Windows?
  48. How to add a where clause in a MySQL Insert statement?
  49. How to recover just deleted rows in mysql?
  50. How to remove duplicate entries from a mysql db?

Leave a Comment