Database Schema Designer
Last reviewed
IntermediateWhat you'll learn
~45 min- Build a React application that converts plain English descriptions into database schemas
- Understand how entity-relationship modeling maps to SQL DDL statements
- Recognize normalization violations (1NF, 2NF, 3NF) and know how to fix them
- Generate portfolio-quality SQL for both MySQL and PostgreSQL dialects
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.
Natural language input → formal model → target syntax output. This pattern powers code generators, config builders, and any tool that translates human intent into structured output.
This lesson uses React and Vite, which are more complex than the single HTML file in Lesson 1. If you have not used React before, that is fine — the AI handles the setup. But expect more files and a longer initial build. See Folder Structure for how React projects are organized.
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.
In foundational database courses, you design schemas, draw ER diagrams, and write DDL statements as core assignments. This tool lets you iterate on designs in seconds instead of hours. More importantly, seeing how plain English maps to relational structures builds the intuition you need for database design and real-world data modeling.
Before you start
First, if your AI tool is still open, leave it: type /exit and press Enter (if you use Antigravity, press Ctrl+D with nothing typed instead), and wait until you see the $ or % prompt. Make a clean folder for this lesson inside your course folder:
mkdir -p ~/mis-workbench/02-database-schema-designer && cd ~/mis-workbench/02-database-schema-designerOn Windows PowerShell, run mkdir -Force ~/mis-workbench/02-database-schema-designer; cd ~/mis-workbench/02-database-schema-designer, or open the folder in your desktop agent app. Then start your agent the way your track’s setup lesson showed.
Switch to plan mode
Once your agent is open, switch to Plan mode inside the session (Shift+Tab, /plan, or your desktop app’s mode picker). In plan mode it asks clarifying questions and shows you a plan before building.
The conversation
Each message below is an example. Say it in your own words.
Say what you want
Start with a short plain-English description of what you want to build:
I want to build a web app that turns plain-English database descriptions into an editable ER diagram, CREATE and INSERT SQL for MySQL and PostgreSQL, and a normalization checker. Ask me what you need first.Answer its questions
Your agent will ask a few questions to pin down the details. Answer each briefly:
| If it asks | You might say |
|---|---|
| What tech stack or framework should we use? | Use whatever standard web tools are simplest to run locally, in a clean dark slate theme. |
| Should it include built-in sample schemas to test with? | Yes, include three preset buttons: University enrollment, E-commerce orders, and Hospital appointments. |
| How should the ER diagram be rendered and interactive? | Show an interactive diagram with crow’s foot lines, zoom and pan, and diagram download. |
| What should the SQL output tabs include? | Tabs for CREATE TABLE, realistic INSERT rows, and DROP TABLE in reverse dependency order. |
| What should the normalization checklist look for? | Check 1NF atomic values, 2NF composite-key partial dependencies, and 3NF transitive dependencies. |
Read the plan
Before approving, check the plan for four domain essentials (your agent will typically propose a React + Vite app with SVG rendering for the live views):
- Many-to-many relationships create a junction table. A sentence like “Students enroll in Courses” automatically creates an Enrollments table with a composite primary key of both foreign keys.
- Editing an entity updates everything live. Adding, renaming, or deleting an attribute or entity in the editor immediately updates the SVG diagram, the SQL tabs, and the normalization checks.
- Both SQL dialects generate valid syntax. MySQL uses
AUTO_INCREMENT, backticks, andENGINE=InnoDB; PostgreSQL usesSERIALand double quotes; andINSERTrows respect foreign key dependency order. - Normalization checks look for structural red flags. Non-key attributes on a foreign key’s referenced entity (like
department_namealongsidedepartment_id) trigger a 3NF warning.
If the first plan misses something, such as junction tables or dependency order, say so briefly:
Make sure many-to-many relationships create a junction table with a composite primary key, and order INSERT statements so parent tables come before child tables.When the plan covers all four, approve it: pick the option to proceed or say Yes, go ahead and build it (in Codex, press Shift+Tab first to leave plan mode). Once you verify it runs cleanly in your browser, ask your agent to save a Git checkpoint:
Save a checkpoint with Git so we can roll back if needed.The schema designer has complex interactive state: editing entities updates the ER diagram, the SQL output, and the normalization checklist simultaneously. React’s component model and state management make this tractable. Vite gives you instant hot reload so you can tweak the layout and see changes immediately. The LLM sets all of this up — you do not need to know how Vite or TypeScript configuration works.
What you get
The LLM will generate a project structure like this:
REFERENCE PROJECT LAYOUT schema-designer/ generated React + Vite project folder tree open reference hide reference Reference example of the folder layout your agent creates. Open to inspect how the files are organized; you do not create these files by hand.
schema-designer/├── index.html├── package.json├── tsconfig.json├── vite.config.ts # includes the @tailwindcss/vite plugin├── 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.tsxAsk your agent to install the dependencies, start the local preview server, and give you the address to open:
Install what this app needs, start the local preview server, and tell me the browser address to open.If you prefer running it yourself in a terminal instead:
cd schema-designernpm installnpm run devOpen the local address (usually 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
| Problem | Follow-up prompt |
|---|---|
| ER diagram lines overlapping | The ER diagram relationship lines are crossing over entity boxes. Arrange the entities in a clean grid and route the connecting lines around the boxes so nothing overlaps. |
| MySQL/PostgreSQL toggle not changing SQL | Switching between MySQL and PostgreSQL does not update the SQL tab. Make sure changing the toggle immediately switches the output between MySQL and PostgreSQL syntax. |
| Junction table missing | Many-to-many relationships should automatically create a junction table. When a description says Students enroll in Courses, create an Enrollments table linking both keys. |
| Sample INSERT data has FK violations | The INSERT statements fail with foreign key violations. Put parent table inserts before child table inserts, and make sure every child row references an existing parent ID. |
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.
When Things Go Wrong
Use the Symptom → Evidence → Request pattern: describe what you see, paste the error, then ask for a fix.
Understanding the architecture
Here is how the pieces connect:
-
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. -
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.
-
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. -
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. -
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.
Libraries like Mermaid.js or GoJS could handle the diagramming, but they add complexity and constraints. A hand-rolled SVG approach gives you full control over styling, layout, and interactivity. The generated SVG also downloads cleanly as a standalone file you can drop into a report or presentation. For a tool this focused, direct SVG manipulation is simpler than learning a diagramming library’s API.
🔍SQL Dialects in the Real World: Beyond MySQL and PostgreSQLclick to expand
The dialect toggle in this tool covers the two most common open-source databases, but in enterprise MIS work you will encounter many more:
- Microsoft SQL Server (T-SQL): The dominant database in Windows-centric organizations. Uses
IDENTITY(1,1)instead of AUTO_INCREMENT, square bracket identifiers[TableName], andNVARCHARfor Unicode text. If your employer uses .NET and Azure, this is likely your primary database. - Oracle Database: Common in large enterprises (banking, government, healthcare). Uses
GENERATED AS IDENTITYcolumns for auto-increment (older schemas useSEQUENCEobjects and triggers), and has its own PL/SQL procedural language. - SQLite: A file-based database used in mobile apps and small tools. Uses
INTEGER PRIMARY KEYfor auto-increment and has limited ALTER TABLE support. - Cloud databases: Amazon RDS, Azure SQL, and Google Cloud SQL are managed versions of these engines. The SQL syntax is the same, but configuration and optimization are handled by the cloud provider.
Why this matters for MIS careers: Data analysts and business systems managers rarely choose the database engine — that decision is made by IT infrastructure teams. But you need to read and write SQL in whatever dialect your organization uses. Understanding the differences (and knowing that most everyday SQL is the same across dialects) is a practical skill.
A strong portfolio addition: extend this tool to support SQL Server syntax as a third dialect option. The prompt is simple: “Add a SQL Server dialect option. Use IDENTITY(1,1) for auto-increment, square bracket identifiers, and NVARCHAR(255) instead of VARCHAR(255).”
Customize it
Add reverse engineering from SQL
Add a Reverse Engineer tab that parses pasted CREATE TABLE statements for MySQL or PostgreSQL into entities, keys, and relationships in the editor and ER diagram.Add data dictionary export
Add a Data Dictionary tab listing each entity's attributes, types, constraints, auto-generated descriptions, and sample values, with a button to download an Excel-ready CSV.Add query builder
Add a visual Query Builder tab where I can click tables to join, pick columns and filters, and copy the live SELECT query.Add schema versioning
Save a timestamped snapshot in the browser whenever the schema changes, and add a sidebar to view, compare, or restore previous versions.The schema design process you just practiced — going from requirements (plain English) to a logical model (ER diagram) to a physical model (SQL DDL) — is exactly the workflow taught in database management courses and reinforced in capstone projects. In industry, this is how database architects work: gather requirements from stakeholders, model the data, generate the schema, iterate. This tool lets you practice that cycle in seconds instead of days.
Try it yourself
- Build the app by talking your agent through the plan above.
- Click Load Example and explore the University schema.
- 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.
- Edit the generated entities — add attributes, change data types, create new relationships.
- Toggle between MySQL and PostgreSQL and notice the syntax differences.
- Check the normalization panel. Intentionally add a redundant column and see if it gets flagged.
- 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. Most MySQL and PostgreSQL DDL is the same. Knowing the parts that differ (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 (both have free plans; Vercel’s Hobby plan is for personal, non-commercial use). 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.
You describe a database as: 'Students enroll in Courses. Each Course is taught by one Professor.' The schema designer creates Students, Courses, and Professors tables. What is missing?
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 by talking your AI agent through a plan in plain English.