7 Security and optimization

 

In this chapter

  • You evaluate and strengthen the security of your database.
  • You 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 complete) 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.

You can also find the SQL scripts corresponding to the finalized database design for different relational database management systems (RDBMSs), including MySQL, MariaDB, PostgreSQL, SQLite, SQL Server, and Oracle.

Overview

In this chapter, you will explore database security and optimization. You will see how to evaluate the integrity and confidentiality of your database design, enhance storage efficiency beyond normalization, understand when to use denormalization, and apply indexing to improve query performance.

Security

Integrity

Confidentiality

Storage considerations

Redundant tables

Categorical data

Indexing

Why and how to index