How to specify this form of follower/like data in a data-model, ERD, or database schema?

Question

I've been trying to learn more about how to design something like a follower/following/like data struct for a service like instagram. I'm having trouble understanding a few basic things:

• when people say, tell me about the high-level datamodel for instagram, are they asking for ERD or Db schema (besides APIs)? is there a difference between Entity-relationship diagram and mysql schema?

• Conceptually a user has multiple followers/followees. For followers, a User entity in ERD, can have a "list of follower Ids" as a field. So the "user" entity will have a many-to-one relationship with itself, i.e. a many-to-one arrow would point from the User's followers field to User itself? Is something like this even possible in ERD?

• How does one specify a "List"/"Array" of follower IDs in actual DBs (as "list/vector/array" seems to be more like C/C++ terminology)?

Answer 1

There is a difference between an ER diagram and a DB schema, but you can convert one into the other. A schema contains database specific information, like where tables are located (schema), how indexes are organized and lot of that stuff. A ERD is at the conceptional layer. And there is no single ER Diagram notation, there are several specified and the tools mix them.

Something about database and recursion

The first recursion you should know is a tree. It is used for directed graphs with a single root. Like a company and its sub companies. This is a so called "fishhook" in an ER Diagram (to be exact: It's a fishhook when it has a direction and an existance depends on the root object).

+----------+
| company  | <---+
+---+------+  *  |
    |1           |
    +------------+
     owns

But what you need is not a tree. A follows relation is a many to many relation with no direction. So we have to change the model:

+----------+
| user     | ----+
+---+------+ *   |
    |*           |
    +------------+
     follows

To archive this, you need a table to store the relations. Let us call the table follower. It will contain the id of the follower and the followed user. For each unique combination there is an entry in the database. A script to create it, (in postgres dialect):

CREATE TABLE public.user (
    id bigint NOT NULL,
    name character varying(100) NOT NULL
); 

CREATE TABLE public.follower(
    id bigint NOT NULL,
    follower_id bigint NOT NULL
);

-- pk user
ALTER TABLE ONLY public.user
    ADD CONSTRAINT user_pkey PRIMARY KEY (id);

-- pk follower
ALTER TABLE ONLY public.follower
    ADD CONSTRAINT follower_pkey PRIMARY KEY (id, follower_id);

-- foreign keys

ALTER TABLE ONLY public.follower
    ADD CONSTRAINT follower_fk1 FOREIGN KEY (id) REFERENCES public.user(id);

ALTER TABLE ONLY public.follower
    ADD CONSTRAINT follower_fk2 FOREIGN KEY (follower_id) REFERENCES public.user(id);

Some impacts of this model

• If you have a graph like that: bob follows judy, judy follows bob you may end in an endless loop.

• If you want to find followers of followers ... you have to query several times. There are some vendor specific extensions for recursive queries, but plain ANSI SQL doesn't support this (and so your OR Mapper will no support it out of the box).

My point of view: It is a model, where a relational database doesn't fit well. A graph DB, like neo4j is a better choise for that case.