Preliminary specifications for MySQL Database Design: V1.0 and mysqlv1.0

Preliminary specifications for MySQL Database Design: V1.0 and mysqlv1.0

 

Database Design specifications:

1. Table design specifications 1.1 Table Design

A) The table name and column name must be annotated.

B) The name should contain meaningful English words or abbreviations. All words may consist of uppercase letters separated by "_". Only English letters, numbers, and underscores are allowed, no space left. For example, USER_DETALL, the keyword TYPE or STATUS cannot be used as the field name.

C) The name length should not exceed 15 characters (avoid exceeding 20 characters). It should reflect the business scope of the dataset or business functions, such as POWER_USER (user center.

D) when the field type is enumeration or Boolean, use the CHAR (1) (or CHAR (2) type and fill in the default value, the default value of the Status field cannot be null. Generally, it is set to 0 or-1. The description of the Status field is written as comment 'group buying coupon status: 1. purchased; 2. used; 3. refund in progress; 4 refunded '.

 

E) include the date field CREATE_DATE (creation date) and UPDATE_DATE (update date) as much as possible during design. Mysql specifies a Date input method, for example, '2017-12-31 00:00:00. 0'

F) default value. The default value of the numeric type is 0, the default value of the string is '', and the default value of the date is '2017-01-01 00:00:00. 0 '.

G) the ID of the primary key field is bigint. If AUTO_INCREMENT = 6653864 is found in the create statement, remove it.

H) The default value of the date field cannot be null. It is generally set to 00:00:00. 0.

 

I) The fields to be searched, such as mobile phone fields and email fields, cannot be null. The default value is null ''. Numeric Fields cannot be null. The default value is 0.

J) the default character encoding is utf8, and the default storage engine is INNODB.

PS: Each table must have a primary key field and a date field, and the value cannot be NULL.

1.2 index design

1) A common index starts with IDX _ and connects the field name.

2) If there is a small proportion of repeated values in the value range, create an index field, for example, the CREATE_DATE field (input time); if the value range repetition rate field does not need to create an index, for example, IS_RETURN (whether it has been refunded) field.

3) You do not need to create a unique key for the primary key field or create a separate index for the primary key field.

4) the fields that are frequently queried after the WHERE condition must be indexed, for example, ORDER_SN (item number) of the ORDER_GOODS table.

5) The range field does not need to be indexed, such as the IS_DEL field in the SHOP_MALL table.

6) The index creation field must not have a null value. Otherwise, the index efficiency will be affected.

 

1.3 table structure example

Table creation statement example:

Create table 'shop _ GAY '(

'Id' BIGINT (20) not null AUTO_INCREMENT COMMENT 'shop id ',

'Shop _ name' VARCHAR (50) DEFAULT ''comment 'shop name ',

'Gal _ PERSON_MOBILE 'VARCHAR (11) default null comment 'legal person mobile telephony ',

'Score 'bigint (20) DEFAULT 0 comment' integral ',

......

'Manager _ name' VARCHAR (20) DEFAULT ''comment 'manager name ',

'Brief 'varchar (500) DEFAULT ''comment' shop introduction ',

'Has _ warehouse' CHAR (1) DEFAULT '0' comment' whether there is a WAREHOUSE, 0: No; 1: Yes ',

'Description _ FIT 'DECIMAL (3, 1) DEFAULT 0 COMMENT' DESCRIPTION is consistent -- get one DECIMAL by calculating the average value of the evaluation for all orders ',

'Background' VARCHAR (200) DEFAULT ''comment' shop title image ',

'Created _ date' timestamp default CURRENT_TIMESTAMP COMMENT 'creation time ',

'Updated _ date' datetime default '2017-12-31 00:00:00. 0' COMMENT 'Update time ',

Primary key ('id '),

KEY IDX_MOB (LEGAL_PERSON_MOBILE ),

KEY IDX_CRETIME (CREATED_DATE ),

KEY IDX_UPTIME (UPDATED_DATE)

) ENGINE = innodb default charset = utf8 COMMENT = 'gay shop'

 

Example of adding a field:

Alter table AUTH_MALL add column SHORT_NAME VARCHAR (20) DEFAULT ''comment' square name abbreviated to 'after FULL_NAME;

 

Example of modifying table fields:

Alter table GATEWAY_PAYMENT_ORDER modify column stat varchar (2) DEFAULT '0'

Comment 'transaction status 0: Pending payment/refund, 1: Waiting for third-party channel callback, 2: payment/refund successful, 3: payment/refund failed, 4: payment/refund confirmed, 5: payment/refund confirmation failed, 6; transaction closed, 7: To be collected (if in this status-check whether the payment account is normal), 8: payment/refund confirmation successful-no other operations can be performed, 9: Verification Failed, 10: Synchronous Confirmation/buyer paid-waiting for the seller to deliver WAIT_SELLER_SEND_GOODS, 11: synchronous Confirmation/the seller has delivered the goods and waits for the buyer to confirm WAIT_BUYER_CONFIRM_GOODS 'after description;

 

 

