Relational Database Design Principles on AWS

Relational database design is the art of organising data into structured tables that relate to each other. A database is simply a place where you store data electronically. A relational database is a special type where data is stored in tables (like spreadsheets) and relationships are defined between those tables. This structure prevents data duplication and keeps everything consistent.

Core concepts first: A table is a collection of rows and columns, similar to a spreadsheet. Each row is a single record (for example, one customer). Each column is a single attribute of that record (for example, customer name or email address). A primary key is a special column (or combination of columns) that uniquely identifies each row — think of it like a passport number for each record. A foreign key is a column in one table that points to the primary key in another table, creating a link between them.

The process of designing these tables is called normalisation. Normalisation is the practice of breaking down large, messy tables into smaller, well-connected tables to eliminate data redundancy (duplicating the same information in multiple places) and avoid anomalies (problems when inserting, updating, or deleting data). For example, instead of storing the customer's full address in every order row, you create a separate customers table with the address once, and link each order to that customer ID.

On AWS, the primary relational database services are:

Why does this matter for the exam? The DBS-C01 tests your ability to choose the right service for a given workload. A key decision point is whether the application needs fast, transactional read-write operations (Choose RDS or Aurora) or heavy analytical processing on historical data (Choose Redshift). Aurora is specifically tested as the premium option for high-availability, high-throughput applications that demand minimal downtime and automatic failover.

Another critical design principle is indexing. An index is like the index at the back of a textbook — it tells the database exactly where to find the data without scanning every row. Without indexes, even a simple query can become extremely slow on large tables. However, indexes also slow down write operations (insert, update, delete) because the index must be updated each time, so you must balance them.

Finally, you must understand ACID (Atomicity, Consistency, Isolation, Durability) — the set of properties that guarantee reliable transaction processing. Atomicity means a transaction either completely succeeds or completely fails. Consistency means the database state always follows its rules (constraints). Isolation means concurrent transactions do not interfere with each other. Durability means once a transaction is committed, the data is permanently saved even if the server crashes. All relational databases on AWS support ACID, but Redshift sacrifices some transactional capabilities for analytical performance.

An IT professional uses these principles every day. Consider a typical e-commerce platform built on AWS. The company sells products online and needs to store customer accounts, product inventory, and order history.

Step one: The database architect analyses the data. They identify four main entities: Customers, Products, Orders, and Order_Items. Each entity becomes a table. The Customers table has columns: customer_id (primary key), name, email, address. The Products table has columns: product_id (primary key), name, price, stock_quantity. The Orders table has columns: order_id (primary key), customer_id (foreign key to Customers), order_date, total_amount. The Order_Items table links orders to products and has: order_id (foreign key to Orders), product_id (foreign key to Products), quantity, unit_price. This design avoids storing the customer address in every order row — that would be redundant.

Step two: They choose the database service. Because this is a live e-commerce application with frequent read and write operations (transactions), the architect selects Amazon Aurora. The application needs high availability — if one Availability Zone goes down, the database must keep working. Aurora provides automatic failover to a replica in another zone within minutes. The architect also enables Multi-AZ deployment for RDS (if they had chosen RDS) which creates a standby replica in another zone.

Step three: They create indexes. The architect adds an index on the Orders.order_date column because the marketing team frequently queries "total orders per month for the last year". Without the index, the database would scan every row in a table that might have millions of rows. They also create an index on Order_Items.product_id because the product catalogue page needs to show "how many units sold" quickly.

Step four: They set up monitoring. Using Amazon CloudWatch (AWS's monitoring service), they track metrics like CPU utilisation, database connections, and read/write latency. If the database becomes slow, they can scale vertically by moving to a larger instance type, or horizontally by adding read replicas (separate copies of the database that handle read-only queries).

Step five: They plan for analytics. The business also needs a weekly sales report by region. Instead of running heavy aggregation queries on the live Aurora database (which would slow down customer orders), they export data once a day to Amazon Redshift. In Redshift, they run complex queries that join millions of rows from multiple tables, using its columnar storage to get results in seconds instead of hours.

This scenario illustrates the practical decisions: choosing Aurora for transactional workloads, designing normalised tables with foreign keys, adding indexes strategically, and separating analytics into Redshift to keep the main application fast.