Relational Data Modeling

In the previous lessons, you explored how Firestore — a flexible NoSQL document database — lets you store and query data with nested objects and arrays. This approach is great for scalability and evolving schemas, but it can become challenging when your application needs to manage complex relationships between entities.

Imagine building an e-commerce platform with customers, products, orders, and order items. A customer can place multiple orders, each order can contain multiple products, and each product can appear in many orders. In Firestore, you might duplicate customer information in every order document. If a customer updates their email, you’d need to find and update every order — missing one could lead to inconsistent data. You could also split data into separate collections, but then you’d need to manually join data in your application code.

This is where Google Cloud SQL comes in. Cloud SQL is a fully managed relational database service for MySQL, PostgreSQL, and SQL Server on Google Cloud. Unlike NoSQL databases, Cloud SQL uses a relational model: data is organized into tables with strictly defined columns, and relationships between tables are enforced through foreign keys. Each customer, product, and order is stored only once, and relationships are maintained by referencing IDs. When a customer’s email changes, you update a single row, and all related data remains consistent.

The relational model also provides ACID transactions, which guarantee that complex operations involving multiple tables either complete entirely or not at all. For example, when a customer places an order, you can create the order, add order items, and update inventory in a single transaction — ensuring data consistency.

In this lesson, you’ll learn how Cloud SQL’s relational model uses tables, foreign keys, and JOINs to represent complex data relationships. You’ll see when structured relationships and guaranteed consistency are more important than the flexibility and scalability of NoSQL databases.

The relational model organizes data into tables, where each table represents a specific type of entity. Unlike Firestore documents, every row in a relational table has the same columns, and the structure is enforced by a schema.

For our e-commerce example, you would create four tables: customers, products, orders, and order_items. Here are their definitions using MySQL syntax (supported by Cloud SQL):

def get_schema_definitions():
    """Get SQL table definitions for Cloud SQL (MySQL)"""
    schemas = {
        'customers': '''
            CREATE TABLE customers (
                customer_id INT PRIMARY KEY AUTO_INCREMENT,
                email VARCHAR(255) UNIQUE NOT NULL,
                first_name VARCHAR(100) NOT NULL,
                last_name VARCHAR(100) NOT NULL,
                phone VARCHAR(20),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )''',

        'products': '''
            CREATE TABLE products (
                product_id INT PRIMARY KEY AUTO_INCREMENT,
                name VARCHAR(255) NOT NULL,
                price DECIMAL(10,2) NOT NULL,
                stock INT DEFAULT 0,
                category VARCHAR(100)
            )''',

        'orders': '''
            CREATE TABLE orders (
                order_id INT PRIMARY KEY AUTO_INCREMENT,
                customer_id INT NOT NULL,
                order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                total DECIMAL(10,2) NOT NULL,
                status VARCHAR(50) DEFAULT 'pending',
                FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
            )''',

        'order_items': '''
            CREATE TABLE order_items (
                item_id INT PRIMARY KEY AUTO_INCREMENT,
                order_id INT NOT NULL,
                product_id INT NOT NULL,
                quantity INT NOT NULL,
                unit_price DECIMAL(10,2) NOT NULL,
                FOREIGN KEY (order_id) REFERENCES orders(order_id),
                FOREIGN KEY (product_id) REFERENCES products(product_id)
            )'''
    }

    return schemas

Let’s break down the SQL syntax:

• customer_id INT PRIMARY KEY AUTO_INCREMENT: This column holds integer values, uniquely identifies each row, and automatically generates a new sequential number for each new customer.
• email VARCHAR(255) UNIQUE NOT NULL: This column holds variable-length text up to 255 characters, must be unique, and cannot be null.
• price DECIMAL(10,2) NOT NULL: This column holds decimal numbers with up to 10 digits and 2 decimal places.
• created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP: Automatically sets the current date and time when a new row is inserted.

The foreign key constraint is key to enforcing relationships. For example, in the orders table, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ensures that every order references a valid customer. You cannot create an order for a non-existent customer — Cloud SQL will reject it. Similarly, the order_items table uses foreign keys to link to both orders and products, creating a many-to-many relationship.

This approach, called normalization, avoids duplication. Customer emails are stored only in the customers table, and product names only in products. To retrieve complete information, you combine data from multiple tables using JOINs.

A JOIN is a SQL operation that combines rows from two or more tables based on related columns. In Cloud SQL, you use JOIN queries to efficiently retrieve and analyze data across tables.

