preface

The database table design I mentioned here involves how to organize your business table, how to design, how to redundant fields, etc., but these are my personal views. You are welcome to discuss different views with me in the message area

1, Database table?

According to my understanding, database tables are generally divided into basic information tables and business tables. Basic information tables are some basic information service tables. Business tables are more or less used. Business tables are actually a business form abstracted from basic information and an abstraction from basic information.
Basic information table: Commodity table:

CREATE TABLE `sku_tab` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sku_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `category_id` int(10) unsigned NOT NULL,
  `name` varbinary(1024) NOT NULL DEFAULT '',
  `is_sn_mgt` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `description` blob,
  `images` varchar(1024) COLLATE utf8mb4_unicode_ci NOT NULL,
  `country` char(4) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sku_id` (`sku_id`),
  KEY `idx_country` (`country`),
  KEY `idx_sku_name` (`name`(767)),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Business table: a detail table:

CREATE TABLE `order_item_tab` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `cc_order_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `sku_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `sku_name` varchar(1024) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `location_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `is_sn_mgt` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `qty` int(10) unsigned NOT NULL DEFAULT '0',
  `ctime` int(10) unsigned NOT NULL DEFAULT '0',
  `mtime` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_cc_order_id` (`cc_order_id`),
  KEY `idx_location_id` (`location_id`),
  KEY `idx_sku_id` (`sku_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Here is a typical negative example. The SKU information of the basic information service is redundant: sku_id,sku_name,is_sn_mgt.
Advantage: search for sku on the page_ id,sku_name,is_sn_mgt does not need to go to the basic information table to query these information.
Disadvantages: once the basic information service changes sku_name,is_sn_mgt will not update these business tables, so the page cannot be searched.
here:
Because sku_id has only one key, so it is destined not to be changed again, so this business table is redundant sku_id is no problem,
But sku_name and is_sn_mgt has no unique key that can be changed. It is redundant here. Once the basic information service sku changes the sku_name or is_sn_mgt, then the business will be unable to search here.
You may say that there is no problem with redundancy here. When the basic information service changes, synchronize these business tables and change the corresponding information together. This is even more infeasible. First of all, there are so many business tables. Are all business table information modified synchronously as soon as the basic information is changed? Moreover, the business table and the basic information belong to the relationship between the upper layer and the lower layer. The changes of the lower layer cannot be modified directly by calling the upper layer.
So the question is, how to choose the design table?
When some basic information needs to be searched in the business table search box, such as sku_id such as sku_name, if the basic information to be searched is unique in the sku table in the original table, you can be assured of redundancy, but if the basic information to be searched, such as sku_name is not unique in the original table and can be changed, so I don't think it is suitable for redundancy.
This is not only a design problem, but also a philosophical problem. Whether to choose redundancy or not depends on your own business.

summary

The design of data table is very important, because if the database design is not good, you will not be very comfortable when writing code. Here, you need to think about how to design database table.