Improve Internet program development efficiency with intermediate tables

Transfer (http://www.cnblogs.com/liyulong1982/archive/2012/06/04/2535264.html)
 

Basic design ideas:

Generates an intermediate table centered on the core business data to merge the main business data. To simplify the InternetProgramDesign complexity. This design can be used for most Internet products such as e-commerce and online video. The following uses the Dangdang network as an example:

Example

If we need to create a book sales page like Dangdang, suppose we have 2 million graph books and data is stored in MySQL, we need at least:

1. Generate homepage recommendations: including titles, prices, scores, and comments of recommended books
2. Generate rankings: including book titles, prices, scores, and comments
3. Generate a book details page: including the book title, price, score, and comment information
4. As a search engine, you can search for books based on titles, authors, and so on. The search page contains the book title, price, score, and comment information.

We usually design databases like this:

1. Basic information table of books (c_id, c_title, c_authors, c_publish... )
2. Price List (c_id, c_price, c_discount ...)
3. Score Table (c_id, c_score ...)
4. Comment table (c_id, c_comment_count ...)
5. Recommended books on the home page (c_pos, c_ids)
6. Xxx
7. Xxx

The first three requirements are not too complex. Read the data in each table to the memory, get a dataset, and then output the static page. However, to read many tables and then merge the data, this operation is still very cumbersome (I believe you will not use an SQL statement to join multiple tables), and, as operational requirements change, more and more tables need to be read. We need to constantly adjust the program to read more tables. The requirement for 4th searches is a little complicated. We could have used sphinx, but sphinx could not be used due to the complex table relationship.

If all the information in the table exists, the above problem can be solved. For example: (c_id, c_title, c_authors, c_publish, c_price, c_discount, c_score, c_comment_count) but this DB Design is unacceptable. There are many reasons:

1. A single record is too long and DB efficiency is low
2. High coupling of modules. All modules are coupled by DB.
3. More importantly, what if the storage structure of a module is not MySQL db.

My design scheme:

The table structure is designed as we mentioned earlier, but an intermediate table is added, that is, the large table containing all the information of the book (c_id, c_title, c_authors, c_publish, c_price, c_discount, c_score, c_comment_count... ). Our program synchronizes the data in other tables to this large table at intervals (such as 2 minutes. The business logic in our previous requirement is implemented through this large table. Now we only need one SQL statement to read the information of all books, and then we can use this information to generate a page without any merge tables.

What are the possible disadvantages of this design scheme?

1. A single record is too long and the efficiency is low. The efficiency is indeed low, but it does not matter. Our table is read-only and used to generate pages for retrieval. What is the relationship between slowness and speed? If you still think she is slow, add another layer.Memcached/redis/leveldb.
2. Real-time performance problems, because this intermediate table is updated regularly, there must be a few minutes of delay, but what is the relationship, except for the price and number of comments on the details page, other information is not sensitive, as long as we get the sensitive information in real time on the details page.

Therefore, these shortcomings are not too many problems.

What are the advantages of this design scheme? More advantages

1. Solve the search problem. Let's make full use of sphinx. Even if the data is stored in a non-mysql storage structure, Sphinx is still available if we can import the data to this intermediate table, and you only need a simple SQL statement;
2. It simplifies program development and completes all data acquisition processes with one SQL statement;
3. This reduces program coupling. Without this intermediate table, if we want to migrate one of the modules to other DB or non-DB engines, many programs have to be modified, now we only need to modify the program for synchronizing data to the intermediate table.