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!!

Answer

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



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'