Lesson 2: Database Setup and Schema Design for Related Tables

JawsDB MySQL Provisioning

Set up a cloud MySQL database through Heroku's JawsDB add-on. Choose the free tier to avoid charges.

Provisioning steps:

# Navigate to Heroku Dashboard
# 1. Go to your app's Resources tab
# 2. Click Add-ons button
# 3. Search for JawsDB MySQL
# 4. Select JawsDB MySQL from results
# 5. Choose Kitefin Shared - Free plan
# 6. Click Submit Order Form

# After provisioning, access database details:
# 1. Click on JawsDB MySQL in your add-ons list
# 2. Copy the connection string provided
# 3. Note the format: mysql://username:password@hostname:port/database_name

Heroku automatically adds the connection URL to your environment variables. Copy the complete connection string for your Flask application configuration.

Environment Configuration Management

Store database credentials in a .env file for security. Extract individual components from the JawsDB connection string:

# Original JawsDB connection string format:
# mysql://username:password@hostname:port/database_name

# Extract and configure individual components:
DB_HOST=hostname
DB_USER=username
DB_PASSWORD=password
DB_NAME=database_name
DB_PORT=3306

# Alternative: Store complete connection string
DATABASE_URL=mysql://username:password@hostname:port/database_name

Use AI assistance to update the .env file safely. Add the .env file to .gitignore to prevent committing credentials.

Install MySQL connector for local development:

# Install MySQL connector for Python
pip install mysql-connector-python

# Alternative: Install PyMySQL
pip install PyMySQL

# Add to requirements.txt
echo mysql-connector-python >> requirements.txt

Two-Table Database Schema Design

Design a two-table schema with Books and Authors representing a one-to-many relationship (one author, multiple books).

Authors table structure:

CREATE TABLE authors (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    date_of_birth DATE,
    nationality VARCHAR(100),
    biography TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Books table with foreign key to Authors:

CREATE TABLE books (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    author_id INT NOT NULL,
    isbn VARCHAR(20) UNIQUE,
    publication_date DATE,
    genre VARCHAR(100),
    price DECIMAL(10,2),
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    -- Foreign key constraint with cascade operations
    FOREIGN KEY (author_id) REFERENCES authors(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

The foreign key constraint with CASCADE operations ensures referential integrity between tables.

Database Creation and Data Loading

Use Python scripts to create tables and load seed data programmatically:

import mysql.connector
import os
from dotenv import load_dotenv

def create_connection():
    load_dotenv()
    try:
        connection = mysql.connector.connect(
            host=os.getenv('DB_HOST'),
            user=os.getenv('DB_USER'),
            password=os.getenv('DB_PASSWORD'),
            database=os.getenv('DB_NAME'),
            port=int(os.getenv('DB_PORT', 3306))
        )
        return connection
    except mysql.connector.Error as err:
        print(f'Error: {err}')
        return None

def setup_database():
    connection = create_connection()
    if connection:
        cursor = connection.cursor()
        # Execute CREATE TABLE statements
        # Insert sample data
        connection.commit()
        print('Database setup completed successfully!')
        cursor.close()
        connection.close()

Load sample data for testing:

-- Sample Authors
INSERT INTO authors (first_name, last_name, date_of_birth, nationality, biography) VALUES
('Agatha', 'Christie', '1890-09-15', 'British', 'Famous mystery writer'),
('George', 'Orwell', '1903-06-25', 'British', 'Author of dystopian fiction');

-- Sample Books
INSERT INTO books (title, author_id, isbn, publication_date, genre, price) VALUES
('Murder on the Orient Express', 1, '978-0062693662', '1934-01-01', 'Mystery', 12.99),
('1984', 2, '978-0451524935', '1949-06-08', 'Dystopian Fiction', 13.99);

Connection Testing and Verification

Verify database setup with connection testing and data integrity checks:

def test_connection():
    try:
        connection = mysql.connector.connect(
            host=os.getenv('DB_HOST'),
            user=os.getenv('DB_USER'),
            password=os.getenv('DB_PASSWORD'),
            database=os.getenv('DB_NAME')
        )
        if connection.is_connected():
            print('Successfully connected to MySQL Server')
            cursor = connection.cursor()
            cursor.execute('SELECT DATABASE();')
            database_name = cursor.fetchone()
            print(f'Connected to database: {database_name[0]}')
            return True
    except mysql.connector.Error as e:
        print(f'Error: {e}')
        return False

Test with MySQL Workbench using the JawsDB connection parameters, or run verification queries:

SHOW TABLES;
SELECT COUNT(*) FROM authors;
SELECT COUNT(*) FROM books;
SELECT a.first_name, a.last_name, b.title
FROM authors a JOIN books b ON a.id = b.author_id;