2. Database Logic Design Principles
2.1 naming rules
2.1.1 table attribute Specification
2.1.1.1 table name
The prefix is TBL _. A data table name must consist of words or abbreviations with feature meanings. It can be separated by "_", for example, tbl_pstn_detail. Table names cannot be enclosed in double quotation marks.
2.1.1.2 table partition name
The prefix is P. The partition name must contain words or strings with specific meanings.
For example, the partition p2004100101 of tbl_pstn_detail indicates that the partition stores data for 2004100101 time periods.
2.1.1.3 field name
The field name must start with a letter and use a word or abbreviation with a feature meaning. It cannot be enclosed in double quotation marks.
2.1.1.4 primary key name
The prefix is pK _. The primary key name should be a field name consisting of prefix + Table name +. If the composite primary key has many fields, it only contains the first field. You can remove the prefix from the table name.
2.1.1.5 foreign key name
The prefix is FK _. The foreign key name should be the field name consisting of the prefix, foreign key table name, primary key table name, and foreign key table. You can remove the prefix from the table name.
2.1.2 Index
4.1.2.1 Common Index
The prefix is idx _. The index name should be a field name consisting of the prefix, table name, and table name. If the composite index contains many fields, only the first field is included and the serial number is added. You can remove the prefix from the table name.
2.1.2.2 primary key index
The prefix is idx_pk _. The index name should be the primary key field name consisting of the prefix + Table name +. When creating a table, use using index to specify the primary key index attribute.
2.1.2.3 unique
The prefix is idx_uk _. The index name should be a field name consisting of the prefix, table name, and table name.
2.1.2.4 foreign key index
The prefix is idx_fk _. The index name should be a foreign key field name consisting of a prefix + Table name +.
2.1.2.5 Function Index
The prefix is idx_func _. The index name should be a feature expression character consisting of prefix + Table name +.
2.1.2.6 Indexing
The prefix is idx_clu _. The index name should be a cluster field consisting of prefix + Table name +.
2.1.3 View
The prefix is V _. Name the view by business operation.
2.1.4 Materialized View
The prefix is MV _. The materialized view is named by business operation.
2.1.5 Stored Procedure
The prefix is proc _. Naming stored procedures by business operations
2.1.6 trigger
The prefix is trig _. The trigger name should be prefix + Table name + trigger name.
2.1.7 Functions
The prefix is func _. Name a function by Business Operation
2.1.8 data packets
The prefix is PKG _. Name a data packet based on the service operation set.
2.1.9 Sequence
The prefix is seq _. Name by service attribute.
2.1.10 tablespace
2.1.10.1 public tablespace
The prefix is TBS _. The name is based on the storage features, such as tbs_parameter.
2.1.10.2 dedicated tablespace
TBS _ <Table Name> _ nn. This tablespace stores data from a specified table or Several partitions of a table.
2.1.11 Data File
<Tablespace Name> NN. DBF. Nn = 1, 2, 3, 4 ,... .
2.1.12 common variables
The prefix is VaR _. Contains characters, numbers, and date variables.
2.1.13 cursor variable
The prefix is cur _. Store the cursor record set.
2.1.14 record Variables
The prefix is rec _. Stores record-type data.
2.1.15 table type variables
The prefix is tab _. Store table data.
2.1.16 database chain
The prefix is DBL _. External links of distributed databases.
2.2 name
2.2.1 Language
English words should be used for naming, especially Chinese characters. Chinese or special characters are not allowed for naming.
An English word uses a word with a relative or similar object meaning. Select the simplest or most common word. You cannot use irrelevant words for naming.
When a word cannot express the meaning of an object, use a phrase combination. If the combination is too long, use a short word or an abbreviation. The abbreviation must basically express the meaning of the original word.
When an object with duplicate names is of different types, the type prefix or suffix is added to indicate the difference.
2.2.2 case sensitivity
All names are capitalized to facilitate migration of different databases and avoid Program Calling problems.
2.2.3 word Separation
Each name can be separated by an underscore.
2.2.4 Reserved Words
SQL reserved words cannot be used for naming.
2.2.5 Name Length
The table name, field name, and view name must be within 20 characters (including the prefix ).
2.2.6 field name
The same field name can only represent one meaning in a database. For example, telephone represents the meaning of "phone number" in a table, and it cannot represent the meaning of "mobile phone number" in another table.
Different tables use the same name and field type for fields with the same content.
2.3 Data Type
2.3.1 character type
Char is used for fixed-length strings, and varchar is used for unfixed-length strings. Avoid using the char type when the length is not fixed. If the preceding conditions occur during data migration, the trim () function must be used to intercept the space after the string.
2.3.2 digital
Number type is recommended for numeric fields.
2.3.3 Date and Time
2.3.3.1 system time
The date type of the database is preferred for the system time generated by the database, such as date.
2.3.3.2 external time
The date and time types generated by data import or external applications are varchar, and the data format is yyyymmddhh24miss.
2.3.3.3 large fields
Avoid using large fields (blob, clob, long, text, image, etc.) unless otherwise required ).
2.3.3.4 unique key
Use series sequence to generate unique numeric values.
2.4 Design
2.4.1 paradigm
If there is no reason for performance, the relational database theory should be used to reach a high paradigm to avoid data redundancy. However, if there are no special requirements on data volume and performance, considering the convenience of implementation, appropriate data redundancy can be achieved, but it basically needs to reach 3nf. if not, avoid storing Multiple labels in a field. For example, 11101 indicates a value of five signs. This is often a place that increases complexity and reduces performance.
2.4.2 Table Design
2.4.2.1 logical Section Design Principles
2.4.2.1.1 tablespace
When creating a table, you must specify the tablespace in which it is located. Do not use the default tablespace to avoid performance problems caused by table creation in the system tablespace. For tables with busy transactions, they must be stored in the dedicated tablespace of the table.
2.4.2.1.2 pctused
By default, pctused causes the database's physical space utilization to be very low by about 40%. For tables with few updates or with no update resulting in an increase of rows, pctused can be set to between 60 and 85; for tables that can cause row enlargement by update, the update value is set to between 40 and 70.
2.4.2.1.3 initrans
For tables that require parallel query or that need parallel processing in the RAC database, set initrans to a multiple of 2. Otherwise, this 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 initial size should be as close as possible to the 64 K, 128 K ,... , 1 m, 2 m, 4 m, 8 m, 16 m ,..., And so on. For example, if the data segment size of a table or partition is 28 m, initial takes 32 m.
2.4.2.1.4.2 next
The extended table or partition size is rounded as described above. When the data segment of a table or partition cannot be round by initial close value, the size of the Data Segment can be round by initial + next. In this case, minextents = 2 must be set. For example, if the data segment size of a table or partition is 150 m, initial = 128 M; next = 32 m, minextents = 2.
2.4.2.1.4.3 minextents
This parameter indicates the initial number of extents during table creation. Generally, 1-2 is used.
2.4.2.1.4.4 pctincrease
It indicates the growth rate of each extended extents. Setting pctincrease = 0 can achieve better storage performance.
2.4.2.2 special table design principles
2.4.2.2.1 partitioned table
For tables with large data volumes, partitions are performed based on the table data attributes to achieve better performance. If the table increases by some fields, range partitions are performed based on the field value range. If the table is distributed by several key values of a field, list partitions are used. For static tables, hash partitions or list partitions are used. In range partitions, if data is evenly distributed by a keyword segment, the composite Partitioning Method of subpartitions is used.
2.4.2.2.2 clustering table
If the relationship between several static tables is close, you can use the clustered tables method.
2.4.2.3 Integrity Design Principles
2.4.2.3.1 primary key constraints
The parent table of the associated table must have a primary key. The primary key field or combination field must meet the non-null attribute and uniqueness requirements. For a parent table with a large data volume, you must specify an index segment.
2.4.2.3.2 foreign key Association
For fields associated with two tables, the primary key and foreign key should be created respectively. Whether a foreign key is set up depends on data integrity requirements. To improve performance, external indexes are required for the benchmark with a large amount of data. For foreign keys that require cascading deletion, you must specify on Delete cascade.
2.4.2.3.3 Null Value
Whether the field can be null should be explicitly specified in the SQL table creation script and the default value should not be used. Because the null value is involved in any operation, the result is null. Therefore, the nvl () function must be used in the application to convert a field that may be null or a variable to a non-null default value. For example, nvl (sale, 0 ).
2.4.2.3.4 check conditions
Check rules must be specified for fields with check constraints.
2.4.2.3.5 trigger
A trigger is a special stored procedure that is triggered by DML operations on a data table. It is created to ensure that data integrity and consistency are not damaged, so as to implement complete data constraints.
When selecting the before or after transaction attribute of a trigger, the transaction attribute of the table operation must be consistent with the application transaction attribute to avoid deadlock. Avoid using triggers in large import tables.
2.4.2.4 notes
Tables, fields, and so on should have Chinese name comments and the content to be explained.
2.4.3 index design
You can create an index for fields that need to be used as query conditions in a query. The final result is to determine whether to create an index based on the performance requirements. For composite indexes, the order of index fields is critical, and the fields with high query frequency are placed at the top of the index combination. In a partition table, use the local partition index as much as possible to facilitate partition maintenance.
Except for local indexes, you must specify the tablespace and storage attributes of the specified index segment when creating the index segment. For details, refer to 4.4.2.1.
2.4.4 View Design
A view is a virtual database table. The following principles must be followed during use:
Query some data items from one or more database tables;
Complex search or word query is implemented through views to simplify queries;
To improve data security, you only need to display the data information to those with limited permissions;
If a view is nested in a view, the level of series cannot exceed 3;
Because the view can only have fixed conditions or no conditions, it is not recommended to use the view for database tables with large data volumes or increasing over time. You can use the materialized view instead.
In addition to special requirements, avoid views similar to select * from [tablename] without search conditions;
Avoid SQL statements that sort data.
2.4.5 Package Design
The stored procedure, function, and external cursor must be implemented in the specified package. The creation of stored procedures and functions is similar to programming procedures in other languages and is suitable for modular design. When a specific algorithm changes, you only need to modify the stored procedure, you do not need to modify source programs in other languages. When data is frequently exchanged with the database, the stored procedure can improve the running speed. Because only authorized users can execute the stored procedure, the stored procedure is conducive to improving the system security.
Stored Procedures and functions must retrieve database table records or other database objects, or even modify Database Information (execute insert, delete, update, drop, create, and other operations. If a function does not need to deal with a database, it cannot be implemented through database stored procedures or functions. Avoid using DML or DDL statements in functions.
Data Packets adopt stored procedures and function overloading methods to simplify data packet design and improve code efficiency. Stored Procedures and functions must have corresponding error handling functions.
2.4.6 security design
4.4.6.1 manage default users
In the production environment, sys and system users must be strictly managed, and their default passwords must be modified. This user is prohibited from creating database application objects. Delete or lock the database test user Scott.
2.4.6.2 database-level user permission Design
Different user access permissions must be designed according to application requirements. Including application system management users and common users. Different Application roles are created according to business needs.
When a user accesses another user object, the user should create a synonym object synonym for access.
2.4.6.3 roles and permissions
Determine the operation permissions of each role on database tables, such as creation, retrieval, update, and deletion. Each role has the right to complete the task. When a user is assigned a role during the application, the permissions of each user are equal to the sum of permissions of both roles.
2.4.6.4 application-level User Design
The password of an application-level user account cannot be the same as that of a database, preventing users from directly operating the database. You can only log on to the application software with an account and access the database through the application software, without any other means to operate the database.
2.4.6.5 User Password Management
The password of the user account must be encrypted to ensure that no plaintext of the password is displayed for queries anywhere.
2.5 SQL writing
2.5.1 character type data
Single quotation marks should be used for character type data in SQL. Special numbers must be enclosed in single quotes. Otherwise, internal conversion may cause performance problems or index failure. Use functions such as trim () and lower () to format matching conditions.
2.5.2 complex SQL
For very complex SQL statements (especially those with multi-layer nesting, clauses, or related queries), you should first consider whether the design is improper. For some complex SQL statements, you can consider program implementation.
User_tab_comments Data Dictionary
Comment on can be annotated
2.5.3 Efficiency
2.5.3.1 avoid in Clause
When an in or not in clause is used, especially when there are multiple values in the clause and there is a large amount of data in the data table, the speed will decrease significantly. You can use connection query or external connection query to improve performance.
Char queries are higher than varchar queries.
Char is more efficient than varchar in queries and index creation. Of course, varchar is better in storage than char.
2.5.3.2 avoid nested select clauses
This is actually a special case of the in clause.
2.5.3.3 avoid using the select * Statement
If it is not necessary to retrieve all data, do not use * instead. A field list should be provided. Note: Select count (*) is not included (*).
2.5.3.4 avoid unnecessary sorting
Unnecessary data sorting greatly reduces system performance.
2.5.4 robustness
2.5.4.1 insert statement
When using the insert statement, you must provide a list of fields to insert values, so that even if you change the table structure and add fields, the operation of the existing system will not be affected.
2.5.4.2 differences between count (*), count (*), and count (distinct ID)
Select count (*) from testtab
Obtain the number of records in the testtab table.
Select count (ID) from testtab
Obtain the number of non-empty records in the testtab Id field of the table.
Select count (distinct ID) from testtab
The number of records with different values in the testtab Id field is obtained.
2.5.4.3 not null is a field type Constraint
This constraint function becomes invalid when no syntax is enabled later. You can modify the field type.
Alter table Modify Field name type not null
Alter table Modify Field name Type
2.5.4.4 the problem that the foreign key value can be null
If the foreign key column does not explicitly state that it is not null, a null record can be inserted (and null is not included in the record of the External table). If there is no idea that a null record can be inserted, add the not null constraint to the foreign key field.
2.5.4.5 sequence jump
The sequence rolls back and the system crashes (the value in the cache will be considered as used). Multi-Table references will cause it to skip, so it cannot be used as the continuous serial number utl_row.cast_to_row.
2.5.4.6 precautions for using ordey by in unicn \ intersect \ minus
The preceding statement is used to connect to a table. The table and table have the same field order type but the field title names can be different. If the field name is followed by ordey, the names of fields in all tables must be the same; otherwise, the sequence number of the field must be used.
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 judgment when using select statements or the update and delete statements that are highly destructive. Do not cause major data loss during running. If you are not sure, use the SELECT statement with the same conditions to check whether the conditions are correct.
2.5.6 integrity
For a table with dependency, for example, a table with a primary or foreign key relationship, you must cascade the corresponding data of its sub-Tables When deleting the parent table, or transfer the data according to certain business rules. In 9i, the fields in the table are reduced and changed to the type. If the fields are empty or the table is empty, the length of varchar and char can be changed without changing. The field names and table names can be used to alter table set unused (column) set to unavailable, note that no command is set to available again