This chapter unifies security and performance into a practical playbook for taking a relational design from “works on paper” to “robust in production.” It frames security through integrity and confidentiality, then shows how to minimize storage and accelerate queries with indexing and, when justified, denormalization. Throughout, the running example demonstrates concrete schema adjustments and SQL changes, while stressing iterative review, measurement, and collaboration with application developers.
On the security front, integrity means enforcing sound schema choices and constraints—appropriate data types, primary keys, foreign keys, and business rules—to prevent issues like orphaned rows. Confidentiality is addressed via access control (comparing stricter MAC with flexible RBAC and showing role creation and grants) and encryption. The chapter distinguishes one-way hashing for secrets that never need to be read back (e.g., passwords hashed with a slow algorithm and stored in a resized column) from symmetric encryption for sensitive values that must be retrieved in clear text (e.g., card numbers and expiry dates encrypted with AES, encoded for storage). It also highlights the importance of secure key management and salting hashes to resist attacks.
For optimization, it first reduces storage beyond normalization by consolidating near-duplicate tables (merging billing and user addresses into a single address entity with a surrogate key plus a composite unique constraint) and by factoring repetitive categorical values into a reference table (states keyed by abbreviation). It then covers indexing strategy: when to add standard versus full-text indexes, how to align them with real query patterns (e.g., product name search, exact matches on review code and payment method email), and the value of empirical tuning in production. Finally, it introduces denormalization as a targeted, last-resort technique to cut expensive joins—illustrated by adding a direct artist–song link and, in the store example, reintroducing a user–purchase relationship—while documenting trade-offs, monitoring performance, and guarding against integrity drift.
Summary
You shouldn’t save sensitive information in plain text. Instead, you need to pick the right encryption method, encrypt the data and ensure the corresponding columns have enough room for the encrypted data in your database design.
Beyond normalization, there might be opportunities that you can further improve the storage efficiency of your database design. Such opportunities may be redundant table, columns, or categorical data. However, you need to be careful because you don’t want to negatively impact your database performance.
Indexing can contribute to increasing query performance of your database. You will need to work with the application developers closely and use log data to understand where to apply indexes. If you aim to support queries that only involve sorting and exact match searching, standard index is good enough. However, if you aim to support queries that involve keyword searching, partial word matching, or fuzzy searching, you will need the help from full-text search.
Denormalization is a process where you introduce redundancy purposefully because such a change can improve query efficiency. Denormalization is rare and requires you to be careful in handling data insertion, update, and deletion in the same table.
FAQ
What are the two key security lenses for database design and how do I apply them?Integrity and confidentiality. Integrity means preserving accuracy and consistency: choose appropriate data types, define primary keys, enforce foreign keys, and add constraints from requirements. Confidentiality means preventing unauthorized access: implement access control (e.g., RBAC roles with least privilege) and use encryption (hash passwords; use symmetric encryption for sensitive values that must be read back, like card numbers).Why are foreign keys essential, and what can go wrong without them?Without foreign keys, you can insert orphan rows that reference non-existent parents, creating integrity gaps and security risks. Always add explicit foreign key constraints to enforce relationships (e.g., book.author_id referencing author.author_id), which prevents orphaned data and simplifies maintenance.What’s the difference between MAC and RBAC, and when should I use RBAC?MAC (Mandatory Access Control) uses centrally enforced, rigid classifications (e.g., confidential/sensitive) and user clearances—no exceptions. RBAC (Role-Based Access Control) assigns permissions to roles, then users to roles—more flexible and maintainable. Most application databases adopt RBAC to align privileges (e.g., standard_user, analyst, admin) with tasks like SELECT/INSERT/UPDATE/DELETE.When should I hash data vs. use symmetric encryption?Hash (one-way) data that must never be recovered in plaintext—passwords are the classic case (e.g., Bcrypt/PBKDF2/SHA-512 with salt). Use symmetric encryption for sensitive data that must be read back (e.g., credit card number and expiry date). Adjust column types to fit encoded ciphertext and hashes (e.g., CHAR(60) for Bcrypt hashes; around CHAR(45) for Base64-encoded AES-256 outputs with IV).How should I manage symmetric encryption keys securely?Do not store the key in the same database. Keep it in application environment variables, a dedicated key management service (KMS), or a hardware security module (HSM). The security of symmetric encryption hinges on the key’s protection.How can I reduce storage beyond normalization?Identify and consolidate redundant tables and values. Example: replace nearly identical billing_address and user_address with a single address table that uses a surrogate key (address_id) plus a UNIQUE constraint on the address fields to prevent duplicates. Update referencing tables with address_id, making it optional where appropriate (NULL for optional relationships).How do I handle highly repetitive categorical data like U.S. states?Extract the repeating category into its own lookup table. Move address.state into a state table keyed by the two-letter abbreviation, and reference it from address. This reduces redundancy, enforces valid values, and can shrink storage and improve consistency.How do standard indexes work and which columns should I index?Standard indexes (often B-tree) maintain sorted structures to accelerate lookups, sorts, and joins. Index columns frequently used in WHERE, ORDER BY, and JOIN conditions. Consider composite indexes for common multi-column orderings or filters. Primary keys are auto-indexed; foreign keys usually are not—index them if used in joins or filters.When should I use full-text indexing instead of LIKE?Use full-text indexing for keyword, partial, or fuzzy searches over text (e.g., product names or movie titles). It tokenizes text and supports efficient MATCH/AGAINST (MySQL/MariaDB) or tsvector/GiN (PostgreSQL). It greatly outperforms wildcard LIKE scans as data grows.What is denormalization, when is it justified, and what are the trade-offs?Denormalization intentionally introduces redundancy to speed frequent, expensive queries (e.g., adding artist_id to song, or re-adding a direct user–purchase link to avoid a three-table join). It can violate normal forms and create transitive dependencies, so document the rationale, monitor performance, and ensure update paths keep redundant data consistent. Reassess if write volume or integrity risks outweigh the performance gains.
pro $24.99 per month
access to all Manning books, MEAPs, liveVideos, liveProjects, and audiobooks!
Grokking Relational Database Design ebook for free
