Best Practices for Designing and Querying

In this chapter, you will learn about database best practices, including database design, indexing, and querying and modifying data. You learned about these topics throughout the previous chapters. This chapter will summarize and give additional tips regarding the best practices. This chapter will also provide a way for the more experienced among you to quickly reference best practices instead of having to go through each chapter.

In this chapter, we will cover the following topics:

• Best practices for database design
• Best practices for indexing
• Best practices for querying and modifying data

Technical requirements

You can refer to the code files of this chapter at https://github.com/PacktPublishing/learn-sql-database-programming/tree/master/chapter-15.

Best practices for database design

To implement the best design for your database, you will need to follow some basic guidelines and ensure that you have proper data integrity by implementing keys and constraints, naming your database objects correctly, and using the correct data types. Always remember to document the database with a data dictionary, an entity-relationship diagram (ERD), or both.

Understanding data integrity

Data integrity refers to the consistency and accuracy of data. In RDMS, keys enforce data integrity. A key forces values in a table to conform to a standard that you specify in the key. It's essential to enforce data integrity in your database so that you have accurate and consistent data. You want...

Best practices for indexing

Indexing is a method of optimizing database performance by reducing the amount of disk usage when running a query against the database. Indexes are placed on column(s) in a table. Tables can have more than one index, but there tends to be an optimal number of indexes that you can have before indexes start hurting performance instead of helping it. The optimal number can vary depending on the table; this is why index tuning can be an art as well as a science. To properly index a table, you need to have a good understanding of how the data in the table is being used.

It's good to plan out the indexing that you will need in advance of adding data, if possible. When you add an index to a blank table, it adds it pretty much instantaneously. If you add an index later, it can take quite a while depending on how much data you have, especially when adding...

Best practices for querying and modifying data

To query and modify data appropriately, you will need to follow some basic guidelines—including the following guidelines for writing clean code—to make your queries fast.

Understanding how to write clean code

It's important to write clean code for readability and ease of use so that you and others can easily understand what your code is trying to do. This can be done by following these tips:

• Formatting your SQL code for readability is important so that you and others can easily understand your SQL code. SQL ignores whitespace, making it easy to format for readability. You can write SQL code all on one line, but it's much easier to read if you place different...

Summary

In this chapter, you learned about the best practices for databases. You also learned about the best practices for database design, including which data types to use.

You also learned about the best practices for indexing, including indexing naming conventions, how indexes relate to data integrity with primary and foreign keys, how indexing impacts performance, and when to create indexes. You also learned about the best practices for querying and modifying data, including how to write clean code. You also learned about the best practices for querying data, with specific tips on SQL statements and clauses, as well as tips on best practices when modifying data.

In the next chapter, the SQL commands discussed in this book will be outlined for quick reference. The syntax for querying data, modifying data, and designing databases and tables will also be included. This will...

Questions

1. Why is data integrity important for your database?
2. Why is it important to name your database objects by following naming standards?
3. Why is it important to choose the right data types for your data?
4. Is there a right number of indexes or columns in an index on a table?
5. How do you see which indexes a query is using?
6. How do you write clean code?
7. What are some best practices when writing SQL statements?
8. What are some best practices when using the WHERE clause?
9. What are some best practices when using an INSERT statement?
10. What are some best practices when using a DELETE statement?