Creación Avanzada de una Tabla Products en MySQL

Approaching a realistic database involves thinking about how to design tables closer to the practical needs of the market. You will learn how to rebuild a products table incorporating fundamental elements such as SKU and automatic timestamps, as well as understand how to correctly optimize the massive insertion of data.

How to create a realistic products table in MySQL?

A useful and real-world structure of a products table must include certain key elements. These elements allow you to clearly identify each product and manage its information efficiently in everyday situations.

• product ID: integer, unsigned, primary key autoincremental.
• SKU: globally unique product identifier, varchar type.
• name: descriptive name of the product.
• slug: URL friendly identifier, unique.
• description: detailed text about the product.
• price: monetary value of the product, can be optional.
• created_at and modified_at: timestamps for automatic tracking.

The appropriate SQL statement to create this table would be a simple but powerful structure:

CREATE  TABLE IF NOT EXISTS products  (  product_ID INTEGER UNSIGNED PRIMARY  KEY AUTO_INCREMENT,  SKU  VARCHAR(20) NOT  NULL UNIQUE,  name  VARCHAR(50) NOT  NULL,  slug  VARCHAR(50) NOT  NULL UNIQUE,
  description  TEXT,  price  DOUBLE(10,2),  created_at TIMESTAMP NOT  NULL DEFAULT CURRENT_TIMESTAMP,  modified_at TIMESTAMP NOT  NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

What is the optimal way to insert a lot of data?

When handling the insertion of thousands of records, it is critical to understand the importance of efficient management of system resources. Doing this correctly ensures overall performance stability, especially in real productions where multiple users are connected simultaneously.

The recommended practice is to insert in small blocks or chunks:

• Insert in smaller blocks (e.g., 100 records per block).
• Avoid inserting thousands of data in a single transaction so as not to hog all system resources.
• Alternate inserts with other requests (user selects, new inserts).

How to insert data with the terminal in MySQL?

A valuable skill in development is to manage databases through the terminal. To insert data from an external file into MySQL, the example would be as follows:

• Create SQL file:

echo "INSERT INTO products (SKU, name, slug, description, description, price) VALUES (...)" > file.sql

• Insert from the terminal:

mysql -u user -p -D database < file.sql

This method allows you to execute SQL commands directly and is useful for large volumes of data or automated scripts.

What happens in case of data conflicts?

When bulk inserting it is usual to encounter conflicts such as duplicates in single columns (e.g. SKU or slug). You can easily handle this situation by using the INSERT IGNORE statement, which ignores and continues with the insertion of valid records, omitting only the conflicting rows.

Finally, analyzing how many records were actually inserted is accomplished with a simple query:

SELECT COUNT(*) FROM products;

What techniques do you currently use to optimize your databases? Share your strategies in the comments.