## Relational Database Schema Diagram: Academic Paper Management System
### Overview
The diagram illustrates a relational database schema for an academic paper management system, depicting seven core tables (`users`, `papers`, `communities`, `paper_engagement`, `paper_analysis`, `community_papers`, `paper_discussions`) and their relationships. Tables are connected via labeled lines representing business logic (e.g., "writes," "has," "receives").
---
### Components/Axes
#### Tables and Columns
1. **users**
- Columns: `uuid` (PK), `id`, `email` (string), `full_name` (string), `avatar_url` (string)
- Primary Key: `uuid`
2. **papers**
- Columns: `uuid` (PK), `id`, `title` (string), `abstract` (text), `url` (string), `pdf_url` (string), `arxiv_id` (string), `doi` (string), `authors` (JSON), `year` (int), `venue` (string), `citation_count` (int)
- Primary Key: `uuid`
3. **communities**
- Columns: `uuid` (PK), `id`, `name` (string), `slug` (string), `description` (string), `image_url` (string)
- Primary Key: `uuid`
4. **paper_engagement**
- Columns: `uuid` (PK), `user_id` (FK to `users.uuid`), `paper_id` (FK to `papers.uuid`), `engagement_type` (string: like, view, save), `created_at` (timestamp)
- Foreign Keys: `user_id`, `paper_id`
5. **paper_analysis**
- Columns: `uuid` (PK), `paper_id` (FK to `papers.uuid`), `analysis_data` (JSON: Nodes & Edges), `markdown_summary` (text), `mindmap_mermaid` (text), `flowchart_mermaid` (text), `created_at` (timestamp)
- Foreign Keys: `paper_id`
6. **community_papers**
- Columns: `uuid` (PK), `community_id` (FK to `communities.uuid`), `paper_id` (FK to `papers.uuid`), `added_at` (timestamp)
- Foreign Keys: `community_id`, `paper_id`
7. **paper_discussions**
- Columns: `uuid` (PK), `paper_id` (FK to `papers.uuid`)
- Foreign Keys: `paper_id`
#### Relationships
- **users** ↔ **papers**: 1-to-many via "writes" (users write papers)
- **papers** ↔ **communities**: 1-to-many via "belongs_to" (papers belong to communities)
- **papers** ↔ **paper_engagement**: 1-to-many via "has" (papers have engagement records)
- **papers** ↔ **paper_analysis**: 1-to-1 via "has_one" (papers have analysis records)
- **communities** ↔ **community_papers**: 1-to-many via "contains" (communities contain papers)
- **papers** ↔ **paper_discussions**: 1-to-many via "has" (papers have discussions)
---
### Detailed Analysis
- **users**: Stores user metadata (email, name, avatar URL).
- **papers**: Central table with metadata (title, abstract, URLs) and structured data (authors as JSON, citation count).
- **communities**: Represents academic groups with descriptive fields (name, slug, image URL).
- **paper_engagement**: Tracks user interactions (likes, views, saves) with timestamps.
- **paper_analysis**: Stores computational analysis outputs (mindmaps, flowcharts) and summaries.
- **community_papers**: Links communities to papers with timestamps for inclusion.
- **paper_discussions**: Junction table for paper-specific discussions (many-to-many relationship implied).
---
### Key Observations
1. **Normalization**: Tables are normalized to 3NF, with minimal redundancy (e.g., `authors` stored as JSON in `papers`).
2. **Temporal Tracking**: Timestamps (`created_at`, `added_at`) enable audit trails for engagement and community inclusion.
3. **Modular Analysis**: `paper_analysis` decouples raw data from derived insights (e.g., Mermaid diagrams for visualizations).
4. **Community-Paper Relationships**: Many-to-many via `community_papers`, allowing papers to belong to multiple communities.
---
### Interpretation
This schema supports a collaborative academic ecosystem where:
- **Users** contribute **papers** to **communities**, which curate and discuss them.
- **Engagement metrics** (likes, views) and **analysis outputs** (summaries, visualizations) enhance paper discoverability and utility.
- **Temporal data** (`created_at`, `added_at`) allows tracking of paper lifecycle and community adoption.
- **JSON fields** (`authors`, `analysis_data`) accommodate semi-structured data, balancing flexibility with relational integrity.
The design prioritizes scalability for large datasets (e.g., citation counts, community memberships) while enabling complex queries (e.g., "find papers with >100 citations in neuroscience communities").
