Introduction

I am Morimoto from KINTO Technologies' New Vehicle Subscription group. I work as a backend engineer.

For the 2022 Advent Calendar, I wrote an article about holding a study group for GraphQL:

For this year’s 2023 Advent Calendar, I would like to talk about the renovation of our subscription site (KINTO ONE) which I worked on for two years from August 2021 when I joined the company, to its launch in August 2023. More specifically, I would like to focus on the considerations we did when reviewing its table design, which was my first job after joining the company.

What Went Into the Table Design

Below is an illustration of the table before and after the renovation, although it’s not exactly the same one we work with.

The table contains information on employees and can be tracked by logically deleting a record and adding a new one whenever there is an update.

Note: A logical deletion is when a record is not deleted, but is flagged and treated as if it is deleted. One advantage is that it can track the revision history of important values.

Before Renovation

After Renovation

Each column contains the following information.

• id: an ID that uniquely identifies a record in the employees table
• employee_id: an ID that uniquely identifies an employee
• mail_address: email address
• pref/prefecture: prefecture
• group_and_role/group and role: group and role that an employee belongs to
• delete_flag/is_deleted: whether the record has been deleted

The Four Key Areas We Reviewed

1. Not using abbreviations for columns
2. Not replacing values with numbers
3. Not giving multiple meanings to a single column
4. To Not Name Flag Columns _flag

1. Not using abbreviations for columns

We did not abbreviate column names, like we did for pref.

One can more or less figure out that it represents Japanese prefectures. However, if you use this in documentation or source code, there is a high chance that it will be mixed up with pref or prf. Some people may not know what the abbreviation stands for in the first place.

Because of that, we did not use abbreviations, and used full notations such as prefecture, even if they were long.

2. Not replacing values with numbers

As you can see in the Before Renovation table, pref has a value of 23. Maybe you can arrive to the conclusion that 23 represents of one the 47 prefectures of Japan, but which one is the 23rd?

It is not intuitive, so it's impossible to tell what this value means. Every time you look at a value, you have to refer to a list of numbers and prefectures, which makes it less readable. It also makes it harder to find mistakes. Because of that, we stored the prefectures using their names, such as AICHI.

One advantage is that if there is an invalid value, it comes up as an error when it is read into a program.

As an example, here you have the structure if we used Java as programming language and JPA was used as an O/R mapper. Also, if we define the enum class of prefecture as follows and use that type with the user entity, it will raise an error if there is an invalid value such as AICHIA.

public enum Prefecture {
  HOKKAIDO ("Hokkaido"),
  .
  .
  .
  AICHI ("Aichi"),
  .
  .
  .
  OKINAWA ("Okinawa");

  private String value;
}

@Entity
public class Employee {
  /** ID */
  @Id
  private Long id;
  .
  .
  .
  /** Prefecture */
  @Enumerated(EnumType.STRING)
  @Column 
  private Prefecture prefecture;
  .
  .
  .
}

If mapping is not possible, the following exception will be raised.

java.lang.IllegalArgumentException: No enum constant com.example.demo.values.Prefecture.AICHIA

3. Not giving multiple meanings to a single column

Before the renovation, columns such as group_and_role had two pieces of information in one column: an employee's department and their role. This may seem fine at first, but this makes it less flexible when adding, deleting, or changing a value.

Suppose there are four definitions as follows.

1. HR_Manager
2. HR_Staff
3. Development_Manager
4. Development_Staff

If you try to rename the Development group to NewDevelopment, the update SQL condition becomes more complex.

1. HR_Manager
2. HR_Staff
3. Development_Manager　→ NewDevelopment_Manager
4. Development_Staff → NewDevelopment_Staff

There are also problems with searching and sorting. Search queries become more complex, and you have to separate the part after the _ (underscore) when sorting. It also makes them less readable.

Because of that, for the site renovation, we separated each column so each one has one meaning.

4. Do Not Name Flag Columns as _flag

You can see that the delete_flag column indicates that an item was deleted. However, it is unclear whether "true" means it was deleted or not.

So, we decided to use the verb "to be" with a past participle, like in is_deleted. This way, it is clear that "true" means an item was deleted, and "false" means it was not deleted.

It is important that there are no inconsistencies with the name of the column and the values in it are clearly understood. For example, the is_deleted column lets you logically delete old records when you are updating information on employees.

• true: deleted
• false: not deleted

If a third state other than true or false appears in a flag column, you should change it from the flag column.

Continuous Improvement

The design of the tables was not a one-time event, but it kept being refined right till a few months before the official launch.

Changing a table while various tests are being conducted, can have a large impact. In addition to improvements added by the development team, it sometimes included updates from feedback of re-run tests that the backend and frontend team did combined. Not only were we able to establish a connection between the backend program and the table, but since we wanted a smooth integration with the API interface, we worked with each team in charge of the APIs as well. I think everyone involved with the project focused on the main goal to renovate, instead of worrying about the scale of the problems in front of them.

Another good thing within the backend team was that we could proactively voice suggestions for topics we thought that needed improvement. Not just when it came to the the table design, but the continuous improvement throughout development was I think one of the factors that led to the success of the renovation project.

Conclusion

We made major changes to the previous tables, such as changing the structure itself and deleting unused columns.

This was the first task I was involved in when I joined the company, and I had to start designing the structure of the subscription site without fully understanding its features. In the middle of development, we checked operations, noticed values that were not made permanent, or even found out only during tests that some parts were defined incorrectly.

Even after the launch, there were many times when we thought, "Why did we name it like this?" or “Maybe we should have separated the table here.”

However, thanks to team members and the people who accepted the changes to the tables throughout the project, we were able to launch the site without issues.

As we continue to improve them, we will continue to do out best to make them easier to understand.