Things I learned from reading "A Thorough Guide to Database Design: Learning from Experts"

Nice to meet you! I'm Kanda, a second-year employee in the Systems Development Department (^^)/

This time, I've summarized my impressions of reading " The Ultimate Guide to Database Design: A Masterclass, 2nd Edition ," and the points that I found particularly insightful!

Why did I pick up this book?

In practical work, we sometimes design tables and APIs while reading through specifications

At times like these, this question pops into my head..

• "First of all, what values ​​should I store in the table?"
• "Should this column be included in the existing table, or should it be separated into a different table?"
• "Besides the user ID, where else should I place the index?"

In actual design work, we resolve these kinds of questions by referring to existing tables, asking AI, and consulting with senior colleagues as we
proceed with the design.

As the design process continued in this manner,

"With these specifications, the table structure will probably look something like this."

"It would be easier to use if this column were separated into a different table."

And nowI can vaguely imagine what a good shape might look like.

In this way, as I gained experience, I became able to design more smoothly than before. However,
one day, upon reflection, I realized that I was simply following the existing table structure, and
that I wasn't designing with a fundamental understanding of "why this table structure is good in the first place."

Therefore, I picked up this book
because I wanted to learn what constitutes good database design, the criteria for judging it, and be able to design databases with confidence and a solid foundation!

After actually reading it, I felt that I could highly recommend it to engineers in their first or second year who are facing similar problems to mine!

The second edition of "A Thorough Guide to Database Design: Learning from the Experts"includes the following content.
If you think it might contain what you're looking for, please give it a read!

[Table of Contents]
Chapter 1: He who controls the database controls the system
Chapter 2: Logical design and physical design
Chapter 3: Logical design and normalization - Why do tables need to be partitioned?
Chapter 4: ER diagrams - Representing the relationships between multiple tables
Chapter 5: Logical design and performance - Disadvantages of normalization and denormalization
Chapter 6: Databases and performance
Chapter 7: Anti-patterns in logical design
Chapter 8: Gray area know-how in logical design
Chapter 9: Advanced logical design - Handling tree structures in RDBs Appendix
(Answers to exercises)

Overall impression

1. The writing is simple and easy to understand, and it's structured in a way that prevents frustration and boredom.
   There are no quirky expressions or sentences that get stuck, making it very easy to read.
   Personally, I tend to get discouraged when books contain a lot of code, thinking, "Maybe this is a million years too early for me to read..." However,
   since this book is about databases, there are only a few SQL statements, so I was able to read through it without any difficulty.
   key points " at regular intervals , and practice problems are provided.
   As a result, I was able to read through it without getting discouraged or bored!
2. This book , which helps you develop various perspectives when designing databases,
   is primarily a "guidebook," so it does
   n't delve deeply into specific areas or provide detailed instructions on concrete database design procedures.
   it broadly covers topics
   ranging from "What is a database?"
   The difficulty level was neither too difficult nor too easy.
   It also covers normalization and ER diagrams, which are frequently encountered in practice, so
   those with practical experience should be able to follow along without difficulty understanding the concepts.
   On the other hand, the latter half of the book gradually becomes more theoretical.
   Chapter 9, "Advanced Logical Design - Handling Tree Structures in RDBs," rather than those looking for immediate practical application
   felt more geared towards those who want to learn more about databases
   Therefore, I recommend reading the sections that are directly relevant to your work first, and then reading the more theoretical parts when you have more time!

Things I learned in particular

Why do we design the database first?

Have you ever wondered, "Why do we design the database first?"

I had taken it for granted that designing the database was the first step, and I never even questioned it

However, after reading this book, I learned that designing data before programming hasn't always been the norm, and that
there was a time when the order was reversed.

The approach of designing in the order of "data ⇒ program" is called the data-centric approach (DOA), while
the approach of designing in the order of "program ⇒ data" is called the process-centric approach (POA).

I was curious about the reasons behind the shift in popularity from POA to DOA, so I did a little research

The era when POA was dominant

POA (Point of Action) was one of the dominant approaches in system development during the 1960s and 70s

At that time, , such as " Let's use computers for payroll calculations!"
there was a trend to replace tasks that were previously done manually by humans with computers

In that case, the first step is to identify exactly what tasks humans were performing and
then translate those tasks directly into a program.
Using payroll calculation as an example, the tasks would look like this:

1. Collect time cards
2. Count the number of days worked and the amount of overtime
3. Find out the base salary from the employee roster
4. The amount paid is calculated by adding overtime pay and subtracting taxes and insurance premiums

Next, we'll translate these steps directly into a program.
How and what data to store will be decided after the steps are finalized, according to the program's needs. This
approach of prioritizing steps and then data is what we call POA (Procedure-Oriented Architecture).

