Creating a feed wall

Question

I have a table of users and a table of questions. The questions table will have records that represent "Questions", "Answers" and "Comments" to questions or answers. I'd like to create a dashboard for each user in which its possible to see activities related to questions and answers. For example if user A creates a question and user B responds with an answer and user C responds with a comment to user B's answer, then users A and B are able to see all of that activities in their dashboards.

This works similar to the way that Facebook home page works where if I put up a video I can see people's comments on my video.

Can anyone suggest a simple way to model this in a database?

Answer 1

I believe Facebook et. al. handle this by having a separate database of generic "update" records, which have a short description of the action taken. Whenever someone performs an action on the site that should show up in the Wall, a record is inserted in the update database as well.

Answer 2

Ok here is what you can try.. you can't mess up questions, answers and their comments in the same table.. One question can have many answers and different answers can have different comments. Keeping all of them in same table will be quite messy and not recommended (against the rules of RDBMS). Make three separate tables one for Questions, one for Answers and one for Comments. Define relations between them based on the primary and foreign keys.. So at any point time in question table you have all the questions asked by all the users. The Answer table will have all the answers posted corresponding to that particular question and the comment table will have the comments to the corresponding answer. Maintaining proper supporting columns in all tables you can easily manage what you want. At any point of time you can see all the answers related to a particular question by joining Question and Answer table, also you can view all the comments made on a particular answer by joining Answer and Comment table..

Hope this gives you an IDEA..

Answer 3

If you are going to use one table, you might want to try something like this.

TABLE{ ID, ParentID, UserID, Type, Text, PostDtm }

This gives you a basic tree structure in one table.

The trade off is that you may have more complex queries and there may be more of them needed to achieve what you want.

make sure you have indexes on the id columns at minimum.

I still am unsure about how much performance you need. A site like facebook has to consider much more than table level optimizations. For most sites a textbook relational model can be queried more efficiently than this model. What are your performance targets?

Answer 4

enter code here@Am, my assumption is to bite the bullet and if using MySQL then use relationships or move to a different type of database if you want to totally get away from tables with relationships. You desire a "clean" implementation of atypical of the RDB model. For RDB, I suggest you use four tables: users, user_map, questions, responses. Responses are categorical (e.g. to a Question or Answer) so track which type it is where the only foreign keys in any table are question.id and user.id.

Users: id, name
User_map (can be used with Questions and/or Responses to join the data): u_id, q_id
Questions: id, text_value
Responses: id, q_id, text_value, category

Have you truly determined that relationships are something you need to optimize out or are just over analyzing it? If you need to get away from a relationship based database then I would suggest looking into technologies that are built for it.