Database Schema Designer

What you’re building

Database design is one of the most important skills in the MIS curriculum — and one of the most tedious to practice. You sketch ER diagrams on paper, translate them into SQL by hand, create sample data row by row, then discover you forgot a foreign key and start over.

In this lesson you will build a database schema designer that lets you describe entities in plain English and instantly generates ER diagrams (as SVG), SQL CREATE TABLE statements, sample INSERT data, and a normalization checklist. Toggle between MySQL and PostgreSQL syntax. The whole thing runs as a React + Vite application on your laptop.

This is a step up from Lesson 1. Instead of a single HTML file, you are building a proper React application with components, state management, and a development server. The LLM handles all of that scaffolding — you just describe what you want.

The showcase

When finished, your app will have:

• Input panel: a textarea where you describe your database in plain English (e.g., “Customers place Orders. Each Order has multiple Order Items. Products belong to Categories.”).
• Parse button that extracts entities, attributes, and relationships from your description.
• Entity editor: cards for each detected entity showing its attributes with data types, primary keys, and foreign keys. You can edit, add, or remove attributes directly.
• ER diagram: a live SVG diagram rendered with entity boxes, relationship lines (crow’s foot notation), and cardinality labels (1:1, 1:M, M:N).
• SQL output panel with tabs for:
  • CREATE TABLE statements (with proper constraints, foreign keys, indexes)
  • Sample INSERT statements (5-10 realistic rows per table)
  • DROP TABLE statements (in correct dependency order)
• Dialect toggle: switch between MySQL and PostgreSQL syntax (data types, auto-increment vs. SERIAL, backticks vs. double quotes).
• Normalization checklist: highlights potential 1NF, 2NF, and 3NF violations based on the schema structure.
• Copy/Download buttons for the SQL output.

The prompt

Navigate to an empty folder, open your AI CLI tool (such as Claude Code, Gemini CLI, or your preferred tool), and paste this prompt:

Create a React + Vite application for database schema design. Use TypeScript and
Tailwind CSS. The app should be called schema-designer.

CORE FUNCTIONALITY:

1. INPUT PANEL
   - Large textarea for describing a database in plain English
   - Example text pre-filled: "A university system: Students enroll in Courses.
     Each Course is taught by a Professor. Professors belong to Departments.
     Students receive Grades for each Course they are enrolled in."
   - "Parse Schema" button that extracts entities and relationships
   - "Load Example" buttons for 3 built-in scenarios:
     a) University (Students, Courses, Professors, Departments, Enrollments)
     b) E-commerce (Customers, Orders, OrderItems, Products, Categories)
     c) Hospital (Patients, Doctors, Appointments, Departments, Prescriptions)

2. ENTITY PARSING LOGIC
   - Extract entity names from nouns in the description
   - Infer relationships from verbs: "places" = 1:M, "belongs to" = M:1,
     "enroll in" = M:N (junction table needed)
   - Auto-generate sensible attributes:
     - Every entity gets an ID primary key (auto-increment)
     - String entities get a "name" VARCHAR(255)
     - Date-related entities get created_at TIMESTAMP
     - Junction tables get composite primary keys
   - Auto-detect foreign keys from relationships
   - Users can edit everything after parsing

3. ENTITY EDITOR
   - One card per entity, showing:
     - Entity name (editable)
     - Attribute list: name, data type (dropdown: INT, VARCHAR, TEXT, DATE,
       TIMESTAMP, DECIMAL, BOOLEAN), constraints (PK, FK, NOT NULL, UNIQUE,
       DEFAULT), referenced table (for FKs)
     - "Add Attribute" button
     - "Delete Entity" button
     - Drag handle to reorder attributes
   - "Add New Entity" button below all cards
   - Changes in the editor instantly update the ER diagram and SQL output

4. ER DIAGRAM (rendered as SVG)
   - Entity boxes with entity name as header, attributes listed inside
   - Primary keys marked with a key icon or underline
   - Foreign keys marked with FK label
   - Relationship lines between entities using crow's foot notation:
     - 1:1 = single line on both ends
     - 1:M = single line on one end, crow's foot on the other
     - M:N = crow's foot on both ends (show junction table)
   - Lines should route cleanly without overlapping entity boxes
   - SVG is zoomable and pannable (use a transform on the SVG group)
   - "Download SVG" button to save the diagram

