1. Database naming conventions

  1. Use the word plus the underscore ‘_’
  2. Concise and clear naming (no longer than 30 characters)
  3. For example: user,stat,log, wifi_user, wifi_stat,wifi_log prefix database
  4. Unless a backup database can add natural numbers from 0 to 9

2. Naming conventions for database table names

  1. Use 26 case sensitive English letters and the natural numbers from 0 to 9 (often not required) plus the underscore ‘_’
  2. The naming is concise and unambiguous, with multiple words separated by an underscore ‘_’.

3. Naming conventions for database table fields

  1. Words + underline
  2. Example: value for user_login and user_id
  3. Each table must have a self-increment primary key, add_time(default system time)
  4. Table to table associated field names should be as identical as possible

4. Database table field type specification

Store data for a field with as little storage space as possible

Varchar (255) int varchar(10) int varchar(255)

The IP address must be an INT

Char is best used for fixed-length types, such as postcodes

Tinyint, int smallInt, tinyInt smallint

Preferably a default value for each field, preferably not Null

5. Database table index specification

For example, in the user_login table, the index of user_name should be unique index of user_name_index

Create a primary key index for each table

Create a reasonable index for each table

Be careful with compound indexes

6. Database paradigm

  1. First Normal Form (1NF) : Field values are atomic and cannot be divisible (all relational database systems meet first normal Form)

    • For example: the name field, in which the last name and the first name are a whole, to distinguish between the last name and the first name that must be set up two fields

  2. Second normal Form (2NF) : A table must have primary keys, that is, each row of data can be uniquely distinguished

    • Note: first normal form must be satisfied

  3. Third normal Form: a table cannot contain information about non-key fields in other related tables, that is, redundant fields cannot be used in table data

    • Note: second normal form must be satisfied first
    • It is generally not necessary to fully follow the third normal form, as excessive engineering can lead to excessive refinement of the project and affect the overall performance of the system
    • Such as: Teachers and students have accounts, passwords, etc., if the teacher and the student account redundancy in the information in the table, and student account information on students in the table, there is no impact the whole store, but it saved a lot of join operation efficiency, improved performance, (and split into several tables will not save memory, also can only take up more disk space, Or memory space) blood lessons ！！！！