Database key key design principle
Perhaps everyone has designed a database, which is also defined for the primary key. Today I want to explain, how to design a primary key correctly, in some of the previous information, there is no principle of primary key design. I summarize A look:
1. Do you want to use GUID as a primary key?
The main key with the GUID has its advantages and deficiencies. The advantage is that the GUID has uniqueness. In any case, the world's unique value can be produced. This is the biggest advantage of GUID, which is also convenient for data imports, such as request from another system. The data is imported, then don't worry, it will cause the primary key conflict. Insufficient is too complicated. It is not easy to memorize, because sometimes, it is inevitable that we will use the recorded way. And the data is too long, affecting the database Efficiency. The generation of GUID is not generated in a certain order. For a database, the database is physically sorted by the primary key, if a record is inserted in the front of the record, it may cause the number of data bars behind the data to move back. This will result in data Insert efficiency. Therefore, the adoption of GUID should be cautious.
2. Do you want to use an automated incremental way?
For the primary keys mentioned before, it is required to use automatic incremental way. This way is very unbearable. It may not be necessary to create a main key value when it is convenient to insert records. So this will be convenient, Actually, it is not possible to better than this so-called "convenient". First: data import is inconvenient, often import data from another system, automatically incremented primary keys, will not allow original tables The ID is imported. This will cause the primary key to lose. Second: For a table with a primary key like an order, if the "Master Table" primary key of the order is automatically generated. So when you save a order It will require transaction to the main gemmation and the schedule, at this time, first generate a order, then remove the primary key generated by this order, then use this as a foreign key to make details. During this process, it will become complicated and not feasible. How to deal with the transaction. If the order main file table is inserted into the record, if you have an error, the main unit record will be deleted. Hey. After inserting success, Take out the maximum value. This will be a serious waste. If you record, you will affect the speed, and there will be parallel to insert. Result in the acquisition records may not be correct. So under the above serious problems, please do not use automatic increment the way.
3. Do you want to use an INT type as a primary key
In used, everyone used INT types, all of them were caused by numbers. In fact, we also understand. It is not only a digital thing that is digital. For example, a phone number, etc. Therefore, for primary keys, the advantages of INT type may be fast, inserted, and the query may be faster than in other ways. But this fast effect may not have more obvious, such as VARCHAR (15) as an example, the physical primary key sorted data, automatically sorted by the primary key. Therefore, even if it is a characteristic data, it will be inserted into the corresponding physical location during insertion, that is, it may affect some speed during insertion. However, in future queries, the speed impact will not be too obvious. And I want to say, don't use int type as the primary key, not to say, no data inside. I still recommend that you store numbers in the primary key, such sorting is more fast than the sorting of the mixture, and the character type is prepared for future data import. One day, it will require import data from other tables. When you can add a particular letter to the primary key of importing data to avoid conflicts with the original key. For example, add a "n" letter before the primary key of importing data. This doesn't worry, and the primary key that requires imported data tables is digital or characters.
4. Whether the number is used to define the primary key
This problem is that the old man is often talking. The primary key design has a principle that the primary key should not have any practical significance. This is actually very important. Some people think that the number itself is the only one, it can be used as a primary key, but it may cause trouble to later. Because of the actual field, there is still a modified possibility. And the biggest taboo for primary keys is to modify the primary key, which may result in a very serious unrecognizable consequence. For example, student numbers, usually do never modify, but modifications may still exist. Also one, the surface is unique, but it should actually be repeated. I will give an example, order, the order number should be the only one. Yes it is. However, there is such a situation, and a original order is for a reason, requiring orders to be invalid. The status of the order is identified as "cancel". The same number of orders is then allowed again. therefore. In such cases, although the effective order number is only one, the number is allowed to be repeated in the database angle. So, no matter what is, it is recommended that everyone builds a primary key without any meaning, such as ID.
So, summarize, I will design the primary key, which will use characters. Do not use automatic incrementation, when new records, the system generates the main key value. Generally, all figures are stored, as for the generating rules of primary key values, rule definitions can be performed as needed. If there is no special request, just to keep the only, you can define a field to store a value. When generating, automatically add one. Then save back. This is also fast than looking for maximum values from a table.
All right. I think so much for a while. There is a suggestion discussion. .
Listen to
http://tintown.blogbus.com/