The most common type is an INNER JOIN, which returns only rows with matches in both tables. A LEFT JOIN returns all rows from the left table and matching rows from the right table, using nulls where there's no match. This is useful for including entities even if they don't have related data. RIGHT JOIN works like LEFT JOIN but keeps all rows from the right table instead, and FULL OUTER JOIN keeps all rows from both tables. Note that MySQL (one of the Cloud SQL database engines) does not support FULL OUTER JOIN, though PostgreSQL does. In practice, most analytical queries can be accomplished with INNER and LEFT JOINs, which we'll focus on here.

Here are some example queries for our e-commerce schema:

def get_analytical_queries():
    """Get complex analytical SQL queries for Cloud SQL"""
    queries = {
        'customer_summary': '''
            SELECT 
                c.first_name,
                c.last_name,
                c.email,
                COUNT(o.order_id) as total_orders,
                COALESCE(SUM(o.total), 0) as lifetime_value,
                COALESCE(AVG(o.total), 0) as avg_order_value
            FROM customers c
            LEFT JOIN orders o ON c.customer_id = o.customer_id
            GROUP BY c.customer_id, c.first_name, c.last_name, c.email
            ORDER BY lifetime_value DESC''',

        'order_details': '''
            SELECT 
                c.first_name,
                c.last_name,
                o.order_id,
                o.order_date,
                p.name as product_name,
                oi.quantity,
                oi.unit_price,
                (oi.quantity * oi.unit_price) as line_total
            FROM customers c
            JOIN orders o ON c.customer_id = o.customer_id
            JOIN order_items oi ON o.order_id = oi.order_id
            JOIN products p ON oi.product_id = p.product_id
            WHERE c.customer_id = 1''',

        'product_performance': '''
            SELECT 
                p.name,
                p.category,
                COALESCE(SUM(oi.quantity), 0) as units_sold,
                COALESCE(SUM(oi.quantity * oi.unit_price), 0) as revenue
            FROM products p
            LEFT JOIN order_items oi ON p.product_id = oi.product_id
            GROUP BY p.product_id, p.name, p.category
            ORDER BY revenue DESC''',

        'monthly_sales': '''
            SELECT 
                DATE_FORMAT(order_date, '%Y-%m') as month,
                COUNT(*) as order_count,
                SUM(total) as revenue,
                AVG(total) as avg_order_value
            FROM orders
            WHERE status IN ('delivered', 'shipped')
            GROUP BY DATE_FORMAT(order_date, '%Y-%m')
            ORDER BY month DESC'''
    }

    return queries

• The customer_summary query uses LEFT JOIN to include all customers, even those with no orders. COALESCE ensures that customers with no orders show zero for lifetime value and average order value.
• The order_details query chains multiple JOINs to provide a detailed breakdown of a customer's orders, including product names, quantities, and prices. The WHERE c.customer_id = 1 clause comes after all the JOINs because SQL first combines the tables, then filters the result. This is the standard SQL syntax: the WHERE clause filters the combined data to show only orders for customer ID 1.
• The product_performance query summarizes sales and revenue for each product.
• The monthly_sales query aggregates order data by month.

Cloud SQL automatically creates indexes for primary keys and foreign keys, allowing these JOINs to be performed efficiently even on large datasets. Indexes are data structures that speed up lookups and joins, making analytical queries fast and scalable.

ACID transactions in Cloud SQL ensure that operations maintain data integrity, even when multiple changes must happen together or when multiple users access the database at the same time. ACID stands for:

• Atomicity: All operations in a transaction complete together or none do. If any step fails, the entire transaction is rolled back.
• Consistency: Transactions maintain all constraints, such as foreign keys and unique constraints.
• Isolation: Concurrent transactions do not interfere with each other.
• Durability: Once committed, changes are permanent, even in the event of a crash.

Let's look at a concrete problem: placing an order while ensuring sufficient inventory. Without proper transaction handling, you could have a race condition:

1. Transaction A reads: "Product has 10 units in stock"
2. Transaction B reads: "Product has 10 units in stock"
3. Transaction A places an order for 8 units
4. Transaction B places an order for 8 units
5. Both transactions succeed, but you've oversold by 6 units!

Both transactions saw 10 units available, but by executing concurrently, they created invalid data. This is where SELECT ... FOR UPDATE comes in. This SQL statement locks the selected rows, preventing other transactions from reading or modifying them until the current transaction completes. It guarantees that only one transaction at a time can check and update inventory for a product.

Here's how you would use a transaction in Cloud SQL (MySQL) to place an order while ensuring inventory is available:

def get_transaction_example():
    """Get example of ACID transaction in Cloud SQL (MySQL)"""
    transaction = {
        'description': 'Place order with inventory check',
        'steps': [
            'START TRANSACTION',
            'SELECT stock FROM products WHERE product_id = %s FOR UPDATE',
            'INSERT INTO orders (customer_id, total) VALUES (%s, %s)',
            'INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (LAST_INSERT_ID(), %s, %s, %s)',
            'UPDATE products SET stock = stock - %s WHERE product_id = %s',
            'COMMIT'
        ],
        'rollback_on': 'Insufficient stock or any error'
    }

    return transaction

