1

I'm trying to design an activity feed, but I have a small problem.

Here's my current table.

activity

id
user_id
status
activitytype_id (status, video, photo, etc)
created_at
location
video

I didn't add some unrelated columns to make it short.

This works when I want to create a feed for the friends' posts of a user. I basically create an array with PHP and check for user_id's in the activity table and sort them by date.

But a user also follows groups and events, this is the tricky part. Do I need to create a second query for groups and events and merge with the user posts together?

What's the best way to include them (groups and events) in the feed? I can add a column to the activity called event_post and group_post and add posts id's as foreign keys to the activity table (null by default), but I'd like to hear your opinions.

communities

id
communitytype_id (group or event)
name
created_at
user_id (FK from users table, owner of the group or event)
Improve this question

1 Answer 1

Reset to default
2

Database design is tricky. First some suggestions for you:

  • Make sure you understand properly how tables works and what constraints and keys are. Do you know what a unique constraint and a non-null constraint is, how primary keys and foreign keys work?
  • Don't try to skimp on the number of tables. Beginners often have far too few tables in their databases. This actually makes the database more complicated and less efficient, not the reverse. You need to have tables for any 'entity' which exists in your system, even if it's not a concrete 'thing', and even if it's not visible to the user. You are also probably going to want some tables which just express relationships between other entities. Trying to do without these will tie you in knots.
  • Tables in relational databases are very good at storing exactly one different kind of 'thing'. They are very bad at storing more than one completely different kind of thing. It's hard to build tables which work like objects in OO languages, with inheritance and polymorphism.
  • Do you definitely need to use a relational database? You might find that a noSQL solution such as MongoDB is what you need. Typically Mongo (there are lots of similar ones) is used to store collections of 'documents'. It is much harder to store relationships between several collections. But it's much easier to store lots of 'things' together, each of which has slightly different types of information. For example, a document could contain either fields for user information, or for group information, or for event information.

Getting back to your problem, one of the following might work for you.

  • If an activity belongs to a single user, AND a single group, AND a single event, you probably want to have a columns in the activity table for each different type of foreign key. You then probably should do one search for activity by users that are followed, one for groups, and one for events.
  • If an activity can belong to multiple users, groups and events, you should have 'relation tables'. Each of these will be called something like activity_user, and will have a column which is a foreign key to user, and a column which is a foreign key to activity. You link one to the other by creating a new row in this table. One user can have multiple activities, and one activity can have multiple users, and so on. You query the list of activity which belongs to a user by JOINing user, activity and activity_user all together.
  • If the activity belongs to all groups that its user belongs to, you will want a similar association table between groups and users. You should probably look up all the users a group contains, and then look for all their activity.
  • If each activity either belongs to a single user, OR a single group, OR a single event, you should probably divide your activity table into three. Call them useractivity, groupactivity and eventactivity. You do a different query for each of them and then combine the results. These are not 'relation tables' but tables which contain the actual data in each case. This means that it possible to enforce each activity only being associated to one thing. There are other ways of doing this, such as having three foreign key columns, and adding constraints that exactly one of them should be non-null, but they are much uglier and more breakable. One advantage of having these extra tables is that you can them easily add some information to one type of activity which doesn't have to be in the others.
Improve this answer
2
  • Thanks for your answer. You can think of how groups works on Facebook. A group owner can post something, and everybody who follows that group should be able to see that post in their feed. Would it be a good solution if I create a table called feeds and add subscriber_id and activity_id? That way there won't be any duplicates in activities table, but there'll be duplicates in feeds table. For instance, if a group has 400 followers, 400 new records will be generated for each post of the group, but I'll fetch those records by subscriber_id, so I think it will execute fast.
    – salep
    Sep 10, 2015 at 20:36
  • 1
    I'm using Laravel's Eloquent ORM, so most of things are very easy to deal with since I don't deal with joins and complicated queries that much. For instance, I don't create a comment table for each thing (group, event, status comment, etc), I hold every comment in just one table for almost everything, thanks to polymorphic relations feature of Eloquent.
    – salep
    Sep 10, 2015 at 20:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.