Data Warehouse Database Modeling: About business primary key and logic primary key BY S00N (Jasea@sina.com) About this problem online already have a lot of discussion, now in combination of these discussions in their own modeling and data warehouse work Experience gives the following analysis and payment recommendations for all-to-see reference: First, business things, everything is the weakest, and it is most likely to be affected by customers, and it is most caused. For example, if there is a system's mesh, other many tables are foreign key. When the ID card is raised from 15 to 18, the entire number of systems will be a very difficult job. A system is far more costly greater than the cost of development, so we must fully consider the needs of customer business changes. Users will not change today, and tomorrow may change, even if users have confirmed the demand. These are unpredictable. Second, the business master key is inconvenient when there is an update when the owner is related (so you have to check from the table, and then deal with the main table). Third, the business main key is a composite type, and the crud operation is inconvenient (for example, if you want to position a record, you must pass each field of the composite, using the business primary key, based on the quality of source data, often there is business master key repetition, for The source data is controllable and the data amount is small, and the service master key is easily controlled, and for some highly coupled systems, the consequences are unbearable. Five, the redundant field in the table of data warehouses is not very small but A large number of increased logic primary keys are not redundant roots.
The basic principles of recommendation (specific cases may be specifically analyzed): 1. For business data, it is best to use logic primary keys; Second, there are multiple fields (> 3?) For business compound primary keys (> 3?), Need to use logic primary keys; Basic data, based on multifaceted consideration, business primary key can be employed. This type of table is initialized after the data will not change frequently. 4. After canceling the business main key, establish Index in the relevant business field that the query often uses, can improve the query efficiency; 5. Use the logical primary key, the business data of the table is uniquely checked by the program, so that the business data repeats this class. Dirty data is controlled in the range of services; 6. Repeat of business data can also be obtained by analyzing results data; 7. The logical primary key of business data uses NUMERIC self-growth type, when migrating data, cancel the target table Growth, after the data migration is complete, reconstruct the logical primary key. By S00n (Jasea@sina.com)
2004-10-28
