A database logic design principle __ Database

2 Logical design principles of database

2.1 Naming conventions

2.1.1 Table Property Specification

2.1.1.1 Table Name

Prefix is tbl_. The data table name must consist of a word or abbreviation with a characteristic meaning, and the middle can be divided by "_", for example: Tbl_pstn_detail. Table names cannot be contained in double quotes.

2.1.1.2 Table partition Name

The prefix is p. The partition name must have a word or string that has a specific meaning.

For example, a tbl_pstn_detail partition p2004100101 indicates that the partition stores data for 2004100101 time periods.

2.1.1.3 Field names

Field names must begin with a letter, with a word or abbreviation that has a characteristic meaning, and cannot be enclosed in double quotes.

2.1.1.4 PRIMARY Key Name

Prefix is pk_. The primary key name should be the prefix + table name + The field name of the composition. If a composite primary key has more constituent fields, only the first field is included. The table name can remove the prefix.

2.1.1.5 FOREIGN Key Name

Prefix is fk_. The foreign key name should be the prefix + foreign key table name + PRIMARY key table name + foreign key table composition field name. The table name can remove the prefix.

2.1.2 Index

4.1.2.1 General Index

Prefix is idx_. The index name should be the prefix + table name + The field name composed of. If the composite index has more constituent fields, only the first field is included and the ordinal number is added. The table name can remove the prefix.

2.1.2.2 PRIMARY Key Index

Prefix is idx_pk_. The index name should be the prefix + table name + The primary key field name, which specifies the primary key index property when the table is created with the using index.

2.1.2.3 only So

Prefix is idx_uk_. The index name should be the prefix + table name + The field name composed of.

2.1.2.4 FOREIGN Key Index

Prefix is idx_fk_. The index name should be the prefix + table name + The foreign key field name composed of.

2.1.2.5 Function Index

Prefix is idx_func_. The index name should be the prefix + table name + The feature expression character that is composed.

2.1.2.6 Nest Index

Prefix is idx_clu_. The index name should be the prefix + table name + cluster field.

2.1.3 View

Prefix is v_. Name the view by business action.

2.1.4 Materialized View

Prefix is mv_. Name the materialized view by business action.

2.1.5 Stored Procedures

Prefix is proc_. Naming stored procedures by Business action

2.1.6 triggers

Prefix is trig_. The trigger name should be a prefix + table name + trigger name.

2.1.7 function

Prefix is func_. Name functions by Business action

2.1.8 Data Packets

Prefix is pkg_. Name the packet by Business Action collection.

2.1.9 sequence

Prefix is seq_. Named by business attribute.

2.1.10 table Space

2.1.10.1 Common Table Space

Prefix is tbs_. Named after a stored attribute, for example: Tbs_parameter.

2.1.10.2 Private Table Space

tbs_< table name >_nn. The table space specifically stores a specified table, or data for several partitions of a table

2.1.11 Data files

< table space name &GT;NN.DBF. nn =1,2,3,4, ... Wait

2.1.12 Ordinary variable

Prefix is var_. Holds character, number, date type variables.

2.1.13 cursor Variable

Prefix is cur_. Holds the cursor recordset.

2.1.14 Record type variable

Prefix is rec_. Store record data.

2.1.15 Table type variable

Prefix is tab_. The table type data is stored.

2.1.16 Database Chain

Prefix is dbl_. Represents a distributed Database external link relationship.

2.2 Name

2.2.1 Language

Name should use English words, avoid using pinyin, especially should not use pinyin shorthand. Naming does not allow the use of Chinese or special characters.

English words use words that are relative or similar in meaning to the object itself. Select the simplest or most common word. Can't use irrelevant words to name

When a word can not express the meaning of the object, with the combination of phrases, if the combination is too long, using a simple or abbreviated, the abbreviation should basically be able to express the meaning of the original word.

When the name of the object names is different, the type prefix or suffix is the difference.

2.2.2 Case

