Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to realize the design of master-slave tables in the database". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "how to achieve the design of master-slave tables in the database"!

For example, I now have some news information, which includes these fields; news ID, news Name, news ShortIntro, news Detail, news PublishTime. I'm going to store it in the database, then take it out of the database and page it in GridView.

I'm going to simulate this implementation step by step in a common way of thinking that I've seen.

Step 1: create a news datasheet.

At this step, many people will directly create a News table that includes the fields mentioned above.

Step 2: query the data.

Write a method to take the data from the News table that meets the query criteria and put it in DataSet (DataTable) as the data source.

Step 3: bind to GridView.

Set the paging property of GridView, bind the data source obtained by the above query to GridView, and realize the paging display of the data in GridView.

That's what we usually do.

What I would do would be like this:

Step 1: establish a master-slave table of news data.

In the process of system development, we will find that, in fact, in a complete piece of data information, in fact, many list items are not used. We analyze the News information, we can initially take ID,Name,ShortIntro,PublishTime as the main information, we collect this information, create a new table News (ID,Name,ShortIntro,PublishTime), another field Detail in another slave table, and create a new table NewsDetail (ID,Detail,NewsID). What are the benefits of this? first of all, we reduce the "weight" of the watch. We simplify the most important and commonly used information into a master table, so that in the process of use, we only need to get the data we need from this table, instead of traversing all fields like the first method, which reduces database query time and improves performance. The principle of master-slave table establishment is to separate the most important and most commonly used tables as master tables and those that are descriptive and with large content as slave tables.

Step 2: write the appropriate SQL statement.

We should write appropriate SQL statements for different functional implementations. In the above method, all the data information is queried in one method, which meets the data requirements of all occasions. However, we don't need so much data content, and the extra data content consumes a lot of time and space. We often only need some of it, such as the main information. This also confirms why we need to set up a master-slave table. After we have established the master-slave table, we can write the following methods to meet various situations: GetNews (int? ID, string Name) / / query the qualified data from the main table, GetNewsDetail (int? ID, string Name) / / query the data that meets the criteria from the master table and slave table. The first method provides the main information of the news, and the second method provides comprehensive information. These two methods can basically satisfy all scenarios without too much data redundancy. I would also like to point out that some people like to write GetNewsByID (int? ID), GetNewsByName (string Name), which is very flexible and targeted, but it is totally unnecessary.

Step 3: paging binding.

The above method is to take out all the data to GridView at once and let the control page itself, which is convenient and easy to do. But there are a few questions:

(1) large amount of data. Because it is to take out all the data that meet the conditions at one time, so the amount of data is relatively large, and these data need to be put in memory, so it will affect the performance of the system. And there will be some cards when it is loaded for the first time, giving people the impression that the system is not loading smoothly.

(2) We don't need so much data. Why would I say that? If we study the usage habits of users, we will find that in most cases, users do not browse the data page by page, and users often focus on the first few items on the first few pages. Therefore, the extracted data is often not viewed by the user.

So here, it is more appropriate to use paging queries. Only one page of data is queried from the database at a time, so that the system load is small, the page load is smooth, and can fully meet the requirements of users. Some people will ask that you will not increase the number of IO in the database. What I want to say is that getting a lot of redundant data at once and bearing the lasting impact of redundancy has an undeniable advantage over these much smaller visits than the theoretically increased number of IO (users do not view page by page, that is, they do not produce so many paging queries).

At this point, I believe you have a deeper understanding of "how to achieve the design of master-slave tables in the database". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.