Code for Database design: Specification for object design usage

Object Design Usage Specification

1. Commonly used data types

Description: The following is a commonly used data type introduction, table design according to the actual application selection.

(1) Number (P,S): The exact numeric type of the fixed precision, which is used by integers or fixed-point numbers.

(2) Number: An exact numeric type with an indeterminate precision, with no determination of numeric precision, which is used by primary keys or floating-point numbers.

(3) Date: Select Date instead of timestamp type only when accurate to seconds is required.

(4) TIMESTAMP: Extended Date type, used only when higher time precision is required.

(5) VARCHAR2: Variable length string, up to 4,000 bytes long.

(6) Char: fixed-length string with only char (1) allowed.

(7) CLOB: When used for more than 4000 bytes, the CLOB field must be split into a separate table, with the primary key associated with the primary table.

The Clob field is not recommended for use, but is replaced with multiple VARCHAR2 (4000) because the space is not reused when the Clob field is updated.

(8) Blob,raw,long: no use allowed.

In MySQL, you can use only the data types mentioned below, and all tables and fields must add comments that clearly indicate their meaning. The description of each state value must be explicitly listed in the comment for the Status Class field.

(1) Decimal (M,D): This type is used when a fixed-point decimal is represented. Fixed-point numbers are stored as strings within MySQL, more precise than floating-point numbers, and are suitable for data with high precision such as currency.

(2) INT series: All integer type fields use INT (TINYINT, SMALLINT, mediumint, int, BIGINT), select the appropriate subtype based on the size of data stored, and all INT types do not use length limits.

(3) Char: You can use the char (1) type if and only if the field stores a single character. Fields with more than one character, using variable length types.

(4) VARCHAR: All variable-length fields use the VARCHAR type, and for a limited class of fields (such as gender, status, and so on), use the VARCHAR type to store strings that clearly show their meaning. When the varchar field is longer than 4000, it must be separated from the original table, and the primary key of the primary table is stored in the new table.

(5) Text series: Use the Text series type (text, Mediumtext, longtext) only if the number of bytes that you want to store may exceed 20000. and split with the original table, the primary key to form a new table store. Because all MySQL databases use the UTF8 character set, varchar can store only 64K of data.

(6) Date: The date type is used only for fields that are accurate to days. Operations that are accurate to the current period of "days" are implemented using the Curdate () function.

(7) DateTime: Use datetime types for fields that need to be accurate to time (time, minutes, seconds). Operations that take the current time exactly to "seconds" are implemented using the now () function.

2. Table Design Conventions

(1) Rule 1: The table must have a primary key.

(2) Rule 2: A field represents only one meaning.

(3) Rule 3: Always include two date fields: Ll_create (Date Created), ll_modified (date Modified), and these two fields should not contain additional business logic.

(4) In the rule 4:mysql, ll_create, ll_modified use datetime types.

(5) Rule 5: Prohibit the use of complex data types (arrays, custom types, etc.).

(6) In rule 6:mysql, the satellite table ID is consistent with the primary table ID when the subordinate table is split. New primary key fields are not allowed in the satellite table.

(7) In the rule 7:mysql, the table with outdated concept must have the expiration mechanism at the beginning of its design, and have definite expiration time. Expired data must be migrated to the history table.

(8) In rule 8:mysql, tables that are no longer in use must be notified to the DBA to be renamed and archived.

(9) In the rule 9:mysql, if there are any fields that are no longer used in the online table, it is forbidden to delete them to ensure the integrity of the data.

3. Constraint Usage Conventions

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/basis/

(1) Rule 1: A primary key must be meaningless, generated by a sequence or primary key generator (in MySQL, where the primary key is generated by the primary key generator or from the growth field), and the use of a combined primary key is prohibited.

(2) Rule 2: The table has a unique constraint in addition to the primary key, and a unique constraint must be created in the database.

(3) Rule 3: The primary key field does not allow updates.

(4) Rule 4: Do not create foreign key constraints, foreign key constraints are controlled by the application.

4. Index Design Conventions

(1) Rule 1: Do not use an index of the bitmap type.

(2) Rule 2: Do not create an index of the unique type.

(3) Rule 3: Index field selection considerations:

① selects fields that appear in the WHERE clause, and that have high selectivity and filter creation indexes.

② for fields associated with other tables, it is recommended that you create an index if you use the nest loop join.

The first field of the ③ index must be in the where condition.

④ a composite index that places a high selectivity field in front of it.

(4) Rule 4: Index use considerations:

① considers DML operations (insert,delete,update) to cause index maintenance costs.

② a function or implicit type conversion on an indexed field can result in an index invalidation.

③ Query result set (cardinality) is relatively small, the index query is applicable.

The ④ index cannot be used to determine is null or is not NULL.

5. Procedures, triggers, view usage principles

(1) Rule 1: Avoid encapsulating business logic in database procedure/function, not allowing application of procedure and function in the call database, business logic should be implemented by program.

(2) Rule 2: Do not allow the use of trigger to implement business logic. With the exception of incremental data synchronization and record data changes, such as search engine incremental build, easy hundred meter synchronization and so on.

(3) Rule 3: View is not allowed to associate with a table.

(4) The rule 4:mysql prohibits any business logic from being implemented by procedure or function or trigger encapsulated in the database.

(5) The rule 5:mysql prohibit the application from using view.

(6) Rule 6:mysql, prohibit the use of business logic plan.

(7) Rule 7: Use SVN to manage these scripts.

6. Db_link Usage Specification

(1) Rule 1: Only the DBA has Dblink permission and does not allow application call Dblink.

(2) The rule 2:db_link read-only and does not allow modification of data through Db_link. Except for Data Warehouse synchronization.

7. Materialized View usage specification

(1) Rule 1 is not allowed to use multiple table joins in materialized views due to Oracle internal bugs.

Author: csdn Blog Guoyjoe