Names are capitalized to facilitate different database porting and to avoid program invocation problems.

2.2.3 Word separation

The names of the words can be delimited by using an underscore.

2.2.4 reserved word

Naming does not allow the use of SQL reserved words.

2.2.5 named length

The table name, field name, and view name length should be limited to 20 characters characters (with prefix).

2.2.6 Field names

The same field name can only represent one meaning in a database. For example, telephone in a table for "phone number" meaning, in another table can not represent "mobile phone number" meaning.

Fields with the same content for different tables should have the same name, field type definition.

2.3 Data types

2.3.1 Character type

Fixed-length string types use char, and length-invariant string types are varchar. Avoid using the char type in the case of a fixed length. If the above situation occurs in data migration, you must use the trim () function to truncate the space after the string.

2.3.2 Digital Type

Numeric fields use the number type as much as possible.

2.3.3 Date and time

2.3.3.1 system Time

The date type of the system time preferred database that is generated by the database, such as date.

2.3.3.2 external Time

Date-time types produced by data import or external applications are of type varchar, with the data format: Yyyymmddhh24miss.

2.3.3.3 Large characters

Avoid using large characters (blob,clob,long,text,image, etc.) without special needs.

2.3.3.4 Unique Key

For numeric unique key values, use a series of sequence as much as possible.

2.4 Design

2.4.1 Paradigm

If there is no performance necessary reason, should use the relational database theory, achieves the high paradigm, avoids the data redundancy, but if in the data quantity and the performance does not have the special request, considers the realization convenience to have the appropriate data redundancy, But basically to reach 3NF. If necessary, avoid the practice of storing multiple flags in a field. A value of 11101 representing 5 flags. This is often the place to increase complexity and reduce performance.

2.4.2 table Design

2.4.2.1 Logical Segment Design principles

2.4.2.1.1 tablespace

At the time each table is created, you must specify the table space in which it resides, and do not use the default table space to prevent the table from being built on the system table space causing performance problems. Data tables that are busy with transactions must be stored in the table's private table space.

2.4.2.1.2 pctused

The default pctused results in a very low utilization of database physical space by around 40%, pctused can be set between 60-85 for tables with less than update or update does not cause rows to grow, and for tables where update can cause row enlargement, update is set to 40-70

2.4.2.1.3 Initrans

For tables that require parallel queries or need to be processed in parallel in a RAC database, the Initrans is set to a multiple of 2, otherwise the value is not set.

2.4.2.1.4 Storage

2.4.2.1.4.1 Initial

Minimize the number of extents in the table data segment, the size of the initial as close to the size of the data segment 64k,128k, ..., 1m,2m,4m,8m,16m, ..., and so on, in multiples of 2 for rounding. For example, the size of the table or partition data segment is 28M, then the initial takes 32M.

2.4.2.1.4.2 Next

The size of the table or partition extension extents, rounded as described above. When a table or partitioned data segment cannot be rounded by Initial close to the value, its size can be rounded by initial+next. At this point, you must set the minextents=2. For example: The table or partition data segment size is 150M, then initial=128m next=32m,minextents=2.

2.4.2.1.4.3 minextents

This parameter represents the initial number of extents when the table was created, typically 1-2.

2.4.2.1.4.4 Pctincrease

Represents the growth rate of each extended extents, and setting pctincrease=0 can achieve better storage performance.

2.4.2.2 Special table Design principles

2.4.2.2.1 partition Table

For tables with large data volumes, partitions are based on the properties of the table data for better performance. If the table grows by some fields, a range partition by field value range, a list partition if the table is distributed by several key values for a field, or a hash partition or a list partition for a static table, or, in a range partition, if the data is evenly distributed by a key field. The composite partitioning method of the sub partition is adopted.

2.4.2.2.2 Cluster table

If a few static tables are closely related, the method of clustering table can be adopted.

2.4.2.3 Integrity Design Principles

2.4.2.3.1 PRIMARY KEY constraint

