Basic concepts and knowledge of database design __ Database

Relationship Type: Mysql, Oracle

non-relational: Mongodb, Redis

Database Design

Reduces data redundancy, avoids data maintenance anomalies, saves storage space, and efficiently accesses

Design Steps

Requirements Analysis: What the data is, what attributes it has, the characteristics of the data and attributes (storage features), and the lifecycle of the data

Logical design: Use ER diagram to design database logically

Physical design: Turning logical design into physical design

Maintenance optimization: New requirements for the table, index optimization, large table split

Demand Analysis

1. Relationship between entities and entities (1n NN)

2. Attributes contained by the entity

3. A combination of attributes or properties can uniquely identify an entity

instance

Take a small E-commerce site for example. User module, commodity module, order module, shopping cart module, supplier module

User module: Record registered user information. Unique indicator-username, ID, telephone. Store features, grow, and permanently store you.

Product module: ..., can be archived storage of the offline goods (cannot be deleted because of the order, etc., can be migrated).

Order module: ..., permanent storage (sub-table, library storage)

Shopping Cart module: ... without permanent storage (set archive, cleanup rule)

Supplier module: ..., permanent storage

Relationship:

Logical Design

1. A logical model for translating requirements into databases

2. Show the logical model by the type of ER diagram

3. Independent of the specific DBMS system selected

three major paradigms

First paradigm: All fields in a database table are single properties and cannot be divided. This single property is composed of basic data types. The first normal form requires that the tables of the database are two-dimensional tables.

Second paradigm: There is no partial function dependency of non-critical fields on any candidate-critical fields in the database table. Partial function dependency refers to the existence of a keyword in the combination of key words to determine the non-keyword. The tables for all but the key fields conform to the second normal form.

The third normal form: Two paradigm is above three normal forms, if the data table does not exist the non-critical field for any candidate key fields of the transfer function dependency rule is in accordance with the third normal form.

BC Paradigm: If compound keywords, there is no pass-through function dependency between compound keywords. (That is, to upgrade non-critical fields in the third paradigm to keywords and their key fields)

 

 

Physical Design-the structure of the design standard

1. Select the appropriate database management system

2. Define database, table and field naming conventions

3. Select the appropriate field type according to the selected DBMS system Varchar/char how to choose

4. Anti-normal design is to increase redundancy, in exchange for efficiency of the promotion of space

MySQL common engine: Mylsam does not support transactions. Table-level locks that support concurrent inserts. Main Select,insert. Easy to read and write frequently.

Mrg-myisam does not support transactions. Table-level locks that support concurrent inserts. Primary segment archiving, data warehousing. It is not easy to find too many scenes globally.

InnoDB support transactions. Supports MVCC row-level locks. Primary transaction processing.

Archive does not support transactions. Row-level locks. Primary logging, only supports Insert,select. It is not easy to read, update and delete randomly. Small storage capacity.

NDB Cluster support transactions. Row-level locks. Primary high availability. Recommended use. (MySQL cluster)

naming rules for tables and fields

1. The principle of readability (hump named, but note the case sensitivity of the table name)

2. Ideographic principle indicates object, data content, stored procedure

3. Long-name principle as little as possible or without abbreviations

 

selection Principles for field types

Data type impressions storage space overhead and query performance. You can select multiple data types when the priority number, second date or binary, the last string. The same level of data type, priority occupies the space small.

1. When the same data is compared (query conditions, join and sort), character processing is slower than the number.

2. In the database, data processing in a page, the smaller the length of the column, conducive to performance improvement.

How char and varchar choose

1. The data length is almost the choice char, the data length is inconsistent when varchar.

2. The maximum data length of the column is less than 50Byte, typically char, but if this column is rarely used, you can choose varchar based on space-saving and reduced I/O considerations.

3. It is not generally advisable to define a char type greater than 50Byte.

Decimal and float

1. Decimal is used to store accurate data, and float can only be used to store imprecise data.

2. Float storage space overhead is generally less than decimal (7-bit decimal to 4 bytes, 15-bit decimal to 8 bytes)

Type of time

1. The length of int is smaller than datetime, but it is not convenient to use functional conversion and can only be stored until 2038-1-19 11:14:07. Use int when you don't often query time.

2. Stored time granularity date of day hours and weeks

How to select a primary key

1. The business primary key is used to represent business data and to correlate the table with the target. Database primary key to optimize data storage (InnoDB not manually set primary key will automatically generate 6-byte implied primary key).

2. Depending on the type of database, consider whether the primary key should be sequentially grown.

3. The field type of the primary key takes up as little space as possible, and for tables stored using a clustered index, the primary key information is attached to each index.

4.

avoid using FOREIGN KEY constraints

1. Although data integrity is maintained, the efficiency of data import is reduced. (In high concurrency, each query checks to see if it conforms to the external inspection)

2. Reduce maintenance costs.

3. Although it is not recommended to use a foreign key, an index is the same on the associated column.

4.

Avoid using triggers

1. Reduce the efficiency of data import.

2. Unexpected data exceptions may occur.

3. Is the complexity of business logic.

4.

about reserved Fields

1. The reserved field type and storage content cannot be properly guided.

2. Late maintenance of reserved fields costs the same as the cost of adding one field.

3. Use of reserved fields is strictly prohibited.

Anti-Normalization of

Violate the third paradigm and exchange space for time.

1. Reduce the number of tables associated.

2. Increase the reading efficiency of the data.

3. Anti-normalization must be modest.

Maintenance Optimization

1. Maintain the data dictionary remember what each number in the dictionary means. You can use a third-party tool to maintain comment '.

2. Maintain index where GROUPBY by clause optional high columns to be placed before the index do not include too long data types too many indexes can degrade the efficiency of the indexes periodically maintain index fragmentation do not use mandatory index keywords in SQL statements.

3. Dimensional Maintenance table structure column change control table width and size (split).

4. In the appropriate time to the table horizontal split (table structure, Hash key) or vertical split (intermediate segmentation table, table structure is different).

suitable for Operation Bulk operation (not suitable for operation by article)

Prohibit use of select * Query (I/O waste, table structure modified data error)

Controlling the use of user-defined functions (index invalidation in columns that affect index use functions)

Do not use Full-text indexing in the database (Maintenance high, not suitable for Chinese)