Social media database (DBML)

Updated
View as text
Open in Confluence
Open in ChatGPT Open in Claude

The classic social schema is a good example because it contains both a self-referencing many-to-many and a polymorphic-looking relationship handled honestly.

#What this example shows

  • A join table for follows, referencing the same table twice.

  • Composite primary keys on join tables.

  • Separate like tables rather than a polymorphic one.

#A worked example

Open it, edit it, or copy the source below.

Social media database (DBML)

The source, which you can paste into a new diagram and edit:

Table users {
  id uuid [pk]
  handle varchar(32) [unique, not null]
  display_name varchar(255)
  bio text
  created_at timestamptz [default: `now()`]
}

Table follows {
  follower_id uuid [ref: > users.id]
  followee_id uuid [ref: > users.id]
  created_at timestamptz [default: `now()`]

  Indexes {
    (follower_id, followee_id) [pk]
    (followee_id)
  }
  Note: 'Directed: following is not mutual'
}

Table posts {
  id uuid [pk]
  author_id uuid [ref: > users.id, not null]
  body text [not null]
  reply_to_id uuid [ref: > posts.id, note: 'Null for a top-level post']
  created_at timestamptz [default: `now()`]

  Indexes {
    (author_id, created_at)
    (reply_to_id)
  }
}

Table post_likes {
  user_id uuid [ref: > users.id]
  post_id uuid [ref: > posts.id]
  created_at timestamptz [default: `now()`]

  Indexes {
    (user_id, post_id) [pk]
  }
}

#Making it your own

  • Two references to the same table is how you model a directed relationship between peers.

  • A composite primary key on a join table prevents duplicates for free.

  • Resist a polymorphic likes table; separate tables keep the foreign keys real.

Close enough to the schema to stay true.