2. Write SQL statements

2.1. Try to use single-table queries to avoid multi-table JOIN. Subsequent ON conditions of JOIN cannot be judged by OR, for example, select. c1, B. c2 from a, B ON (. ID = B. pid or B. TAG =. TAR_GET); OR performance is very low, PS: some of our online function modules that open slowly are caused by this OR statement.

 

2.2. Write the SQL statements written to the application and prohibit all DDL operations, such as create, drop, alter, grant, and remove. If you have special requirements, negotiate with the dba before using the statements.

 

2.3 when writing SQL statements, you must specify the table name prefix for each field. For example, select ub. id, ub. name from user_business ub where ub. create_date> ''; Use" # var_name "to bind variables to the SQLMap file of iBatis, and use" $ var_name $ "to replace variables. All queries that require dynamic order by conditions, when using substitution variables, you need to write the content that may be passed in to the code by enumeration, and prohibit external incoming content.

 

2.4. If transaction support is required, when innodb is used, disable automatic commit when connecting to the database, for example, set auto_commit = 0; when writing java code, in the case of transaction processing, after executing insert, delete, and update, commit; In the exception Code block, the rollback operation must be written.

 

2.5. Do not write code similar to select *. You need to specify the field name.

 

2.6. mysql has the same date and character, so it does not need to be converted like oracle. For example:

Select e. username from employee e where e. birthday> = '2017-12-31 11:30:45 '.

 

2.7. Avoid applying a function to a field in the where clause, unless required by the business, but consult the DBA During writing. For example, DATE_FORMAT (p. PAYMENT_DATE, '% Y-% m-% D')> = DATE_FORMAT ('2017-10-01', '% Y-% m-% D'), you must correct it.

 

2.8 to avoid unnecessary sorting. When group by is used, it will be sorted by default. When you do not need to sort, order by null can be used;

 

2.9 when the table is connected, if the fields of the two tables used for connection are of different data types, the type conversion function must be added on one side. This prevents mysql from performing implicit type conversion.

 

2.10. You are prohibited from performing batch update SQL operations on the database in the application. If necessary, send an email to the dba to determine that the database is manually executed on the IDC database within the appropriate time period.

3. Basic Principles

PS: The table structure change must be initiated by the team of the database table.

 

1. All SQL statements for modifying the table structure executed in the test environment must go through DBA review.

2. Physical deletion is not allowed, and stored procedures, triggers, and views are not allowed. You can apply for DBA in special cases and business scenarios.

 

PS: all the specifications are applicable to your own business scenarios. You are welcome to make better suggestions and constantly summarize and expand the most appropriate database specifications based on your business development.

Bytes ----------------------------------------------------------------------------------------------------------------

<All Rights Reserved. This document can be reprinted, but the source address must be indicated by link. Otherwise, we will be held legally responsible.>
Original blog address: http://blog.itpub.net/26230597/viewspace-1312184/
Original Author: Huang Shan (mchdba)

Bytes ----------------------------------------------------------------------------------------------------------------

 

MySql database design specifications urgently needed

1. Data Table naming rules
Module name abbreviation _ storage information [_ storage Information subclass] (multiple words are separated by underscores), all in lower case, for example: B2C _goods_type
2. Field naming rules
Store information attributes (multiple words are separated by underscores), all in lower case. Naming rules only come from services and express the meaning of columns as much as possible.
Example: goods_id
3. Field Type specification.
Rule: use up a small amount of storage space to store data with a few fields.
For example, if int is used, char or varchar is not required.
Int is not required if tinyint can be used.
Varchar (255) is not used when varchar (20) is used)
Try to use int type for the timestamp field, such as created: indicates from '2017-01-01? 08:00:00 'indicates the int seconds that start with an English word. gmtCreated indicates the time of the datetime type, that is, '2017-01-01? 00:00:00. The corresponding type in Java is Timestamp.
Date: date
Time: time
Number Format: int, tinyint, mediumint, smallint, and bigint
String: char, varcahr;
Text: text
Amount: float

MySql Database Design Problems

I don't understand why you need to set up the language and relational table relationtab. You just need to set up the first two.

Software Table soft:
Field: sid, sname software name, size, ver version, lang language, cid Software category, desc description, URL address, IMG address
Primary Key sid, foreign key cid

Classification Table cls:
Field: cid, cname category name, pre parent category
Primary Key cid, foreign key pre
Pre refers to the cid of this table. If pre is 0, it indicates the first-level classification.
Sample Data:
Cid cname pre
1 System Software 0
2 Application Software 0
3 Operating System 1
4. Database System 1