How can I structure my DB table(s) to allow users to follow multiple tables( people, topics, questions,etc)

Question

I want to allow users on my site to follow other users and topics on my site but I'm unsure how to combine them both. Think about how quora allows you to follow users, topics, questions, edits,etc..

To follow users I was going to keep it extremely simple:

user_id: int
followers_id: int

but this is only good for users so I thought about how:

user_id: int
date: Date
following: int(could be topic_id or user_id)
following_type: text(topic or user)

I'm a bit unsure if this is the best way to do this since I'll have to parse the type to query either the topic or user table(but the benefit is since all the data is in one table I can quickly get all following info with one query).

Is this the best way or is there a better(more efficient, scalable,etc.maybe.) way to achieve this? Should I be splitting each follower type into different tables? I'm really unsure what design I should be considering.

Answer 1

The obvious possibility is to proliferate link tables - one for each combination of the kinds of items that can be connected.

However, avoiding this proliferation by using inheritance¹ will probably be more manageable:

(Relationships between various followables not shown.)

This way, no matter how many kinds of followable items there are (user, topic, question, edit etc...), the FOLLOWER table covers them all.

There are generally 3 strategies for representing inheritance in relational databases. For more info, take a look at "Subtype Relationships" in the ERwin Methods Guide.

---

¹ Aka category, subtype, subclass, generalization hierarchy etc.

Answer 2

There are a few different ways you can handle this problem. If you want to stay with a relational-DB, you would create a join table (or multiple) that will keep track of all your "follows".

If you have the time to take a looking at some non-relational databases, like MongoDB or Redis, you will be able to keep track of exactly what you want. These services allow you to structure custom data types with varying parameters. It may be worth reading about this style of DB.