Database denormalization design: application in PHP programming

With the rapid development of Internet technology, databases, as data storage and management centers, have become an indispensable part of the modern information age. When designing a database, many developers will hope to be as close to the database standardized design theory as possible so that the structure of the data table is reasonable, standardized, and convenient for maintenance and query. However, in some cases, denormalization is also a very useful technique to optimize the efficiency of data storage and access through redundant data and proper data organization. In PHP programming, anti-paradigm design can greatly improve performance, reduce database machine load, enhance database scalability, and shorten development cycles. This article will describe PHP from the aspects of anti-paradigm design principles, implementation, and applicable scenarios. How to use de-paradigm design in programming.

1. Normalized design and de-normalized design

When designing a database, reasonable normalized design aims to make the structure of the data table more standardized, concise and accurate, simplify operations, and Reduce development difficulty and data redundancy, and improve data transmission and security. Database normalization theory mainly has several levels such as 1NF (first normal form), 2NF (second normal form), 3NF (third normal form), etc., which restrict the atomicity of attributes, relationship dependencies between entities, and data redundancy. aspect.

Denormalization design is the opposite concept. It aims to improve database performance by increasing certain data redundancy, optimizing queries, and speeding up. This is mainly achieved through denormalization, such as splitting fields. Divide into multiple tables to reduce JOIN operations when querying, or store redundant data in multiple tables to avoid JOIN when updating. However, anti-paradigm design also has certain limitations. In most cases, excellent paradigm design is still the first choice.

2. Application scenarios of de-normalized design

The principle that should be followed in de-normalized design is that when weighing the design, the needs of developers and user-oriented needs need to be taken into consideration.

1. Scenarios with high query requirements

When frequent queries are required, using denormalization design can optimize query efficiency and reduce JOIN operations and multi-layer queries, etc. Unnecessary queries, such as user login information, order details, etc. can be loaded into the cache first and output directly when the page is displayed.

2. When the database has a high volume of writes or access

Using denormalization design can reduce the pressure on the database and split some data that does not need to be changed into On multiple tables, to avoid locks during writing, for example: product prices, certain text descriptions, etc. can be cached somewhere first, and then retrieved from the cache when actually writing.

3. Scenarios where some data needs to be updated/deleted in a timely manner

Using de-normalization design can also avoid the situation where UPDATE and DELETE are too inefficient. When the amount of data is large, UPDATE and DELETE operations will consume a lot of resources. Using denormalization design, some data can be written into multiple tables to achieve distributed processing and partial update/deletion.

3. How to apply de-normalization design in PHP programming?

The following is a specific PHP programming example to show how to use denormalization design:

1. Premise: The order number, product ID, product name, etc. need to be displayed on the order details page. Product unit price, product quantity, product subtotal. There are two tables, the order table and the product table. The order table contains the order number, product ID, and product quantity fields, and the product table contains the product ID, product name, and product unit price fields.
2. Normal design plan: Query orders and corresponding product information by connecting two tables. As shown below:

SELECT order_no, product_id, product_name, product_price, product_qty, (product_price * product_qty) AS sub_total 
FROM order_tbl
LEFT OUTER JOIN product_tbl
ON order_tbl.product_id = product_tbl.product_id
WHERE order_no = '1001';

3. Denormalization design plan: Redundant the product name and product unit price fields in the order table to the order table to reduce Join operations during query . As shown below:

SELECT order_no, product_id, product_name, product_price, product_qty, (product_price * product_qty) AS sub_total 
FROM order_tbl
WHERE order_no = '1001';

4. Implementation steps:

(1) Create two tables: order_tbl and product_tbl.

CREATE TABLE `order_tbl` (
  `order_no` varchar(100) NOT NULL,
  `product_id` int(11) NOT NULL,
  `product_qty` int(11) NOT NULL,
  `product_name` varchar(100) DEFAULT NULL,
  `product_price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`order_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `product_tbl` (
  `product_id` int(11) NOT NULL,
  `product_name` varchar(100) NOT NULL,
  `product_price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

(2) There are two redundant fields in the order table order_tbl: product_name and product_price.

ALTER TABLE `order_tbl` ADD COLUMN `product_name` VARCHAR(100) NOT NULL DEFAULT '';
ALTER TABLE `order_tbl` ADD COLUMN `product_price` DECIMAL(10,2) NOT NULL DEFAULT '0.00';

(3) When writing an order, write data to order_tbl and cache table cache_tbl.

//写入订单表
$sql = "INSERT INTO order_tbl(order_no, product_id, product_qty, product_name, product_price) 
        VALUES ('$order_no', $product_id, $product_qty, '$product_name', $product_price)";

//写入缓存表
$sql_cache = "INSERT INTO cache_tbl(key_name, cache_value) 
              VALUES ('product_info_${product_id}','{"product_name":"${product_name}", "product_price":"${product_price}"}')";

(4) When querying an order, first obtain the product name and price from the cache table cache_tbl. If they do not exist in the cache, query the product name and price from the product table product_tbl and cache them in cache_tbl.

$redis = new Redis(); 
$redis->connect('127.0.0.1', 6379);

$key_name = "product_info_${product_id}";
if ($redis->exists($key_name)) {
  $cache_data = json_decode($redis->get($key_name), true);
  $product_name = $cache_data['product_name'];
  $product_price = $cache_data['product_price'];
} else {
  $sql = "SELECT product_name, product_price FROM product_tbl WHERE product_id=$product_id";
  $result = mysqli_query($conn, $sql);
  $row = mysqli_fetch_array($result);
  $product_name = $row['product_name'];
  $product_price = $row['product_price'];
  $redis->set($key_name, json_encode(['product_name'=>$product_name, 'product_price'=>$product_price]));
}

$sql = "SELECT order_no, product_id, product_name, product_price, product_qty, (product_price * product_qty) AS sub_total 
        FROM order_tbl
        WHERE order_no = '1001'";

In this example, we use Redis for caching. When querying order details, we first obtain the product name and price from the cache. If they do not exist in the cache, we query them from the product table and write them to the cache. middle. Through this method, we avoid JOIN operations and greatly improve query efficiency and performance.

4. Summary

Anti-paradigm design has both advantages and disadvantages, and reasonable application is the key. When designing a database, you should make various trade-offs based on the actual situation, weigh the pros and cons, and use design methods flexibly. In PHP programming, optimizing database operations through denormalization design is also a very useful and practical technology. We can improve database performance and efficiency, system response speed and user satisfaction by adding redundant data, distributed processing, caching and indexing strategies and other means.

The above is the detailed content of Database denormalization design: application in PHP programming. For more information, please follow other related articles on the PHP Chinese website!