Reasons why DOA has become important

However, business processes do change

For example, data such as "employee base salary" is used in both payroll calculations and performance evaluations

However, if you design a system from a process-centric perspective, you end up having separate data for each process, such as data for payroll calculations and data for performance evaluations. Even though the content is the same—"employee base salary"—the same information ends up scattered all over the place

This means that when the base salary is revised, every single detail must be updated without fail.
If even one detail is forgotten to be updated, different amounts will be displayed on different screens.

That's when Dead or Alive (DOA) came to be given importance

While processes can change indefinitely, the data itself, which is ultimately handled, is less prone to change (it is persistent) compared to the process. Focusing on this point, the DOA (Data-Oriented Object) approach aims to define the meaning and format of the data beforehand

If you decide beforehand that "employee information includes employee number, name, department, and base salary," then you only need to refer to the same data in any process, whether it's payroll calculation or performance evaluation. Even if the base salary is revised, you only need to make the changes in one place

Furthermore, the widespread adoption of relational databases also played a role. Unlike the days when data was stored in files, data can now be centrally managed and retrieved later in any way that suits the user. Designing systems with data at the center has become technically feasible

This is how the now-common practice of "starting with database design" came about

So, what exactly does database design involve deciding?

 It's the layer that displays only the necessary data for each user.
For example, in a system that handles employee information, the employee list screen might only display names and departments,
while another screen might display salaries as well. The same data can be displayed in different ways depending on the purpose.
Even though the appearance may differ, the underlying data is, of course, the same.
The same data is presented in different ways depending on the viewer and the purpose. Defining this presentation method is what the external schema is all about.

To understand this, you need to grasp the concept of a three-tier schema

3-tier schema

1. External schema —
    This is the layer that displays only the necessary data to each user, from the user's perspective of the database.
   For example, in a system that handles employee information, the employee list screen might display only the name and department,
   while another screen might display the salary as well. The same data can be displayed in different ways depending on the purpose.
   The underlying data is, of course, the same, even though the appearance is different.
   The definition of this method of displaying the same data in different ways depending on the viewer and purpose is what constitutes an external schema.

1. Conceptual schema — Defines the logical structure of the entire database, including database tables and the relationships between tables, from a developer's perspective
   
   Users cannot directly see the table structure of this system. (It would be a major problem if they could!)
2. Internal Schema —
   This is the part of the database from the DBMS's perspective that defines how data is actually stored and managed within the DBMS.
   We usually think of it as "creating a table," but the DBMS internally stores data in files and other storage.
   For example users table, the DBMS writes it to its internal storage area.
   A table is merely how it looks from a human perspective!
   Specifically, this layer determines things like the following:

• Where and how to create indexes —which columns to use and what structure (e.g., B-tree) to create them in to speed up searches.
• How to organize the data —this refers to storage locations, such as whether to split a massive table into sections based on dates or other criteria.

Why is a conceptual schema necessary?

Those who have read this far may be wondering, "Wait, if the internal schema is the core, why do we need a conceptual schema?"
Having a conceptual schema layer allows us to separate the external schema and the internal schema, making it easier to minimize the impact of changes.
let's say you want to display the " department name " on the employee list screen
If you have a conceptual schema, and the employee table and department table are already defined, all you need to do is connect them and display the information. The physical method of storing the data does not change at all.

However, if the external schema and internal schema are directly linked, the items displayed on the screen will be directly reflected in the file. For example, the "Employee List Screen File" will contain names, ages, and start dates. It might be easier to understand if you think of the screen's appearance as directly reflecting the data's form. To add department names to this, you would have to recreate the file itself and transfer all existing records to the new format

Therefore, a conceptual schema is used as a buffer, separating the external schema from the internal schema.
No matter how the screen changes or how the storage method changes, the impact can be more easily absorbed between each layer.
This makes it easier to make changes without worrying about each other.

Designing this conceptual schema is called "logical design," and designing the internal schema is called "physical design."
Since it's impossible to determine how to store something until you've decided what it will hold, the process proceeds in the order of logical design ⇒ physical design.

Afterword

I actually wanted to talk about normalization and indexing as well, but this post has become longer than I expected, so I'll save that for next time!

These days, it's commonplace to design and implement things together with AI, but I'm constantly reminded that, ultimately, a solid foundation of my own knowledge and understanding is absolutely essential to effectively utilize AI

While it's certainly important to keep up with things that evolve daily, like AI, I realized it's equally important to focus on the fundamental aspects that will remain unchanged even several years from now

That's precisely why the fundamental idea of ​​managing data in a database won't change anytime soon, and
I felt that the knowledge I learned would be useful for a long time to come.

Thank you for reading all the way to the end (*'▽')