Relational Database App

A MySQL editorial-management system for an academic journal, driven from a Python command-line client. People sign in as an Admin, Author, Reviewer, or Editor, and the schema carries a manuscript through its whole life: submission, assignment to reviewers, scoring, an accept/reject decision, typesetting, and finally placement in a published issue. The invariants that keep that pipeline honest live in SQL triggers and stored routines, not in the client.

Manuscript is the hub of the schema. Each manuscript carries a research-interest code (RICodes), an assigned Editor, and, once placed, an Issue. Two junction tables record the many-to-many facts: Manuscript_Author links a manuscript to its authors and keeps an author_ordinal so the lead author is just ordinal 1, and Reviewer_has_Manuscript holds each reviewer's five scores (appropriateness, clarity, methodology, experimental, recommendation), constrained to the range 1 to 10. Reviewer_has_RICodes and Journal_has_RICodes attach interest codes to reviewers and journals; Author and Reviewer both point at Affiliation, and Editor and Issue both point at Journal. A single credentials table unifies logins across all four roles through a (user_type, type_id) pair.

Triggers move the workflow rules out of the client and into the engine, where they fire for every writer. AutoRejectManuscriptOnNoReviewers runs before a manuscript is inserted: if no reviewer holds its research-interest code, the row is stamped rejected on arrival rather than entering a queue that can never clear. When a reviewer resigns, DeleteAssignmentOnReviewerResign clears their assignments and code links, and ResetManuscriptStatusonReviewerResign inspects each manuscript they leave behind: if it now has no reviewer but another qualified one exists, the manuscript is reset to Submitted and a SIGNAL message is raised; otherwise it is set to Rejected. AutoAcceptManuscript collapses a step: a status set to Accepted immediately becomes Typesetting. And IndexAuthor, IndexReviewer, and IndexEditor each fire after an insert to create the matching credentials row, so every new person is a valid login without a second statement from the client.

Decision logic and read-side rollups round out the database side. The MakeDecision procedure averages a manuscript's reviewer scores and returns Accepted when the total reaches 40, Rejected below it. The rollups are packaged as views: ReviewQueue gathers every under-review manuscript with its reviewers concatenated into one row, PublishedIssues lists the contents of each completed issue in page order, and LeadAuthorManuscripts filters Manuscript_Author down to author_ordinal = 1.

The Python side stays thin. main.py opens the connection and reads a user ID; role modules (author.py, editor.py, reviewer.py) issue parameterized statements through dbutils.py. A /rebuild flag reconstructs the tables and a /populate flag seeds sample data; otherwise only the two admin accounts exist. The interesting work sits in the schema: the constraints, the automation, the consistency guarantees.

Lookups by key resolve through the engine's indexes — balanced B-trees — rather than a full table scan.

Collaborative project with Ke Lou.