Rules related to naming database tables and fields, etc.

Rules that object naming should follow

1. Readability principle

Use uppercase and lowercase to format library object names for good readability (note that some DBMS systems are sensitive to table name capitalization)

2. The principle of ideographic

The name of the object should be able to describe the object it identifies

3. Long name Principle

Use as little or no abbreviations as possible for any object other than the database name

Selection Principles for field types

Column data type: Affects the cost of data storage space, affecting the query function of the database

(priority: Numeric Type--date or binary type--with the same level of character type, select a data type that takes up less space)

The above principles are mainly considered from two aspects:

1. When comparing data (query condition, join condition and sort) operation: same data, character processing is often slower than the number

2. In the database, data processing in page units, the smaller the length of the column, the benefit of performance improvement

How data types are selected at the same level:

How char and varchar are selected

1. If the length of the data to be stored in the column is approximately the same (like ID card, school number, etc.), char first, otherwise varchar

2. If the maximum data length in a column is less than 50byte, Char

(If this column is rarely used, it is space-based and reduced I/O considerations, or you can choose to use varchar)

3. It is generally not appropriate to define a char type column greater than 50byte

How to choose decimal and float

1.decimal for accurate data storage, float for non-accurate data storage

2.float Less storage space overhead

How the time type is stored

1. Advantages and disadvantages of storing with int type ( for data that is only used to store very few queries, eg: birthday date, otherwise suitable for date type storage )

Advantage: Word length is smaller than decimal

Cons: Use inconvenient, to perform function conversion

Limit: Only until 2038-1-19 11:14:07 2^32 is 2147483648

2. Time granularity required for storage

Years, days, hours, minutes, weeks

How to select the primary key (the best order growth, so that no logical data migration, the benefits of I/O performance)

Avoid using FOREIGN KEY constraints:

1. Reduce the efficiency of data import

2. Increase maintenance costs

3. However, the associated column must be indexed

Avoid using triggers:

1. reduce the efficiency of data import

2. Unexpected data anomalies may occur

3. Complicate the business logic

about reserved fields: Not very important, reserved fields are strictly forbidden

Rules related to naming database tables and fields, etc.