The parent table of the associated table requires a primary key, and the primary or combined field must meet the Non-null attributes and uniqueness requirements. For a parent table with a large amount of data, the specified index segment is required.

2.4.2.3.2 FOREIGN Key Association

For fields associated with two tables, you should generally create primary and foreign keys separately. Whether a foreign key is actually established is determined according to the requirements for data integrity. In order to improve the performance, the data volume is larger than the standard requirements of external health index. The ON DELETE cascade must be specified for a foreign key that requires cascading delete attributes.

2.4.2.3.3 Null value

For a field to be null, you should explicitly indicate in the SQL table script that the default should not be used. The result is NULL because the null value is participating in any operation. Therefore, you must use the NVL () function in your application to convert a potentially null-worthy field or variable to a non-null default value. For example: NVL (sale,0).

2.4.2.3.4 Check Condition

A check rule is required for a field with an inspection constraint.

2.4.2.3.5 triggers

A trigger is a special stored procedure that triggers execution through the DML operation of a data table, and is created to ensure that the integrity and consistency of the data are not compromised and that the data is fully constrained.

The transaction properties for a table operation must be consistent with the application transaction properties to avoid deadlocks when the before or after transaction attributes are selected. In a large import table, avoid using triggers as much as possible.

2.4.2.4 Notes

tables, fields, and so on should have a Chinese name annotation, as well as the content to be described.

2.4.3 Index Design

Consider indexing a field in a query that needs to be a query condition. Ultimately, the index is determined based on the needs of the performance. For composite indexes, the order of the indexed fields is critical, and the fields with higher query frequency are ranked at the top of the index combination. In the partitioned table, the local partitioning index is used as far as possible to facilitate partition maintenance.

Unless the local index is partitioned, the tablespace, storage properties of the specified index segment must be specified when the index segment is created, referring to the 4.4.2.1 content.

2.4.4 View Design

A view is a virtual database table, followed by the following guidelines when used:

Query some data items from one or more library tables;

To simplify the query, the complex search or Word query is realized through the view;

Improve the security of the data, only to see the data information to show to the people with limited authority;

If the view is nested in the view, the series must not exceed 3 levels;

Because the view can only be fixed or no conditions, so for large data or over time, the gradual increase in the library table, should not use the view, you can use a materialized view instead.

Avoid views similar to select * FROM [TableName] without retrieval criteria, except for special needs;

In the view, try to avoid the SQL statement in which data is sorted.

2.4.5 Package Design

Stored procedures, functions, and external cursors must be implemented in the specified packet object package. The establishment of stored procedure and function is like the programming process of other language forms, it is suitable to adopt modular design method. When the specific algorithm changes, only need to modify the stored procedures, you do not need to modify the source program of other languages. When and database Exchange data frequently is through stored procedures can improve the speed of operation, because only authorized users can execute stored procedures, so stored procedures are conducive to improve the security of the system.

Stored procedures, functions must retrieve database table records or other database objects, and even modify (execute Insert, Delete, Update, Drop, create, etc.) database information. If a feature does not need to deal with the database, it must not be implemented by means of a database stored procedure or function. Avoid using DML or DDL statements in functions.

In the data package, the method of storing procedure and function overload is used to simplify the design of data package and improve the efficiency of code. Stored procedures, functions must have the appropriate error handling function.

2.4.6 Security Design

4.4.6.1 Manage default Users

In a production environment, the SYS and system users must be strictly managed, and their default passwords must be modified to prevent database application objects from being established with that user. Delete or lock database test user Scott.

2.4.6.2 database-level user rights design

Different user access rights must be designed according to the application requirements. Including application system management users, ordinary users and so on, according to the business needs to establish different application roles.

When a user accesses another user object, it should be accessed by creating a synonym object, synonym.

2.4.6.3 Roles and Permissions

Determine the operational permissions for each role on the database table, such as Create, retrieve, update, delete, and so on. Each role has just the right to complete the task, no more, no less. When the user is assigned a role at the time of application, each user's permissions are equal to the sum of the privileges of his or her role.

