Part 3 - Select key and index
1. Data mining should be pre-planned
My market sector once to deal with more than 80,000 contact information, and fill in the necessary data for each customer (this is definitely not small
live). I have to identify a group of customers as a market goal. When I started with the first design table and field, I tried it.
Increase too much field in the main index to speed up the running speed of the database. Then I realize a specific group query and information mining
Neither accurate speed is not fast. The results have to rebuild in the main index and incorporate data fields. I found some indicator plan.
When the key - when I want to create a system type lookup, use the number as the main index field? I can use fax numbers
Retrieving, but it is not important to me almost like the system type. Using the latter as the primary field, database update
After re-indexing and retrieving.
- HSCOVELL
The data index in the two environmental data warels (ODS) and data warehouses (DW) is different. In the DW environment
Under, you have to consider how the sales department organizes sales activities. They are not database administrators, but they determine
Key information. The designer or database staff should analyze the database structure to determine between performance and correct output.
Best conditions.
- TEBURLEW
2. Use the primary key generated by the system
This day is the same skill 1, but I think it is necessary to repeat everyone here. If you always use the database
The system generated key as the primary key, then you actually control the index integrity of the database. In this way, the database and non-manual mechanisms are
Effectively controls access to each line of storage data.
It is also advantageous to use the system generated key as the primary key: When you have a consistent key structure, it is easy to find logical defects.
- TEBURLEW
3. Decompose fields for indexing
In order to isolate the named field and contain a field to support user-defined reports, consider breakout other fields (or even primary keys) for its group
Entries can so that users can index them. The index will speed up the execution speed of SQL and report generator scripts. for example,
I usually create a report in the case of the SQL Like expression, because the Case Number field cannot be decomposed into
Year, Serial Number, Case Type and Defendant Code and other elements. Performance will also become. If the annual and type word
The segment can be broken down into index fields, then these reports will run more.
- RDELVAL
4. Key design 4 principle
• Create foreign keys for related fields.
· All keys must be unique.
· Avoid using a compound key.
· Foreign key is always associated with unique key fields.
- Peter Ritchie
5. Don't forget the index
Indexes are one of the most efficient ways to get data from the database. 95% of database performance issues can be used to get indexing technology
solve. As a rule, I usually use the unique group index for the logical primary key, using the system key (as a stored procedure)
The unique non-group index is non-group index to any foreign key column. However, the index is like a salt, too much vegetables are 篌. you
Do you have to consider how big the database is space, how to visit, and whether these accesss are mainly used as read and write.
- TDUVALL
Most databases index automatically created primary key fields, but don't forget the index foreign key, they are often used, ratio
If you are running a query showing a record of the primary table and all the joint tables. Also, don't index the Memo / Note field, no
To index large fields (there are a lot of characters), making the index occupy too much storage space.
- GBRAYTON
6. Don't index common small tables
Don't set any keys for small data tables, if they often have this insert and delete actions. Insert and
Index maintenance of deletion operations may consume more time than scanning tables.
- kbpatel
7. Don't choose a social security number (SSN)
Never use SSN as the database of the database. In addition to privacy reasons, it is important to know that the Government is increasingly tending to use SSN as other purposes other than income, and SSN needs manual input. Never use the manual input key as the master
Key, because once you entered an error, you can do it is to delete the entire record and start from the beginning.
- TEBURLEW
When I was studying at college, I remember that SSN was also used as a student number at that time. Of course, I don't have it.
legal. And people also know that this is illegal, but they are used to it. Later, with the initiative of the identity crime
Add, my current university campus is painful from a large stall data to delete SSN.
- generalist
8. Don't use the user's key
When determining what the field is used as a table, you can be careful that the user will be edited. Usually don't
Select the user-editable field as the key. This will force you to take the following two measures:
• Implementing a restriction on the behavior of the user editing field after creating a record. If you do this, you may find your application
The order suddenly changes in business needs, and the user needs to edit those in editable fields lack sufficient flexibility. Use
Households after entering the data until the record is found to find a problem? Delete rebuild? If the record is not available
Does reconstruct users open?
· Propose some methods for detecting and correcting key conflicts. Usually, the cost of energy is getting it, but from the performance to do this.
The consideration is bigger. Also, the correction of the keys may force you to break through your data and business / user interface layers.
from.
So still override an old saying: your design should adapt to the user rather than letting users adapt to your design.
- Lamont Adams
The reason why the primary key is not updated is that the primary key implements the association between different tables in relational mode. such as,
The Customer Table has a primary key CustomerID, while the customer's order is stored in another table. ORDER table's primary key
It is a combination of ORDERNO or ORDERNO, Customerid, and date. No matter which key settings you choose, you need it.
The Order table stores Customerid to ensure that you can find its order records for users.
If you modify Customerid in the Customer table, you must find all relevant records in the Order table.
Row modification. Otherwise, some orders will not belong to any customer - the integrity of the database is finished.
If the index integrity rule is applied to the table level, it is almost impossible if you do not write a large number of code and additional deletion records.
Change a record of a record and all associated records in the database. And this process is often incorrect, so it should be avoided as much as possible.
- ljboast
9. Optional button Sometimes you can do the primary key
Remember, query data is not a machine but a person.
If you have an optional button, you may further use it for primary key. In that case, you have the ability to build powerful indexes. This
A person who can prevent the use of a database has to connect to the database to properly filter data. Database in strict control domain table
In this load, this load is more awake. If the option is really useful, it is the level of the primary key.
My opinion is that if you have an optional button, such as State_code in the country, you don't do the only key in existing can't change.
Create a subsequent key. You have to do more than creating a worthless data. For example, the following example:
SELECT Count (*)
From address, state_ref
WHERE
Address.State_Id = state_ref.state_id
And state_ref.state_code = 'TN'
My approach is this:
SELECT Count (*)
From address
WHERE
And State_code = 'TN'
If you build this form of association because of the subsequent keys of excessive use table, the operation load really needs to be considered.
- Stocker
10. Don't forget the foreign key
Most database indexes automatically created primary key fields. But don't forget the index outer key field, they are in the records in the primary table.
Each time it is related to its associated record. Also, don't index the Memo / Notes field and don't index large text fields
(Many characters) that makes your index occupies a large amount of database space. .
- GBRAYTON
