Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to achieve good database design". In the operation process of actual cases, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations. I hope you can read carefully and learn something!

1. Why database design?

No matter how the application or database changes, data is always the most important part. Data is usually the primary purpose of a system. This is why database systems should be viewed not only as black boxes for storing data, but as tools for validating and preventing data corruption.

Doing this requires robust and well-thought-out database design. Of course, the business logic is encoded at the application layer, which ensures that the data is in the correct format before it reaches the database.

But who can guarantee that network failures or flaws won't let unreliable "guests" through? Furthermore, the application layer is not the only "door" to the database. We can use import scripts, maintenance scripts, DBAs and developers interact with them. We can take precautions at the bottom to ensure that data is always checked before it is stored.

Having robust, reliable data also helps with development and testing. Setting a column to Not Null eliminates many test scenarios where the column is assumed to be null, and simplifies the code so developers don't have to check the value (almost) every time they access it.

Having emphasized the importance of good database design, let's look at what tools are available to implement it.

2. standardization

This is undoubtedly the first principle of good design. Here, we do not intend to delve into normalization rules, but merely to emphasize their importance.

Here's a good resource on this topic that you can read further on.

https://docs.microsoft.com/en-us/office/troubleshoot/access/database-normalization-description

3. data type

Another thing to watch out for is defining appropriate attribute types. This not only improves database performance, but also validates data before storing it. So we should store numeric data in integer or numeric fields; timestamps in timestamp or timestamptz fields; boolean values in bit or char(1) or boolean fields; and so on.

The date deserves special attention. If the Date attribute assumes only a date component (OrderDate, ReleaseDate), use the Date type without a time component. If you only need to reserve time (StartTime, EndTime), use the appropriate time type.

If you do not need to specify precision, specify zero ("time(0)"). One problem with dates with a time component is that you always have to truncate the time component to show only the date, and when you want to display it in a different time zone than the database, make sure it doesn't format as yesterday or tomorrow. Adding or subtracting a date time with a time component can also be problematic when jumping to daylight saving time.

4. constraint

Constraints are the focus of this paper. They exclude invalid data and ensure data robustness. Let's look at them one by one.

nonempty constraint

If a business rule requires that the attribute should always exist, do not hesitate to set it to Not Null. Fields suitable for Not Null are Id, Name, AddedDate, IsActive, State, CategoryId (if all items should have a category), ItemCount, Price, and many others. Often, these attributes play an important role in business logic. Other optional information fields may still be set to Null.

Be careful, however, not to use the Not Null constraint on nullable attributes. For example, a long-running task always has a StartTimestamp (Not Null), but EndTimestamp (Null) is updated only when the task is complete.

Another typical example is the ManagerId of the Employee table, not all employees have managers. Don't try to make ManagerId non-empty and insert "0" or "-1" for employees who don't have a manager. This causes other problems when we add foreign key constraints.

unique constraint

Similarly, according to business rules, some attributes (or combinations of attributes) should be unique, such as Id, PinNumber, BookId and AuthorId, OrderNo, etc. Uniqueness of these attributes should be guaranteed by adding unique constraints.

One more thing to note: you can use unique indexes to achieve the same effect, but adding constraints is a better way. Because non-unique indexes are automatically created when unique constraints are added.

Therefore, if for some reason you have to temporarily disable/enable constraints, it will be very easy. In the case of a unique index, you have to delete/recreate the index, which is an expensive operation in terms of performance and time.

primary key

Not Null and unique constraints together make up the primary key. When we think of primary keys, we quickly think of columns like Id or ObjectId. But the primary key can also be compound, such as BookId and AuthorId.

The puzzle here is, should I use the Id column alone as the primary key, or a combination of the two? In general, using a separate Id column is a better approach because it makes connections clearer and makes it easier to add another column to a unique combination. However, even with a separate primary key (Id), we still want to add unique constraints for the BookId and AuthorId columns.

Check constraints

Check constraints allow us to define valid values/ranges of data. Properties suitable for Check constraints are Percentage (0 to 100), Status (0, 1, 2), Price, Amount, Total (greater than or equal to 0), PinNumber (fixed length), and so on.

Likewise, do not attempt to encode business logic into Check constraints. I remember once adding a Check constraint of "greater than or equal to zero" to the AccountBalance column to avoid accidental overdraft.

default constraint

Default constraints are also important. They allow us to add new Not Null columns to existing tables and make the "old"API compatible with the new structure until all parties are upgraded (although default constraints should be removed after a full upgrade).

Here's something to remember. Do not write business logic in default constraints. For example, the function "now()" may be well suited (though not always) as a default for the timestamp field in the log table, but not for the OrderDate field in the Orders table. You might prefer to omit OrderDate from insert statements and rely on default constraints, but this means extending business logic to the database layer.

Also, in some cases, the business might assign OrderDate only after the order is approved, because the default constraint is buried deep in the database, so it's not as obvious when we make changes to the code at the application layer.

foreign key constraint

Foreign key constraints are the king of relational database design. Foreign keys work with primary keys to ensure data consistency between tables. Normalization rules tell us when to extract data into a table and reference it with foreign keys. Here we'll focus on the differences in detail, such as the OnDelete and OnUpdate rules.

Foreign key constraint editor in DBeaver

Let's start with the easy part: OnUpdate. Foreign keys reference primary keys, and they are rarely, if ever, modified. Therefore, the OnUpdate rule is not very common, but it makes sense to set it to Cascade because we may sometimes have to update the primary keys of certain rows (usually after migration). This way, the database will allow us to update and propagate the new id to the child tables.

The OnDelete rule is a bit complicated. Depending on the database, we have NoAction, Restrict, SetNull, SetDefault, and Cascade options. So, which one to choose?

In general, we select NoAction for entities whose key references lookup or does not reference entities. For example, Products -> Categories, Books -> Authors, etc. In most cases, Restrict and NoAction are the same, but for some databases they are slightly different.

https://www.vertabelo.com/blog/on-delete-restrict-vs-on-delete-no-action/

On the other hand, select Cascade when a child record cannot exist without a parent record. In the Book and Author example, when deleting a book, we should also delete records from the BookAuthor table. Other examples are OrderDetails -> Orders, PostComments -> Posts, etc. Here, some might disagree that the database should not automatically delete child rows, they should be deleted by the application layer. According to business logic, yes. But sometimes "unimportant" subitem deletions can be delegated to the database.

SetNull is rarely used. For example, the foreign key between Employee.ManagerId and Employee.Id can be SetNull. When a manager is fired, his subordinates have no manager. Obviously, this rule can only be selected if the column is nullable.

Of these rules, SetDefault is the rarest. When the parent record is deleted, it sets the column to its default value. Because a foreign key references a primary key, it is difficult to imagine a field with a foreign key hardcoding the default value. But anyway, the option exists, and we may still need it.

5. index

Indexes are an important part of good database design, but they deviate somewhat from our discussion because they do little to protect our data (except for a single index).

One thing to note is that some RDBMS systems (such as Oracle) automatically create indexes when they create foreign keys without us having to worry about them. Other databases (e.g. MS SQL Server) don't do this, we have to add indexes ourselves.

"How to achieve good database design" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.