5. SQL OUTPUT
   - Tabbed panel with three tabs:
     a) CREATE TABLE: properly formatted DDL with:
        - Primary keys, foreign keys with REFERENCES
        - NOT NULL constraints where appropriate
        - Indexes on foreign key columns
        - Comments on each table
     b) INSERT DATA: 5-10 rows of realistic sample data per table
        - Names should be realistic (not "test1", "test2")
        - Dates should be reasonable (within last 2 years)
        - Foreign key values must reference existing PKs
        - INSERT order respects foreign key dependencies
     c) DROP TABLE: in reverse dependency order (so FKs don't break)
   - Dialect toggle at the top: MySQL | PostgreSQL
     - MySQL: AUTO_INCREMENT, backtick identifiers, ENGINE=InnoDB
     - PostgreSQL: SERIAL, double-quote identifiers, no engine clause
   - "Copy to Clipboard" button for each tab
   - "Download .sql" button that downloads the active tab as a .sql file

6. NORMALIZATION CHECKLIST
   - Side panel or collapsible section that checks:
     - 1NF: Are all attributes atomic? Flag any VARCHAR that contains commas
       or lists in sample data
     - 2NF: In tables with composite keys, do all non-key attributes depend
       on the entire key? Flag potential partial dependencies
     - 3NF: Are there transitive dependencies? Flag attributes that might
       depend on non-key attributes (e.g., department_name in a student table
       that has department_id)
   - Show pass/warn/fail status for each normal form per table
   - Brief explanation of each violation with suggestion to fix it

7. DESIGN
   - Dark theme: bg-slate-950, cards bg-slate-900, text-slate-200,
     accent blue-400
   - Split layout: input + entity editor on the left, ER diagram + SQL on
     the right
   - Responsive but optimized for desktop/laptop (this is a workstation tool)
   - ER diagram should be the visual centerpiece

Generate the complete application with all components.
Include a README with setup instructions (npm install && npm run dev).

What you get

The LLM will generate a project structure like this:

schema-designer/
├── index.html
├── package.json
├── tsconfig.json
├── vite.config.ts
├── tailwind.config.js
├── postcss.config.js
├── src/
│   ├── main.tsx
│   ├── App.tsx
│   ├── index.css
│   ├── types.ts
│   ├── utils/
│   │   ├── parser.ts          # plain English to entities
│   │   ├── sqlGenerator.ts    # entities to SQL DDL/DML
│   │   ├── sampleData.ts      # realistic INSERT generation
│   │   └── normalizer.ts      # normalization checks
│   └── components/
│       ├── InputPanel.tsx
│       ├── EntityEditor.tsx
│       ├── ERDiagram.tsx
│       ├── SQLOutput.tsx
│       ├── NormalizationPanel.tsx
│       └── DialectToggle.tsx

To run it:

cd schema-designer
npm install
npm run dev

Open http://localhost:5173 in your browser. Click Load Example to see the University schema.

Expected behavior with the University example

• Five entity cards appear: Students, Courses, Professors, Departments, and Enrollments (the junction table for the M:N relationship between Students and Courses).
• The ER diagram shows five boxes connected with relationship lines. Enrollments connects to both Students and Courses with crow’s foot notation.
• The SQL CREATE TABLE tab shows proper DDL: Students has a student_id PK, Enrollments has a composite PK of (student_id, course_id), and foreign keys reference the correct parent tables.
• The INSERT tab shows realistic data: student names like “Maria Chen” and “James Thompson”, course names like “Introduction to Database Systems” and “Business Statistics”.
• The normalization checklist should show all green for a well-designed schema. If you manually add a “department_name” column to the Students table, it should flag a 3NF violation.

Common issues and fixes

When things go wrong

SQL generation and ER diagram rendering each have their own category of issues. Here is a structured approach for diagnosing the most common problems.

Understanding the architecture

Here is how the pieces connect:

1. Parser (parser.ts) does basic NLP: it splits the input into sentences, identifies nouns as entities, verbs as relationships, and infers cardinality from patterns like “each X has multiple Y” (1:M) or “X enroll in Y” (M:N). It is not a full NLP engine — it uses pattern matching, which is good enough for structured descriptions.

2. Entity state lives in React state (or a context provider). Every component reads from the same state: the Entity Editor mutates it, the ER Diagram renders it, and the SQL Generator transforms it.

3. SQL Generator (sqlGenerator.ts) walks the entity list and produces DDL strings. The dialect parameter switches between MySQL and PostgreSQL syntax. The key differences: auto-increment syntax, identifier quoting, and engine declarations.

4. ER Diagram (ERDiagram.tsx) renders an SVG element. Each entity is a group of rectangles and text elements. Relationships are SVG paths between entity groups. Crow’s foot notation is drawn with small line segments at the endpoints.

5. Normalization checker (normalizer.ts) applies heuristic rules: if a table has a composite key, it checks whether every non-key column depends on the full key (2NF). If a table has columns that look like they belong to another entity (e.g., department_name when department_id exists), it flags a 3NF issue.

Customize it

Add reverse engineering from SQL

Add a "Reverse Engineer" tab to the input panel. Let the user paste existing
CREATE TABLE SQL statements and parse them back into entities. Extract table names,
column names, data types, primary keys, foreign keys, and relationships. Populate
the entity editor and ER diagram from the parsed SQL. Support both MySQL and
PostgreSQL syntax.

Add data dictionary export

Add a "Data Dictionary" tab to the output panel. Generate a formatted table for
each entity with columns: Attribute Name, Data Type, Constraints, Description
(auto-generated from the attribute name, e.g., "Unique identifier for the student"),
Example Value (from the sample data). Add a "Download CSV (Excel-compatible)" button that exports
the data dictionary as a CSV file formatted for easy import into Excel.

Add query builder

Add a "Query Builder" tab. Show a visual query builder where the user can:
- Select tables to JOIN (click entities in the ER diagram)
- Pick columns to SELECT (checkboxes)
- Add WHERE conditions (column, operator, value dropdowns)
- Add GROUP BY and ORDER BY
- See the generated SQL query update live
- Copy the query to clipboard
Support INNER JOIN, LEFT JOIN, and RIGHT JOIN.

Add schema versioning

Add schema version tracking. Every time the user modifies the schema, save a
snapshot with a timestamp. Show a version history sidebar where users can:
- See a list of all versions with timestamps
- Click a version to view the schema at that point
- Compare two versions and see what changed (added/removed/modified tables and columns)
- Restore a previous version
Use localStorage to persist versions across browser sessions.

Try it yourself

1. Build the app using the prompt above.
2. Click Load Example and explore the University schema.
3. Try describing your own database: think about a system you use daily (library catalog, restaurant ordering, gym membership tracking) and describe it in 2-3 sentences.
4. Edit the generated entities — add attributes, change data types, create new relationships.
5. Toggle between MySQL and PostgreSQL and notice the syntax differences.
6. Check the normalization panel. Intentionally add a redundant column and see if it gets flagged.
7. Copy the SQL and paste it into your database course’s MySQL Workbench or pgAdmin — it should often run without errors. If you get a syntax error, paste the database error into your CLI tool and ask for corrected SQL.

Key Takeaways

• Plain English to SQL is a real workflow. In industry, database design starts with stakeholder conversations, not code. This tool mirrors that process: describe the business domain, then generate the technical artifacts.
• ER diagrams, DDL, and sample data are three views of the same model. Changing one should update the others. This is the “single source of truth” principle you will see in every enterprise system.
• Normalization is about eliminating redundancy. The checker flags potential issues, but you make the design decision. Sometimes denormalization is intentional (for performance), and that trade-off judgment is what separates a database user from a database designer.
• Dialect differences are smaller than you think. MySQL and PostgreSQL are 90% identical in DDL syntax. Knowing the 10% that differs (auto-increment, identifier quoting, engine declarations) is enough to work across both.
• Iterative design beats big upfront planning. Describe a few entities, check the ER diagram, adjust, add more. This is how real database projects work — not waterfall, but continuous refinement.

Portfolio Suggestion

This schema designer is one of the strongest portfolio pieces for MIS graduates targeting data analyst, business analyst, or database administrator roles. To present it effectively:

• Deploy the React app to Vercel or Netlify (free hosting). Include the live URL on your resume.
• Create three example schemas relevant to your target industry: a CRM system for sales roles, an inventory management system for supply chain roles, or a patient records system for healthcare IT.
• Export the ER diagrams as SVGs and include them in your capstone project documentation.
• Save the generated SQL files and show that they run without errors in MySQL Workbench or pgAdmin — this demonstrates that the tool produces portfolio-quality prototype output. Validate any generated SQL before claiming production readiness.
• If you added the reverse engineering feature, demonstrate it with a real database schema from a class project. Being able to go both directions (English to SQL and SQL to ER diagram) shows comprehensive understanding.

What’s next

In Lesson 3, you will build a Project Management Tracker with Kanban boards, Gantt charts, and burndown tracking. It is the kind of tool you would compare to Jira or Asana — and you will build it with a single prompt.