MySQL Subversion practical Note (iv)--Commodity system design (i): Product master table Design

Copyright Notice: NOTE-taker fugitive pawns love freedom, advocating sharing. But this note stems from the www.jtthink.com (programmer in the awkward way) Judging teacher's "web-level MySQL subversive combat course." For reprint please respect the teacher Labor, retain the judging teacher attribution and course source address.

　　Mr. Shen takes us to the product list first.

Fixed goods: For example, we just sell shoes, then the whole product properties are basically consistent, such as shoes color, size, style, brand, price. The tables we are involved in are often flat.

The characteristics of this approach include:

1, pure customization.

2, development fast, just as long as the development of certain elements.

3, but poor scalability, once we have added other types of goods, then the system will be re-done.

4, time is long, this table will be extremely big. Affect system performance.

So we try to design a relatively generic commodity table as much as possible. To fit most of the e-commerce system.

Today we are going to design a product master table (we first assume that we are consumer-only)

Contains common information:

(i) immediate update: ID, product name, category, storage time, last modified time, product introduction.

(ii) Delay Update/Log update: Total hits, monthly clicks, total sales, monthly sales, total evaluation, monthly evaluation.

　　

Below we create table Prod_main in Navicat:

　　

Where prod_id is set to auto-grow, set as primary key.

Prod_adddate sets the default value to Current_timestamp.

We then fill in the table with a single data based on the field.

Qizho

　　
　　
　　
Click-to-volume log table:
　　As an e-commerce system, we do not know the total number of hits, but also need to know the product of the week click and the month click, and then the data can not be recorded in the main table of the product.
　　In the course we are simple, design to the month click on the good.
Let's create the Prod_clicklog table:
　　
　　
　　Next, we use stored procedures to simulate the Reading products page:
1. Read all information from the Product master table according to the ID.
2, if read, then record the click Log.
We need to pay attention to the knowledge Point: Found_rows () Select returns the result and number of the most recent SQL.
Row_count (): Update Delete Inserts the number of affected bars.

BEGIN    SELECT *  fromProd_mainWHEREprod_id=_prod_id LIMIT1; SET @num=found_rows (); IF @num=1  Then#代表商品取出成功INSERT  intoProd_clicklog (PROD_ID,USER_IP,user_id)VALUES(_prod_id,_user_ip,_user_id); END IF;ENDParameters:inch_prod_idint,inch_user_ipvarchar( the),inch_user_id int

　　The usual, we create a new query, call, get results:

　　

then we'll:

SELECT *  from Prod_clicklog;

　　

See you next Class!

previous: MySQL Subversion real-World notes (iii)--user Login (ii): How to save the user action log

MySQL Subversion practical Note (iv)--Commodity system design (i): Product master table Design