MySQL database design specifications and mysql design specifications

MySQL database design specifications and mysql design specifications
Reprinted by songdeyouxiang

1. Database naming rules
It consists of 26 English letters (case sensitive) and 0-9 Natural Numbers (not frequently needed) with an underscore;
The name must be concise and clear (the name cannot exceed 30 characters );
For example, user, stat, log, or wifi_user, wifi_stat, or wifi_log can be used to add a prefix to the database;
Unless it is a backup database, you can add the Natural Number of 0-9: user_db_20151210;

2. The naming rules for database table names are composed of 26 English letters (case sensitive) and 0-9 Natural Numbers (not frequently needed) with underscores '_'. The names are concise and clear, multiple words are separated by underlines '_'. For example, user_login, user_profile, user_detail, user_role, user_role_relation, user_role_right, user_role_right_relation table prefix 'user _ 'can effectively display tables with the same relationship. 3. The database table field name naming Rules use 26 English letters (case sensitive) it is comprised of 0-9 Natural Numbers (often not needed) and underlines '_'. The names are concise and clear. Multiple words are separated by underscores '_'. For example, user_login table field user_id, user_name, pass_word, eamil, tickit, status, mobile, add_time; each table must have an auto-incrementing primary key. The names of related fields between the add_time table and the table must be the same as possible; 4. The field type specification of the database table uses up a small amount of storage space to store data of one field. For example, if you can use int, do not use varchar or char. You can use varchar (16) do not use varchar (256); Use int for IP addresses; Use char for fixed-length types, such as ZIP code; Use tinyint instead of smallint or int; it is best to give a default value for each field, preferably not null; 5. The database table index naming conventions are concise and clear. For example, the index of user_name field in user_login table should be the unique index of user_name_index; create a primary key index for each table; create a reasonable index for each table; create a composite index with caution; 6. Be familiar with the first paradigm (1NF) of the database paradigm. Field Values are atomic, no further score (all relational database systems meet the first paradigm); for example, the name field, where the surname and name are a whole, if the surname and name are distinguished, two independent fields must be set up; 2NF: A table must have a primary key, that is, each row of data can be uniquely distinguished. Note: The first paradigm must be satisfied first; the third paradigm (3NF ): A table cannot contain non-Keyword fields in other related tables, that is, the data table cannot have a redundant field. Note: The second paradigm must be satisfied first. Note: we often do not follow the third paradigm in the design table, because a reasonable redundant field will reduce join queries. For example, the album table will add a click field of the image, the number of clicks of an image is also added to the album image table;

MYSQL Database Design Principles

