Mysql subversion Practice Notes (4) -- product system design (1): Product master table design and mysql System Design

Mysql subversion Practice Notes (4) -- product system design (1): Product master table design and mysql System Design
Copyright statement: the note organizer loves freedom and advocates sharing. However, this note is sourced from www.jtthink.com (where programmers are at ), instructor Shen Yi's "practical course on web-level mysql disruption". If you need to repost the course, please respect the teacher's work and retain the instructor's signature and course source address.

In these lessons, Mr. Shen first showed us the commodity table.

Fixed items: for example, if we only sell shoes, the attributes of the entire item are basically the same, such as the color, size, style, brand, and price of the shoes. At this time, the tables involved are usually flat.

Features of this method:

1. Pure customization.

2. fast development. Only development for certain elements is required.

3. But the scalability is poor. Once we add other types of products, the real system will be re-implemented.

4. After a long time, this table will be very large. Affects system performance.

So we try our best to design a relatively general commodity table. To adapt to most e-commerce systems.

Today, we will first design a commodity master table (we assume that we are B2C)

Contains common information:

(1) instant update: id, product name, category, warehouse receiving time, last modification time, product description.

(2) Delayed update/log update: Total clicks, monthly clicks, total sales, monthly sales, total ratings, and monthly ratings.

　　

Next we will create the table prod_main in navicat:

　　

Prod_id is set to automatically increase and set to primary key.

The default value of prod_adddate is CURRENT_TIMESTAMP.

Then we can enter a data entry for the table based on the field.

 

 

Qizho

　　Create a simple item category table, prod_class:
　　
　　
Click Log table:
　　As an e-commerce system, we know the total number of clicks on a product. We also need to know the weekly and monthly clicks on the product. Then, the data cannot be recorded in the main table of the product.
　　In the course, we can simply design the course to monthly click.
Let's create the prod_clicklog table:
　　
　　
　　Next, we use the stored procedure to simulate the reading product page:
1. Read all product information from the commodity master table based on the ID.
2. If the log is read, Click Log.
Note: FOUND_ROWS () select returns the result and number of the last SQL statement.
ROW_COUNT (): Number of affected items in update delete insert.

Begin select * FROM prod_main WHERE prod_id = _ prod_id LIMIT 1; SET @ num = FOUND_ROWS (); IF @ num = 1 THEN # indicates that the product is successfully retrieved. insert into prod_clicklog (prod_id, user_ip, user_id) VALUES (_ prod_id, _ user_ip, _ user_id); end if; END parameter: IN _ prod_id int, IN _ user_ip varchar (15), IN _ user_id int

Old rule: Create a query and CALL it. The result is as follows:

　　

 

Then we will:

SELECT * from prod_clicklog;

 

　　

Goodbye to next lesson!

Previous section: mysql subversion (iii) -- User Logon (II): Method for saving user operation logs