2.4.6.4 application-level user design

The user account password at the application level cannot be the same as the database, preventing users from manipulating the database directly. Users can only login to the application software, through the application software access to the database, and there is no other way to operate the database.

2.4.6.5 User Password Management

The password for the user account must be encrypted to ensure that the password is not plaintext in any queries anywhere.

2.5 SQL Authoring

2.5.1 Character type data

Character type data in SQL should use single quotes uniformly. In particular, a string of pure digits must be enclosed in single quotes, or it can cause an internal conversion that may cause performance problems or index invalidation issues. Use Trim (), lower () and other functions to format matching conditions.

2.5.2 Complex SQL

For very complex SQL (especially with multiple layers of nesting, tape sentences, or related queries), it is important to consider whether the design is improper. For some complex SQL you might consider using a program implementation.

User_tab_comments Data Dictionary

Comment on can add annotations

High efficiency of 2.5.3

2.5.3.1 avoid in clause

When using in or not in clauses, especially when there is more than one value in the clause, and the query data table data is more, the speed will drop significantly. You can use either a connection query or an outer join query to improve performance.

Char is higher than varchar query

Char is more efficient than varchar when querying and indexing, of course varchar is better than char on storage

2.5.3.2 to avoid nested SELECT clauses

This is actually a special case of the IN clause.

2.5.3.3 Avoid using SELECT * Statements

If it is not necessary to remove all the data, do not replace it with *, give the field list, note: SELECT COUNT (*) is not included.

2.5.3.4 Avoid unnecessary sorting

The unnecessary data ordering greatly reduces the system performance.

Robustness of 2.5.4

2.5.4.1 INSERT statement

Use the INSERT statement to give the list of fields to which you want to insert a value, so that even changing the table structure plus fields does not affect the operation of the existing system.

The difference between 2.5.4.2 count (*), COUNT (*), COUNT (distinct ID)

Select Count (*) from Testtab

Get the number of records testtab the table

Select COUNT (id) from Testtab

Get table testtab ID field non-empty record number

Select COUNT (Distinct ID) from Testtab

Get Table Testtab ID field value not the same number of records

2.5.4.3 NOT NULL is a constraint on the nature of a field type

This constraint function fails at a later stage without syntax and can be modified using the Modify field type

ALTER TABLE Modify field name type NOT NULL

ALTER TABLE modify field name type

Problems with NULL 2.5.4.4 foreign key values

A foreign key column that does not explicitly specify NOT NULL can be inserted into a null record (and NULL is not in the records of the external table), such as the idea of no nullable records, to the foreign key field plus the NOT NULL constraint.

The problem of 2.5.4.5 sequence sequence jump number

Sequence because of rollback, the system crashes (using the values in the cache will be considered to be used), the multiple table references will make it jump number, so it cannot be used for sequential serial number Utl_row.cast_to_row

Considerations for 2.5.4.6 Unicn/intersect/minus using Ordey by

The preceding statement is a table operation, and the table's field order is the same type but the field header name can be different, using Ordey by when the field name, all the table's field header name is required, otherwise the ordinal number of the field

Select Id,name,year from User1
Union
Select No,name,to_number (null) year from User2
ORDER BY 1,name,year

2.5.5 Security

2.5.5.1 where condition

Check the integrity of the Where condition when you use SELECT, or when you use the most destructive update and DELETE statements. Do not show significant loss of data at run time. If you are unsure, it is a good idea to use the SELECT statement with the same conditions to fruit the result set to verify that the condition is correct.

2.5.6 Integrity

A dependent table, such as a primary foreign key relational table, must cascade the corresponding data of its child tables when the parent table is deleted, or transfer the data according to a business rule. The fields in the 9I table are reduced and variable, fields are empty or table empty, varchar and char lengths are invariant, field names and table names can be fields available with ALTER table table set unused (column), note that no commands are set to be available