Search code examples
sqldatabasefeed

Combining data from different tables to form a news feed

Let's say I have 2 tables, Message and Product. Whenever a user post a new products or messages, users who subscribe to that particular user will have their feed updated.It is similar to Facebook feed.

The problem is how to combine the records from 2 different tables, Message and Product, to make a news feed, the feed has to be sorted by the date posted.

I think it is hard to done using purely SQL, or maybe I need another table and insert new row whenever a new product or message is created?

thanks for reading, hopefully can get some help here, thnx!!

Solution

  • Find common things to message and product, for example they are both type of a post, so you can have something like:

    post_model_01

    Post table has columns common to all posts; message and product tables have only columns specific to each one.


    UPDATE

    To get messages (changed after 2009-11-10 15:00)

    SELECT * 
  FROM Message AS m
  JOIN Post as AS p ON p.PostID = m.PostID
WHERE p.LastChanged > '2009-11-10 15:00'

    To get products (changed after 2009-11-10 15:00)

    SELECT * 
  FROM Product AS d
  JOIN Post as AS p ON p.PostID = d.PostID
WHERE p.LastChanged > '2009-11-10 15:00'

    To get all in one table with NULLS in columns which do not apply to message or a product.

    SELECT * 
  FROM Post AS p
  LEFT JOIN Message AS m p.PostID = m.PostID
  LEFT JOIN Product AS d ON p.PostID = d.PostID
WHERE p.LastChanged > '2009-11-10 15:00'