Lesson 2: Database Schema Creation & Blueprint Development

Database Schema Design with AI Assistance

Design a comprehensive five-table database structure for the Pizza Management System using AI to generate SQL schema and seed files efficiently.

Database table structure:

# Five tables for pizza management system:

# 1. Customer Table
#    - customer_id (Primary Key)
#    - name
#    - phone
#    - email

# 2. Pizza Table
#    - pizza_id (Primary Key)
#    - name
#    - size
#    - price
#    - cost

# 3. Order Table
#    - order_id (Primary Key)
#    - customer_id (Foreign Key)
#    - date

# 4. Order_Detail Table
#    - order_detail_id (Primary Key)
#    - order_id (Foreign Key)
#    - pizza_id (Foreign Key)
#    - quantity

# 5. Employee Table
#    - user_id (Primary Key)
#    - username
#    - first_name
#    - last_name
#    - password

AI prompt for schema generation:

# Tell Claude:
"We need to update the schema and seed files in the database folder with this information"

# Then paste the table structure above
# Claude will automatically:
# 1. Update schema.sql with CREATE TABLE statements
# 2. Generate seed.sql with sample data
# 3. Create INSERT statements for all tables

Automated Table Creation Process

Use AI assistance to automatically create database tables and populate them with seed data using Python scripts.

Command AI to create tables:

# After schema files are generated, tell Claude:
"You have the database connection information. Go ahead and create the tables in the DB."

# Claude will attempt multiple approaches:
# 1. First attempt: Use MySQL client via bash
# 2. If MySQL client not installed: Create Python script
# 3. Generate create_tables.py automatically
# 4. Execute Python script to create all tables

Automatic error handling and self-correction:

# Claude will automatically:
# - Detect connection errors
# - Fix Unicode/encoding issues
# - Retry failed operations
# - Self-correct syntax errors (typically 3-4 iterations)
# - Verify successful table creation

# Common corrections Claude makes:
# - Unicode character encoding
# - SQL syntax adjustments
# - Connection string formatting
# - Foreign key constraint ordering

The AI will create a create_tables.py script, execute it, handle any errors, and verify all tables were created successfully.

Automated Data Seeding and Verification

AI generates comprehensive seed data and automatically verifies successful database population.

Seed data generation:

# Claude automatically generates:
# - Multiple customer records with realistic data
# - Various pizza types (small, medium, large)
# - Sample orders with order dates
# - Order details linking orders to pizzas with quantities
# - Employee records with hashed passwords

# Example seed data structure:
# INSERT INTO customer (name, phone, email) VALUES
# ('John Smith', '555-0101', 'john@email.com'),
# ('Jane Doe', '555-0102', 'jane@email.com'),
# ...

Automatic verification process:

# After seeding, Claude creates verify_data.py
# This script:
# 1. Connects to database
# 2. Counts records in each table
# 3. Verifies foreign key relationships
# 4. Confirms data integrity
# 5. Reports results

# Output example:
# "Connected successfully"
# "Customers: 10 records"
# "Pizzas: 15 records"
# "Orders: 8 records"
# "Order Details: 20 records"
# "Employees: 3 records"

Note: The generated Python scripts (create_tables.py, verify_data.py) will be deleted in Lesson 4, but schema.sql will be kept for reference.

Creating Customer and Pizza Blueprints

Use AI to generate Flask blueprints with modal-based CRUD operations based on the existing examples blueprint template.

Blueprint generation approach:

# Strategy: Generate blueprints one at a time
# Use existing examples.py blueprint as template

# AI prompt for pizza blueprint:
"Using the examples.py blueprint, make a new blueprint for pizzas.
Make sure to use the blueprint and template using the examples template.
Also make sure to use modals and dropdowns for the CRUD operations."

# Then for customers:
"Make the first blueprint and template for customers."

# Important: Be specific about which blueprint to create first
# Otherwise AI may start creating multiple blueprints simultaneously

Blueprint structure created:

# Claude automatically generates:

# File: blueprints/pizzas.py
# - Database connection setup
# - Route for listing all pizzas
# - Route for adding new pizza (modal)
# - Route for editing pizza (modal)
# - Route for deleting pizza
# - Dropdown handling for size selection

# File: templates/pizzas.html
# - Bootstrap table for pizza list
# - Add modal with form
# - Edit modal with form
# - Delete confirmation modal
# - JavaScript for modal interactions

# Then repeats for customers blueprint

Blueprint registration happens automatically in app.py with proper routing and navigation updates.

Creating Orders and Order Details Blueprint

Build a combined blueprint for orders and order details, demonstrating related table management in a single blueprint.

Combined blueprint approach:

# AI prompt for orders blueprint:
"Let's make the order and order_details blueprint.
These can be in one blueprint and template.
Make sure they get registered."

# Why combine these tables?
# - Orders and order details are tightly coupled
# - Order details are child records of orders
# - Better UX to manage them together
# - Reduces code duplication

Features of combined orders blueprint:

# blueprints/orders.py includes:

# Main orders view:
# - List all orders with customer names (JOIN)
# - Show order date and totals
# - View button for order details

# Order details view:
# - Display items in specific order
# - Show pizza names, quantities, prices
# - Calculate sales tax automatically
# - Add more pizzas to existing order
# - Update quantities
# - Delete order items

# Foreign key handling:
# - Customer dropdown in add order modal
# - Pizza dropdown in add item modal
# - Automatic relationship validation

The orders blueprint demonstrates managing parent-child table relationships within a single blueprint structure.

Removing Template Files and Updating Navigation

Clean up the project by removing example blueprint files and updating the application structure to reflect the new blueprints.

Removal command for AI:

# AI prompt to remove examples:
"Go ahead and remove the examples py and html files
and also from the registry and the navigation in the base file."

# Claude will request permission for deletions
# When deleting files, AI always asks for confirmation

Files removed automatically:

# Claude deletes using remove command:

# 1. blueprints/examples.py
#    - Example blueprint code

# 2. templates/examples.html
#    - Example template file

# 3. Updates app.py registry:
#    - Removes: from blueprints.examples import examples_bp
#    - Removes: app.register_blueprint(examples_bp)

# 4. Updates templates/base.html navigation:
#    - Removes examples nav link
#    - Keeps: Customers, Orders, Pizza links
#    - Navigation ready for production use

Final navigation structure:

• Customers - Manage customer records
• Orders - View and manage orders with details
• Pizza - Manage pizza menu items

The application now has a clean structure with only production blueprints and navigation ready for testing.

Blueprint Generation Summary

Review the complete blueprint structure created through AI assistance in Lesson 2.

Final project structure:

M5-demo01/
├── app.py (updated with new blueprints)
├── blueprints/
│   ├── customers.py (new)
│   ├── pizzas.py (new)
│   ├── orders.py (new - includes order_details)
│   └── examples.py (removed)
├── templates/
│   ├── base.html (updated navigation)
│   ├── customers.html (new)
│   ├── pizzas.html (new)
│   ├── orders.html (new)
│   └── examples.html (removed)
├── database/
│   ├── schema.sql (generated, kept for reference)
│   └── seed.sql (generated, kept for reference)
├── create_tables.py (temporary, delete in Lesson 4)
└── verify_data.py (temporary, delete in Lesson 4)

Blueprint features implemented:

• Modal-based add/edit/delete operations
• Dropdown selections for foreign key relationships
• Bootstrap styling for professional appearance
• Responsive design for mobile compatibility
• Database connection handling with error management
• JOIN queries for related data display

Next Steps: Lesson 2 focused on database and blueprint creation. Testing of the blueprints will occur in subsequent lessons, along with adding authentication and dashboard features.