Domain 4.2 of the Google Professional Cloud Database Engineer exam focuses on applying schema design best practices for Cloud SQL, which includes mastering data types, constraints, and partitioning. Without a well-planned schema, your database becomes a chaotic jumble of inconsistent data that slows down every query. This chapter will give you the mental model to design clean, efficient relational tables that scale gracefully on Google Cloud.
Jump to a section
A simple way to picture Schema Design for Cloud SQL: Relational Best Practices
A restaurant kitchen's recipe card collection is the central object of this analogy. The head chef organises every dish by two key attributes: main ingredient (chicken, beef, vegetable) and cooking method (grill, fry, steam). Each card has a strict format: the ingredient list must use standardised units (grams, not handfuls), every instruction must be one clear step, and no card can list a spice that doesn't exist in the pantry. This is the schema – the rigid structure that makes the kitchen run without chaos. When a new vegetarian starter is added, the chef doesn't rewrite every card; she simply files the new card in the 'vegetable-steam' section. The constraints prevent errors: the 'oven temperature' field only accepts numbers between 100 and 250, and a card cannot have a 'grill' method if the dish has no oil. Partitioning works like organising cards by month – all January specials go in one drawer, February in another. When the restaurant gets busy, the chef can pull out only the January drawer to plan seasonal menus, without shuffling through the entire collection. This maps directly to Cloud SQL: the card format is the table schema, the constraints enforce data quality, and partitioning splits data into manageable chunks for faster queries.
Schema design is the blueprint for how you organise data inside a relational database. Think of a database table as a spreadsheet with strict rules: every column has a specific data type (like 'integer' for whole numbers, 'varchar' for text, 'date' for calendar days), and every row must follow those rules. Cloud SQL is Google's managed service for relational databases; it supports MySQL, PostgreSQL, and SQL Server. The schema is the set of definitions you write before you put any data in.
Let's define the key terms. A relational database stores data in tables that relate to each other through keys. A table is a collection of rows (each row is one record, like one customer) and columns (each column is one attribute, like customer name). A schema is the complete description of all tables, their columns, data types, and relationships.
Data types are the most fundamental building block. They tell the database exactly what kind of value can go into a column. Common types include:
- INTEGER for whole numbers (e.g., age, quantity in stock) - VARCHAR(n) for variable-length text up to n characters (e.g., a name up to 255 letters) - BOOLEAN for true/false values (e.g., 'is_active' flag) - DATE for calendar dates (e.g., order_date) - TIMESTAMP for date and time combined (e.g., when a row was last updated) - DECIMAL(p,s) for precise numbers with decimal places (e.g., price stored as DECIMAL(10,2) for ten digits total, two after the decimal) Choosing the right data type matters because it affects storage size, query speed, and accuracy. Using VARCHAR(255) when you only need 10 characters wastes space; using FLOAT for money causes rounding errors.
Constraints are rules you add to columns to keep data clean. The most common ones are:
- NOT NULL: the column must have a value – it cannot be left blank - UNIQUE: every value in this column must be different from all others (like email addresses) - PRIMARY KEY: a column (or combination of columns) that uniquely identifies each row. It automatically implies NOT NULL and UNIQUE. Every table should have one. - FOREIGN KEY: a column that links to the primary key of another table, creating a relationship between tables - CHECK: a custom condition that each row must satisfy, e.g., CHECK (age >= 0) Constraints prevent bad data from entering the system. Without them, you could accidentally insert two customers with the same email, or store a negative price.
Partitioning is a technique for splitting a large table into smaller, more manageable pieces called partitions, usually based on a column value like a date range or region. Cloud SQL supports several partitioning strategies:
- Range partitioning: divides data by ranges of values, e.g., orders from January 2025 in one partition, February in another - List partitioning: divides data by a list of discrete values, e.g., customers from 'US', 'UK', 'Canada' in separate partitions - Hash partitioning: distributes data evenly across a fixed number of partitions using a hash function on a column. Good for load balancing. Partitioning speeds up queries because the database can skip entire partitions that don't match the query. For example, if you query 'all orders from January 2025', it only scans that one partition instead of the entire table.
Why does all this matter for the PCDE exam? Google expects you to know how to design schemas that are normalised (split data into related tables to avoid duplication), use appropriate data types, apply constraints correctly, and partition tables to handle large-scale workloads on Cloud SQL. You'll be tested on scenarios: given a business requirement, which data type and constraint combination is best? When should you use partitioning, and which type? How do foreign keys enforce relationships between tables?
Identify Entities and Relationships
List the real-world objects your database needs to store (e.g., customers, products, orders). Determine how they relate: one-to-many (one customer has many orders), many-to-many (orders can have many products, products can be in many orders). This step produces the high-level logical model.
Define Tables and Primary Keys
Create one table per entity. Assign a primary key to each table – usually an auto-incrementing integer column called 'id'. The primary key must uniquely identify every row and never be NULL. For many-to-many relationships, create a junction table with two foreign keys forming a composite primary key.
Choose Appropriate Data Types for Each Column
For every column, pick the smallest and most precise data type that fits the data. For example, use INT for customer IDs, DECIMAL(10,2) for prices, DATE for birth dates, VARCHAR(255) for short text. Avoid using TEXT for short strings or DOUBLE for money. This step directly affects storage cost and query speed.
Apply Constraints to Enforce Data Integrity
Add NOT NULL to required fields, UNIQUE to columns like email, CHECK for business rules (e.g., price > 0), and FOREIGN KEY on columns that link to other tables. These constraints prevent invalid data from entering the database and ensure relationships stay consistent.
Plan Partitioning Strategy for Large Tables
If a table is expected to grow beyond millions of rows, choose a partition key based on the most common query filter (e.g., order_date). Use RANGE partitioning for date ranges, LIST partitioning for discrete values like region codes, or HASH for even distribution when no natural range exists. Implement partitioning in Cloud SQL using the engine-specific syntax (MySQL's PARTITION BY RANGE, PostgreSQL's table inheritance or declarative partitioning).
Create Indexes for Query Performance
After defining the schema, create indexes on columns that appear in WHERE clauses, JOIN conditions, or ORDER BY statements. Avoid over-indexing; each index slows down writes. Use composite indexes when queries filter by multiple columns together. Monitor performance with Cloud SQL's query insights and add or remove indexes as needed.
An IT professional designing a schema for Cloud SQL typically works with a business team to understand what data needs to be stored and how it will be queried. Let's walk through a concrete scenario: building a database for an online retail store called 'ShopSimple'.
First, the database engineer meets with the product manager and learns that the core entities are Customers, Orders, Products, and Payments. Each customer can have many orders; each order can contain many products; each payment belongs to one order. The engineer starts by sketching a logical schema on a whiteboard. They identify that each entity gets its own table. They define a primary key for each table – typically an auto-incrementing integer called 'id' or a universally unique identifier (UUID) for distributed systems.
Next, the engineer chooses data types. For the 'email' column in the Customers table, they choose VARCHAR(320) because that's the maximum length of a valid email address. For 'price' in the Products table, they choose DECIMAL(10,2) to avoid floating-point rounding errors. For 'order_date' in the Orders table, they choose TIMESTAMP WITH TIME ZONE to correctly store the moment an order was placed, regardless of the customer's time zone.
Then, constraints are added. The customer email gets a UNIQUE constraint – no two customers can share an email. The order total gets a CHECK constraint to ensure it's greater than zero. The 'customer_id' column in the Orders table gets a FOREIGN KEY constraint referencing Customers.id, which prevents orphan orders that don't belong to any customer. The engineer also adds NOT NULL to critical fields like 'product_name' and 'order_status'.
Finally, the engineer plans partitioning. The company expects millions of orders per year, so they partition the Orders table by month using range partitioning on order_date. Queries for 'last month's sales' will scan only one partition instead of the whole table. They also create indexes on columns that are frequently searched, like 'customer_id' and 'order_status', to speed up lookups.
After deployment, the engineer monitors query performance using Cloud SQL's query insights and adjusts the schema as needed. They might add a new index if a query is too slow, or change the partition range from monthly to quarterly as data grows. They also back up the schema definition in version control (e.g., a git repository) so the team can track changes over time.
The PCDE exam tests your understanding of schema design for Cloud SQL in several specific ways. Expect multiple-choice questions and scenario-based items where you must choose the best design decision. Here are the exact concepts they love to test:
Choosing the correct data type for a given scenario. For example: 'A column stores a person's age. Which data type is most appropriate?' The correct answer is SMALLINT or TINYINT (depending on the engine), not VARCHAR or INTEGER. Traps: they might offer FLOAT for monetary values, or TEXT for short strings.
Primary key selection: they often ask whether to use a natural key (like a tax ID number) or a surrogate key (like an auto-increment integer). The correct answer is usually a surrogate key because natural keys change or are not always unique.
Foreign key constraints and referential integrity: they ask what happens when you try to delete a row that is referenced by another table. The correct behaviour is that the delete fails (unless you set ON DELETE CASCADE).
Partitioning strategy: they give you a table with 100 million rows and a query pattern (e.g., 'queries always filter by a date range between 2024-01-01 and 2024-12-31'). The correct partitioning type is RANGE on the date column. Traps: suggesting list partitioning on a continuous range, or hash partitioning for date-range queries.
Handling of NULL values: they test whether a column with a UNIQUE constraint can have multiple NULLs. In MySQL, yes; in PostgreSQL, yes (with some nuance). The PCDE exam expects you to know the standard SQL behaviour: NULLs are not considered equal, so multiple NULLs are allowed in a UNIQUE column.
Composite keys: a scenario where you need to store a many-to-many relationship (e.g., students and courses). The correct design is a junction table with two foreign keys forming a composite primary key.
Trap patterns to watch for:
They give you a table with no primary key defined and ask 'what's wrong?' The correct answer is that every table should have a primary key; otherwise, rows cannot be uniquely identified and performance suffers.
They suggest using VARCHAR for everything 'to keep it simple'. The trap: this wastes space, slows queries, and loses data type enforcement.
They propose putting all data in one table with many columns instead of normalising. The trap: this leads to data duplication and update anomalies.
Key definitions to memorise:
Normalisation: the process of organising data to reduce redundancy. Normal forms: 1NF (no repeating groups), 2NF (every non-key column depends on the whole primary key), 3NF (no transitive dependencies). The exam rarely asks for formal definitions but will test you on recognising violations.
Index: a data structure that speeds up lookups on a column. Not part of the schema per se, but closely related. They test when to use indexes – generally on columns used in WHERE clauses and JOIN conditions.
Surrogate key vs natural key: surrogate = artificial, auto-generated (e.g., id integer); natural = derived from real-world data (e.g., social security number). Surrogate is preferred for stability.
Referential integrity: the guarantee that foreign key values always match a valid primary key in the referenced table. Enforced by foreign key constraints.
A well-designed schema with correct data types, constraints, and indexes is the foundation of database performance and data integrity on Cloud SQL.
Every table must have a primary key that is NOT NULL and UNIQUE; use a surrogate integer key for stability.
Choose the smallest appropriate data type for each column to save storage and improve query speed.
Use foreign key constraints to enforce referential integrity between related tables and prevent orphan records.
Partition large tables by a column used in frequent queries (like a date range) to allow the database to skip irrelevant partitions.
Never use FLOAT or DOUBLE for monetary values; always use DECIMAL with specified precision to avoid rounding errors.
Index only columns that appear in WHERE clauses and JOIN conditions to avoid slowing down write operations.
Normalise your schema to reduce data duplication, but denormalise sparingly only after measuring performance issues.
These come up on the exam all the time. Here's how to tell them apart.
Primary Key
Only one per table
Automatically NOT NULL
Used for row identification and foreign key references
Unique Constraint
Multiple allowed per table
Allows NULL values (multiple NULLs)
Used only to enforce uniqueness, not for row identification
INT Data Type
Stores values up to 2,147,483,647
Uses 4 bytes of storage per value
Suitable for most tables with fewer than 2 billion rows
BIGINT Data Type
Stores values up to 9,223,372,036,854,775,807
Uses 8 bytes of storage per value
Suitable for tables that may exceed 2 billion rows
Range Partitioning
Splits data by contiguous ranges (e.g., date intervals)
Good for time-based queries like 'last month'
Partitions have different sizes if data distribution is uneven
Hash Partitioning
Splits data by applying a hash function to a column
Good for distributing load evenly when no natural range exists
Partitions are roughly equal in size, but range queries are inefficient
Natural Key
Derived from real-world data (e.g., email, tax ID)
May change over time (e.g., person changes email)
Often composite and can be long, slowing joins
Surrogate Key
Artificial, auto-generated (e.g., auto-increment integer or UUID)
Never changes for the lifetime of the row
Short and fixed size, speeding up index lookups and joins
VARCHAR(255)
Stored inline in the row
Maximum length defined (e.g., 255 characters)
Faster for sorting and comparisons on short strings
TEXT
Stored off-row (may require extra reads)
No practical length limit (up to 65,535 bytes in MySQL)
Slower for sorting; better for very long content like descriptions
Mistake
Adding an index always makes queries faster, so I should index every column.
Correct
Indexes speed up reads but slow down writes (INSERT, UPDATE, DELETE) because the index must be updated. You should index only columns used in WHERE clauses and JOINs, not every column.
People assume more is better; they don't realise the write overhead. Beginners hear 'index = fast' and apply it indiscriminately.
Mistake
Partitioning is the same as sharding.
Correct
Partitioning splits a table within a single database instance; sharding distributes data across multiple database instances. Cloud SQL supports partitioning but not built-in sharding (you'd use Spanner for that).
The terms sound similar and both split data. Beginners conflate them because they both solve 'table too big' problems, but they work at different layers.
Mistake
A primary key can be NULL as long as it's unique.
Correct
A primary key automatically implies NOT NULL and UNIQUE. It can never contain a NULL value.
People remember the UNIQUE part but forget the NOT NULL part. They think 'unique' is the only rule, because NULLs are allowed in a UNIQUE constraint on other columns.
Mistake
Using TEXT instead of VARCHAR saves space because it's variable-length like VARCHAR.
Correct
TEXT is variable-length but stored differently (often off-row) and can have performance penalties. VARCHAR is stored inline and is more efficient for short strings. They are not interchangeable for performance.
Both store text, and the term 'variable-length' makes them seem identical. Beginners don't understand storage engine internals.
Mistake
FOREIGN KEY and INDEX are the same thing.
Correct
A FOREIGN KEY enforces referential integrity; an INDEX speeds up lookups. They serve entirely different purposes, though many databases automatically create an index on foreign key columns.
They are often mentioned together, and both involve columns. Beginners confuse the 'constraint' role with the 'performance' tool.
Mistake
You should always use the largest data type 'just in case' — for example, BIGINT for all integer columns.
Correct
Using oversized types wastes storage, memory, and reduces cache efficiency. Choose the smallest type that fits your data range (e.g., INT for up to 2 billion, BIGINT for larger).
People fear future growth and don't realise the cost. They think 'more space = safer', but in databases, bigger is almost always worse for performance.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
A schema is the blueprint for your database tables – it defines table names, column names, data types, and constraints. In Cloud SQL, the schema is written using SQL CREATE TABLE statements.
Use INT if you expect fewer than 2 billion rows; use BIGINT if you might exceed that. For most applications, INT is sufficient. Over-allocating with BIGINT wastes space.
Both ensure uniqueness, but a PRIMARY KEY also implies NOT NULL and there can be only one per table. A UNIQUE constraint allows NULLs (multiple NULLs are allowed) and you can have many UNIQUE constraints on different columns.
Yes, but it may require a table rebuild and could fail if existing data cannot be converted. Use ALTER TABLE ... ALTER COLUMN ... TYPE (PostgreSQL) or ALTER TABLE ... MODIFY COLUMN (MySQL). Plan carefully to avoid downtime.
Cloud SQL for MySQL supports declarative partitioning starting from MySQL 5.7. Cloud SQL for PostgreSQL supports partitioning natively (table inheritance or declarative partitioning). Cloud SQL for SQL Server does not support partitioning in the same way; you would use partitioned views. Check the engine version on the GCP documentation.
In practice, yes – indexing foreign key columns prevents locking issues and speeds up joins. Some databases create this index automatically (MySQL InnoDB does not, PostgreSQL does not). You should explicitly create it if not present.
The database rejects the insert and returns an error message like 'FOREIGN KEY constraint failed'. The row will not be inserted, ensuring referential integrity.
You've finished Schema Design for Cloud SQL: Relational Best Practices. Continue through the PCDE study guide to build a complete picture of the exam.
Done with this chapter?