Combining data from different tables to form a news feed.

Question

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 1

To do this in SQL, you want to do a table join. In this case, you want to join Message and Product by User.

Here is a link to a page on Coding Horror that is the best explanation of joins that I have seen, http://www.codinghorror.com/blog/archives/000976.html

Answer 2

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'