Social Network

A reaction is an entity, not a many-to-many between user and post: it has a type and a timestamp, and "one reaction per post per user" is a rule the diagram can only document.

Intermediate

The diagram

(0,N)(1,1)(0,N)(1,1)(0,N)(1,1)(0,N)(1,1)UserPostCommentReactionWritesCommentsOnReactsOnPostusernamedisplay_nameemailpost_nobodypublished_atcomment_nobodywritten_atreaction_typecreated_at
Sign in to open and edit

Opening an example creates a copy in your own projects. The original example is never modified.

The problem

A small social network has to store what its members publish and how the others respond. A member is known by a username, which is unique and which appears next to everything they write; the profile also keeps a display name and an e-mail address. A post is a piece of text published at a given moment by exactly one member. Other members comment on a post: a comment is a short text written under the post, and the interface shows comments only inside the page of the post they belong to. Members also react to a post - like, love, laugh - and each reaction is recorded with the moment in which it was given. The product rule is strict: a member has at most one reaction per post, and if they change their mind the reaction changes type instead of being added twice. The moderation team also wants to know how many reactions of each type a post received and in which order they arrived.

Requirements

  • The system must record members, each with a unique username, a display name and an e-mail address.
  • The system must record posts, each with a unique post number, a body, a publication timestamp and exactly one author.
  • A member may write zero, one or many posts; each post has exactly one author.
  • The system must record comments, each with a unique comment number, a body, a timestamp and exactly one post it belongs to.
  • A post may have zero, one or many comments; each comment belongs to exactly one post.
  • The system must record reactions, each with a type and the moment in which it was given; every reaction is made by exactly one member and refers to exactly one post.
  • A member may make zero, one or many reactions, and a post may receive zero, one or many reactions.
  • A member may have at most one reaction per post: a second reaction on the same post replaces the type of the first instead of creating a new one.

Entities and attributes

Entity Attributes Identifier
User username, display_name, email username
Post post_no, body, published_at post_no
Comment comment_no, body, written_at comment_no
Reaction reaction_type, created_at (username, post_no)

Relations and cardinalities

Relation From To Cardinality (min, max) Note
Writes User Post (0,N) – (1,1) A member takes part in zero, one or many posts (0,N); each post has exactly one author (1,1), so the author is never missing and never stored twice.
CommentsOn Post Comment (0,N) – (1,1) A post collects zero, one or many comments (0,N); each comment belongs to exactly one post (1,1). This is the thread: a comment exists only inside a post.
Reacts User Reaction (0,N) – (1,1) A member makes zero, one or many reactions (0,N); each reaction has exactly one author (1,1). This is the first half of the external identifier of Reaction.
OnPost Reaction Post (0,N) – (1,1) Read together with Reacts, the two diamonds say that one reaction is one member on one post, while a post collects many reactions. This is the second half of the external identifier.

Reading the diagram

  • Four entities appear in the diagram. User is identified by username, Post by post_no and Comment by comment_no. Reaction has no key attribute at all, and that is the teaching point of this example.
  • The obvious design for a reaction is a many-to-many between User and Post, with reaction_type and created_at written on the diamond: a member reacts to many posts, a post collects reactions from many members. That design is not absurd, and in the relational schema it produces exactly the same table. The entity version was chosen because a reaction is a thing the product talks about: it has a type, an instant, and later it may need to be referenced by a notification or by a moderation decision. A diamond cannot be referenced; a node with a name can.
  • The identity of a reaction is external: it is the pair (username, post_no). An external identifier is what an entity uses when it has no attribute of its own able to distinguish its instances, and here it coincides with the product rule. Saying "a member has at most one reaction per post" and saying "the pair (member, post) is the identifier of a reaction" are the same statement. The diagram cannot declare it - there is no notation for a pair of foreign identifiers used as a key - so the pair is written in this documentation and becomes the composite primary key of the logical table.
  • Writes is the one-to-many that gives every post an author. (0,N) on the User side means a member takes part in zero or more posts, and (1,1) on the Post side means each post has exactly one author. The minimum 1 matters: it is the difference between "the author is a relationship" and "the author is an optional attribute that may be missing", and it is what allows the page of a post to always print a name.
  • CommentsOn builds the thread. A post collects zero or more comments, and each comment belongs to exactly one post. Comment, unlike Reaction, has an identifier of its own - comment_no - because a comment is not identified by the pair (author, post): a member may write several comments under the same post. That single difference explains why one of the two entities needs a surrogate number and the other does not.
  • Reacts and OnPost are the price of turning the many-to-many into an entity: two diamonds instead of one. Their minimums are both 1, so a reaction cannot exist without an author and without a post, and neither link can be null. The same structure also implies something the requirements did not say: a reaction cannot be moved from one post to another, because the post is part of its identity. In practice that is correct - changing the post would mean deleting the reaction and creating a new one - but it is a semantic consequence, not an accident of the drawing.
  • What the diagram cannot say is in the assumptions: that (username, post_no) is unique, that reaction_type comes from a closed set (like, love, laugh), that a comment cannot be moved between posts, and that a username may change. The first is enforced by the composite key, the second by a CHECK or by a domain table, the last by a deliberate choice between cascading updates and a surrogate key. Naming these limits is part of the design; the E-R model is a language for structure, not for every rule a product needs.

From the conceptual model to the logical schema

User(username, display_name, email)
  PRIMARY KEY (username)
Post(post_no, body, published_at, username)
  PRIMARY KEY (post_no)
  FOREIGN KEY (username) REFERENCES User(username)
Comment(comment_no, body, written_at, post_no)
  PRIMARY KEY (comment_no)
  FOREIGN KEY (post_no) REFERENCES Post(post_no)
Reaction(username, post_no, reaction_type, created_at)
  PRIMARY KEY (username, post_no)
  FOREIGN KEY (username) REFERENCES User(username)
  FOREIGN KEY (post_no) REFERENCES Post(post_no)