1. The core principle is not to perform operations in the database; the cpu computing must be moved to the business layer; the number of control columns (the number of fields is smaller and smaller than 20); the balance paradigm and redundancy (efficiency first; decline 3B (reject large SQL statements: big SQL, reject big transactions: big transaction, reject large batches: big batch ); 2. The field type principle makes good use of the value type (space saving with the appropriate field type); converting characters into numbers (the best conversion can also save space and improve query performance ); avoid using NULL fields (it is difficult to query and optimize NULL fields, the index of NULL fields requires extra space, and the composite index of NULL fields is invalid); Use less text type (use varchar instead of text field whenever possible ); 3. Use indexes reasonably based on the indexing principle (improving queries and slowing down updates, and the index must not be more or better); prefix indexes must be created for character fields; column operations are not performed on indexes; we recommend that you use auto-increment columns for innodb primary keys (primary keys should not be modified to create clustered indexes, and strings should not be primary keys) (understand the Innodb Index storage structure ); do not use foreign keys (restricted by the Program); 4. SQL principles SQL statements are as simple as possible (one SQL statement can only be operated on one cpu. Large statements can be used to remove small statements to reduce lock time, A large SQL statement can block the entire database); simple transactions; avoid using trig/func (triggers and functions do not need to be replaced by client programs); Do not use select * (consuming cpu, io, memory, bandwidth, which is not scalable); OR rewrite to IN (or efficiency is n-level); OR rewrite to UNION (mysql index merge is very retarded ); select id from t where phone = '000000' or name = 'john '; => select id from t where phone = '000000' union select id from t where name = 'jonh' avoid negative %; Use count (*) with caution; limit efficient paging (larger limit, lower efficiency); use union all to replace union (union has deduplicated overhead); Use less join; use group by; use the same type of comparison; scatter batch update; 5. Performance analysis tools: show profile; mysqlsla; mysqldumpslow; explain; show slow log; show processlist;

Copy code
Database Design Principles

Copy code

[Example 1]: A copy of employee resume. In the human resources information system, three basic tables are required: employee basic information table, social relationship table, and work history table.
This is a typical example of "one original document corresponds to multiple entities.

The Design of primary keys and Foreign keys plays an important role in the design of global databases. After the design of the global database is complete, an American database design specialist
He said: "keys are everywhere. There is nothing except keys." His database design experience also reflects his information system core.
High abstraction of the Mind (Data Model. Because: The primary key is the height abstraction of the object. The pairing between the primary key and the foreign key indicates the connection between the object.

[Example 2]: There is a basic table for storing items, as shown in table 1. The existence of the "amount" field indicates that the table design does not meet the third paradigm,
Because the "amount" can be obtained by multiplying the "unit price" by "quantity", the "amount" is a redundant field. However, add the redundant field "amount,
This can increase the speed of query statistics, which is the practice of changing the space for time.
In Rose 2002, two types of columns are required: Data columns and calculation columns. Columns such as "amount" are called "calculation columns", while "unit price" and
Columns such as "quantity" are called "Data columns ".

Table 1 Table Structure of the commodity table
Product Name product model unit price quantity amount
TV 29 2,500 40 100,000

No redundant database can be designed. However, databases without redundancy may not be the best. Sometimes, to improve operational efficiency, you must reduce
Low paradigm standard, with redundant data properly retained. The specific approach is to follow the third paradigm in conceptual data model design and reduce the workload of paradigm standards to physical
Data Model Design considerations. Reducing the paradigm is to add fields and allow redundancy.

[Example 3]: In the "library information system", "books" are an entity, and "readers" are also an entity. The relationship between the two entities is one
Typical multi-to-many relationship: A book can be borrowed by multiple readers at different times, and a single reader can borrow multiple books. Therefore
The third entity is added. The entity name is "borrow and return". Its attribute is: Borrow and return time, borrow and return sign (0 indicates borrowing and returning; 1 indicates Returning books). In addition,
It should also have two foreign keys (the primary key of the "book" and the primary key of the "Reader"), so that it can be connected to the "book" and "Reader.

Watch:

Book 1 and the entity named "borrow and return" n
Reader 1 and the entity named "borrow and return" n

[Example 4]: the unit price, quantity, and amount fields in the product. The "amount" is derived from the "unit price" multiplied by the "quantity", which is redundancy,
It is also a type of advanced redundancy. Redundancy is designed to speed up processing. Only low-level redundancy increases data inconsistency because the same data can
It can be input multiple times from different times, locations, and roles. Therefore, we advocate advanced redundancy (derivative redundancy) against low-level redundancy (repetitive redundancy ).

10. View technology is useful in Database Design
Unlike basic tables, code tables, and intermediate tables, a view is a virtual table that depends on the real table of the data source. The view is for programmers to use the database.
A window is a form of base table data synthesis, a method of data processing, and a means of user data confidentiality. For complex processing,
Increase computing speed and save storage space. The definition depth of a view generally cannot exceed three layers. If the three-layer view is not enough, you should define a temporary table on The View,
Define the view on the temporary table. In this way, the depth of the view is unlimited.

Views play a more important role in certain information systems related to national political, economic, technical, military, and security interests. The basic tables of these systems are complete.
After the physical design, create the first-level view on the basic table. The number and structure of the view on this layer are exactly the same as the number and structure of the basic table.
It is also stipulated that all programmers are only allowed to operate on The View. Only the database administrator can take the "Security Key" jointly controlled by multiple personnel ",
In order to operate directly on the basic table. Readers can think about this: Why?

(2) The fewer fields that combine primary keys in a table, the better. Because the primary key is used to create a primary key index, and the other is used as the foreign key of the sub-table
The number of fields with primary keys is reduced, which not only saves the running time, but also saves the index storage space;

(3) The fewer fields in a table, the better. Only when the number of fields is small can it indicate that there is no data duplication in the system and there is little data redundancy.
More importantly, the reader is urged to learn how to "change columns to rows", which prevents the field in the sub-table from being pulled into the main table.
Multiple spare fields. The so-called "Change columns to rows" means to pull out part of the main table and create a sub-Table separately. This method is simple.
Single, some people do not get used to, do not adopt, do not execute.

The practical principle of database design is to find a proper balance between data redundancy and processing speed. "Three shao" is an overall concept,
A principle cannot be isolated. This principle is relative, not absolute. The "three-plus" principle is certainly incorrect. Imagine: if the system is covered with the same power
Yes, a E--R graph of one hundred entities (one thousand properties in total) is definitely much better than a E--R graph of two hundred entities (two thousand properties in total.

We advocate the "Three shao" principle, which allows readers to learn to use Database Design technology for system data integration. The data integration step is to integrate the File System
For the application database, the application database is integrated into the topic database, and the topic database is integrated into the global integrated database. The higher the degree of integration, the data
The more shared, the less information islands, the number of entities, the number of primary keys, the number of attributes in the global E-R diagram of the enterprise information system
The smaller the number.

The purpose of advocating the "Three shao" principle is to prevent readers from using the patch technology to constantly add, delete, and modify databases, making enterprise databases arbitrary.
Design the "Garbage Collection" of database tables, or the "Miscellaneous" of database tables, and finally create basic tables, code tables, intermediate tables, and temporary tables in the database.
Disorganized and countless tables (that is, the number of tables is increased by dynamic table creation), leading to the inability to maintain information systems of enterprises and institutions.

The "three-plus" principle can be implemented by anyone. This principle is the theory of "patching methods" for designing databases. The "three less" principle is less refined
Principle: it requires a high level of database design skills and art, not everyone can do it, because this principle is to prevent the use of "patching methods"
Design the theoretical basis of the database.

The fourteen skills mentioned above are gradually summarized by many people in a large number of database analysis and design practices. For the use of these experiences, readers should not be able to stick to them, but should digest and understand them, be realistic, and be flexible. And gradually achieve: Development in the application and application in the development.

Reprinted from: http://www.javaeye.com/topic/281611

======================================

How does denormalization explain in the DATABASE? Example

Dictionary: reverse normalization, blocking Normalization
This is what we usually call inverse normalization.
For example, set two primary keys in a table.
For example, the relationship between two tables is multi-to-many.
And so on, they all violate the standard paradigm.
Both normalization and inverse normalization are designed to improve database performance.
It is better for beginners to standardize as much as possible.