I would create something as:

“users”: id, email …
“followings”: id, user_id, follower_id

unique constraing on user_id and follower_id. Both user_id and follower_id are referencing users table. Just that the one is user being followed, the other one is user who is following that user.

@hubertlepicki answer is exactly right.

In a more general sense, what is being suggested is a join table. A join table is used when we need to express has and belongs to many relationships.

In your specific case, the join table is expressing the notion that a user has many followers, and can also belong to many followers.

This is good usage for some graph db like https://neo4j.com/
Import from a relational database into Neo4j - Getting Started

I would say it highly depends on particular use case, while in theory graph dbs are supposed to be faster for working with graphs in reality they are often not and you have to maintain an extra dependency while losing the ability to do transaction for part of your data and a ton of other inconveniences.

(How do you reply to a post as a new topic?)

I’ve always written a join table like followings as:

followings: user_id follower_id

With no base ‘id’ column as I’ve found them useless on join tables, I then make user_id and follower_id a combined primary key with indexes on user_id or follower_id depending on which direction (or both) I expect to be accessing it from. Is there actually a good reason to put an ID column on a pure many-to-many join table I am curious?

How do you link the posts with the user and make the “timeline” query?

In twitter, I’m able to see all posts of everybody I’m following. So, if I use an inter-table as mentioned then I need to join it with the Posts table. That join will probably not perform very well if I’m following too many users since the key cardinality will be big (because I will use the composite key (user_id + following_id) joined with “owner_id” from posts table.

Do you think is there any other solution for displaying the timeline? (timeline = posts from people I follow). I know some hierarchical solution in NoSQL databases, but I never implemented that in Relational ones.

at small scale it might be OK
at larger scale you might be caching the “followings” table
and posts in something like Redis or replicating everything to ElasticSearch
and running queries against it.

yes in theory you don’t need it. In practice you often want to store some extra fields in the table, simple example is timestamps inserted_at. I don’t think Ecto will create/update those unless it’s a full model behind it, so, having an id and model is necessity (but please do double check my words here, I assume this is how it works but never actually tried it without).

2nd thing is that if you are following the REST principles in your API urls, you can have actions such as DELETE /followings/:id if you have ID of the resource this way.

@hubertlepicki @StevenXL @OvermindDL1 thank you for your replies.

This is how I built the relationships.

schemas

schema “followings” do
belongs_to :user, User
belongs_to :follower, Follower

schema “users” do
has_many :followings, Following

migration

create table(:followings) do
add :user_id, references(:users, on_delete: :nothing)
add :follower_id, references(:users, on_delete: :nothing)

indexes

create index(:followings, [:user_id])
create index(:followings, [:follower_id])
create index(:followings, [:user_id, :follower_id], unique: true)

Just a nitpick I’m sure,

To me personally this reads as if the people (followers) following the user are being modeled, not the people that the user is following. (Is that the intent?)

Now apparently the term “followee” is actually a thing but it’s not universally liked. The suggested term “subject” seems more appropriate and less controversial for identifying those who are being followed.

An alternate term for join table is association table (or associative entity) - good to know for googling additional information. In fact José Valim has a blog entry “Working with Ecto associations and embeds” discussing the technical aspects of using associations in Ecto.