Database design Guidelines (2)

Part 3rd-Select keys and Indexes

1. Data mining should be planned in advance

My marketing department had to deal with over 80,000 contacts at a time and fill in the necessary data for each customer (this is definitely not a small job). I also want to identify a group of customers as a marketing target. When I was designing tables and fields from the very beginning, I tried not to add too many fields to the main index to speed up the database. Then I realized that specific group queries and information mining were neither accurate nor fast. As a result, the data fields are rebuilt and merged in the primary index. I've found that there's a key to the plan--why do I use numbers as the primary index when I want to create a system type lookup? I can retrieve it with a fax number, but it's almost as much a system type as it doesn't matter to me. Using the latter as the primary field, the database is indexed and retrieved more quickly after it is updated.

There are differences in data indexing between operational data warehouses (ODS) and Data warehousing (DW) environments. In the DW environment, you need to consider how the sales department organizes sales activities. They are not database administrators, but they determine the key information in the table. Here the designer or database worker should analyze the database structure to determine between performance and correct output

The best conditions.

2. Using system-generated primary keys

This day is similar to skill 1, but I think it is necessary to remind everyone here. If you always use the system-generated key as the primary key when designing the database, you actually control the index integrity of the database. In this way, the database and the non human mechanisms effectively control access to each row in the stored data. The advantage of using a system-generated key as a primary key is that it is easy to find a logical defect when you have a consistent key structure.

3. Decomposition fields for indexing

To isolate named fields and include fields to support user-defined reports, consider factoring other fields (even primary keys) to make them available for users to index. Indexing speeds up the execution of SQL and Report Builder scripts. For example, I usually create a report if I have to use an SQL like expression because the Case Number field cannot be decomposed into elements such as year, serial number, cases type, and defendant code. Performance can also go bad. If the year and type fields can be decomposed into indexed fields, then these reports will run much faster.

4. Key Design 4 principle

• Create a foreign key for the associated field.

• All keys must be unique.

• Avoid using composite keys.

• Foreign keys are always associated with unique key fields.

5. Don't forget the index

An index is one of the most efficient ways to get data from a database. 95% of database performance problems can be solved by indexing technology. As a rule, I usually use a unique group index on a logical primary key, a unique, nonclustered index on the system key (as a stored procedure), and a non group index on any foreign key columns. However, the index is like salt, too much food on the lll. You have to think about how large the database is, how the tables are accessed, and whether they are primarily used for reading and writing.

Most databases index automatically created primary key fields, but don't forget to index foreign keys, which are also frequently used keys, such as running a query that shows a record of the primary table and all associated tables. Also, do not index memo/note fields, and do not index large fields (with many characters), which makes the index occupy too much storage space.

6. Do not index the common small table

Do not set any keys for small data tables, even if they often have inserts and deletes. The index maintenance for these insert and delete operations may consume more time than the scan table space.

7. Do not select the Social Security Number (SSN) as a key

Never use the SSN as the key to the database. In addition to privacy reasons, it is increasingly necessary for the government to not allow the SSN to be used for purposes other than revenue related, and the SSN needs to be manually entered. Never use a manually typed key as the primary key, because once you enter the error, the only thing you can do is to delete the entire record and start from scratch.

When I was in college in the 70 's, I remembered that the SSN had been used as a school number, although it was illegal. And people know it's illegal, but they're used to it. Later, as the crime of identity theft increased, my current college campus was painfully removed from a large stall of data.

8. Do not use the user's key

When deciding what field to use as a key for a table, be sure to be careful what fields the user will edit. In general, do not select a user-editable field as the key. This will force you to take the following two measures:

• Imposes restrictions on the behavior of user-edited fields after a record is created. If you do, you may find that your application is suddenly changing in business requirements and users need to edit those non-editable fields without enough flexibility. What do they think when the user enters the data and then saves the record before discovering that the system is out of order? Delete Rebuild? If the record is not

Does rebuilding allow users to walk away?

• Some methods for detecting and correcting key conflicts are presented. Usually, it takes a bit of energy to get it done, but in terms of performance It's a lot more expensive. Also, key corrections may force you to break through the isolation between your data and the business/user interface layer.

So it's an old saying: your design needs to be adapted to the user rather than to the user to adapt to your design.

The reason that you do not make the primary key updatable is that in relational mode, the primary key implements an association between the different tables. For example, the Customer table has a primary key CustomerID, and the client's order is stored in another sheet. The primary key of the order table may be a combination of OrderNo or OrderNo, CustomerID, and dates. Regardless of which key setting you choose, you will need to store CustomerID in the order form to ensure that you can find the order records for the user who orders it. If you modify the CustomerID in the Customer table, you must find all relevant records in the order list to modify it. 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 to change the key of a record and all the associated records in the database without writing a large amount of code and attaching a deleted record. This process is often incorrectly clustered and should be avoided as much as possible.