Database Schema Designer

Requirements: a blog with users, posts, and tags. PostgreSQL. Tables & relationships: - users (1) \u2192 posts (many). - posts (many) \u2194 tags (many) via post_tags. CREATE TABLE users ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), email text UNIQUE NOT NULL, name text NOT NULL, created_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE posts ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), author_id uuid NOT NULL REFERENCES users(id), title text NOT NULL, slug text UNIQUE NOT NULL, body text, published_at timestamptz, created_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE tags ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), name text UNIQUE NOT NULL ); CREATE TABLE post_tags ( post_id uuid NOT NULL REFERENCES posts(id) ON DELETE CASCADE, tag_id uuid NOT NULL REFERENCES tags(id) ON DELETE CASCADE, PRIMARY KEY (post_id, tag_id) ); Indexes: CREATE INDEX idx_posts_author ON posts(author_id); CREATE INDEX idx_posts_published ON posts(published_at DESC) for the homepage feed. Notes: many:many tags use a join table (post_tags) with a composite PK to prevent dupes. If you list "posts by tag" constantly, the post_tags(tag_id) lookup is covered by the PK's leading column \u2014 add an index on (tag_id, post_id) if queries lead with tag. Watch the posts feed query as the table grows \u2014 the published_at index keeps it fast.