chapter seven

7 Security and optimization

 

In this chapter

  • You evaluate and strengthen the security of your database.
  • You explore how to further improve the storage efficiency of your database design beyond normalization.
  • You learn about indexing and how to apply it when implementing your database design to improve query performance.
  • You learn about denormalization and apply it to your database design to further improve query performance.
What you need to know

You can find the database design covered in this chapter (it’s finally completed) represented using tools commonly used by practitioners, such as dbdiagram.io, and MySQL Workbench in the GitHub repository (https://github.com/Neo-Hao/grokking-relational-database-design). You can navigate to the chapter_07 folder and follow the instructions in the README.md file to load the database design into corresponding tools.

Additionally, you can also find the SQL scripts corresponding to the finalized database design for different RDBMS, including MySQL, MariaDB, PostgreSQL, SQLite, SQL Server, and Oracle.

Overview

In this chapter, you will explore a series of topics relevant to database security and optimization, including how to evaluate the integrity and confidentiality of your database design, how to enhance the storage efficiency beyond normalization, what denormalization is and when to use it, as well as how to apply indexing to improve query performance.

Security

Integrity

Confidentiality

Storage Considerations

Redundant tables

Categorical data

Indexing

Why and how to index

Indexes for your ER diagram

Denormalization

Denormalize your database design

Summary