10 Relational Databases

This chapter introduces how to modernize Banning Books’ ordering system with a cloud-hosted relational database. It explains why relational databases are a strong fit for structured, transactional workloads and highlights the business priorities that guide all choices: performance, cost, disaster recovery, and security. The narrative frames database work as equal parts good design and sound platform selection, emphasizing schema planning, thoughtful normalization and denormalization, and the advantages of running managed databases on Azure for global access and operational reliability.

The chapter walks through a pragmatic schema for customers, orders, order items, books, reviews, and categories, showing how keys and relationships capture business processes and enable scalable queries. It then compares Azure SQL Database, Azure Database for MySQL, and Azure Database for PostgreSQL, outlining their strengths, trade-offs, and cost considerations. While each service can meet core needs, Azure SQL Database is selected for its blend of performance features, built-in security, and disaster recovery options, with step-by-step Azure CLI examples for provisioning and configuring databases.

Operational excellence is addressed through performance tuning (automatic tuning for indexes and plans, query performance insights, and partitioning strategies), and elastic scale patterns (vertical scaling with tiers/DTUs or vCores, and horizontal scaling via read replicas). A robust resilience posture is built from automated backups, geo-replication, and failover groups for coordinated, low-downtime failovers. Security guidance covers Entra ID–based authentication and RBAC, encryption at rest with TDE, encryption in transit via TLS, and strict least-privilege access. The chapter closes with practical cost hygiene, reminding readers to clean up resources to avoid unnecessary spend.

The Customers and Orders tables are related using the Id in the Customers table.

Denormalizing the Posts table by adding the count of comments on each post.

Banning books ordering database schema showing the relationships between tables.

The Azure relational database selection represented as Banning Books.

Azure SQL automatic tuning.

Partitioning a table in a database can improve performance dramatically if done correctly.

Poster for Banned Books Week sales and Ultra Black Friday

Vertical scaling is increasing the size of CPU and memory for the same VM.

Horizontal scaling of a SQL instance means adding more instances as demand increases and remove them again as demand decreases.

Geo-replication of the database between two regions means the Banning Books website can withstand disaster.

A failover groups abstracts away the need to know which is the primary and secondary database server.

The principle of least privilege means that user roles can only access resources necessary to carry out their work.

• The database schema defines the structure of the data, the relationships between that data, and how we access the data.
• The schema provides a framework for storing and retrieving data in a structured and organized manner and is an essential step in any relational database.
• Normalization is the process of organizing data in a way that minimizes redundancy and ensures that each piece of data is stored in only one place.
• Denormalization is the process of intentionally introducing redundancy into a database schema to improve performance.
• There are pros and cons to both normalization and denormalization, such as data redundancy, performance, scalability, maintenance and flexibility. You need to consider these in each scenario where you have to decide on your approach.
• Azure Database for MySQL is a fully managed service that brings the extensive capabilities of the widely-used open-source MySQL database system to the Azure cloud platform.
• Azure PosgreSQL is a managed service on Azure, and is known for its robustness, advanced features, and strong standards compliance, making it a popular choice for complex, high-scale applications.
• Automatic tuning analyzes your database workload and provides recommendations for creating and dropping indexes.
• Automatic tuning has three options: force plan, create index and drop index. These automatic tuning options can be enabled or disabled individually, giving you the flexibility to choose the level of automatic tuning that's right for your database.
• Partitioning is a technique used to divide a large database table into smaller, more manageable pieces, while maintaining the abstraction of a single table to the user.
• Vertical scaling means increasing or decreasing the compute size and/or memory of your database server.
• Horizontal scaling, or scaling out, involves adding more replicas of your database to distribute the load at peak times.
• Geo-replication in Azure SQL Database is a feature that allows you to replicate your databases to a secondary server located in a different geographic region.
• Failover groups in Azure SQL Database are an abstraction built on top of geo-replication, designed to facilitate database failover in a more automated and coordinated manner across multiple databases.
• The principle of least privilege dictates that users and applications should be granted only the minimum levels of access—or permissions—necessary to perform their functions.

FAQ