The %s placeholders in these queries are parameterized query placeholders used by MySQL's Python connector (mysql-connector-python). Instead of building SQL strings by concatenating values (like f"SELECT * FROM products WHERE product_id = {user_input}"), you use %s placeholders and pass values separately as a tuple. This prevents SQL injection attacks, where malicious users could insert SQL commands into your queries. For example, if a user provided "1; DROP TABLE products;" as input, string concatenation would execute the DROP command, deleting your entire products table. With parameterized queries, the database treats the entire input as a safe data value, not executable code. Note that %s is specific to MySQL's Python connector — PostgreSQL's psycopg2 uses the same syntax, but other databases like SQLite use ? as the placeholder. All SQL client libraries support parameterized queries — you pass values as a tuple or list, and the library safely escapes and inserts them.

Now let's walk through the transaction steps:

• The transaction starts with START TRANSACTION.
• SELECT stock FROM products WHERE product_id = ? FOR UPDATE reads the current stock and locks that product row. Any other transaction trying to read this product with FOR UPDATE must wait until this transaction commits or rolls back. This prevents the race condition described above.
• After checking stock in your application code (if stock is sufficient), you insert the order and order items and update the product's stock.
• COMMIT makes all changes permanent and releases the lock. If any step fails, you execute ROLLBACK to undo all changes and release the lock.

This guarantees that orders and inventory updates happen together atomically — you cannot create an order without reserving inventory, and you cannot oversell products.

Choose Cloud SQL when:

• Your data has structured relationships that must be enforced (customers → orders → products)
• Updates must be consistent across multiple entities (order creation + inventory decrease)
• You need complex analytical queries spanning multiple entities
• Data accuracy is critical (financial transactions, inventory management)

Choose Firestore when:

• Your schema needs to evolve frequently without downtime
• Your access patterns are primarily key-based lookups (get user by ID)
• You need to scale to very high request rates with simple queries
• Eventual consistency is acceptable

Here’s a comparison for our e-commerce example:

def compare_nosql_vs_sql():
    """Compare Firestore (NoSQL) and Cloud SQL (SQL) approaches for e-commerce"""
    nosql_approach = {
        'structure': 'Orders duplicate customer data',
        'updates': 'Must update all orders if customer changes email',
        'queries': 'Fast by order_id, slow for analytics',
        'consistency': 'Eventual consistency'
    }

    sql_approach = {
        'structure': 'Customers stored once, referenced by ID',
        'updates': 'Update customer once, affects all orders',
        'queries': 'Complex JOINs for comprehensive reports',
        'consistency': 'ACID guaranteed'
    }

    return nosql_approach, sql_approach

In the Firestore approach, each order document might duplicate customer information for fast lookups, but this makes updates and analytics more complex. In the Cloud SQL approach, customer information is stored once and referenced by ID, making updates and complex queries easier and more reliable.

The choice depends on whether you value consistency and complex queries more than flexibility and raw performance for simple operations. Many applications use both: transactional data in Cloud SQL for consistency, and Firestore for flexible, scalable access patterns.

You now understand two database models available on Google Cloud:

• Firestore: A NoSQL document database for flexible, hierarchical data with evolving schemas and rapid development.
• Cloud SQL: A fully managed relational database for structured data, complex relationships, and guaranteed consistency.

These are not competing alternatives, but complementary tools for different scenarios. Use Firestore when your schema needs flexibility, your access patterns are primarily key-based lookups, and eventual consistency is acceptable. Use Cloud SQL when relationships between entities are complex, data consistency is critical, and you need sophisticated analytical queries with JOINs.

The key differences come down to:

• Schema flexibility vs enforcement: Firestore lets your schema evolve without migrations; Cloud SQL enforces structure and relationships.
• Duplication vs normalization: Firestore often duplicates data for fast access; Cloud SQL normalizes data and uses JOINs.
• Eventual vs immediate consistency: Firestore offers eventual consistency; Cloud SQL provides ACID transactions.

To get started with Cloud SQL, you create an instance, choose your database engine (MySQL, PostgreSQL, or SQL Server), and connect using standard SQL clients or libraries. You can then define schemas with foreign keys, insert data, and run queries as shown in this lesson.

In the upcoming practice exercises, you'll define relational schemas with foreign keys, insert sample data, write JOIN queries, and work with ACID transactions. Pay attention to how foreign keys prevent invalid data, how JOINs simplify complex queries, and how transactions guarantee consistency.