Basic concepts and architecture of MySQL database

Database

1. Key: The primary key is the flag column in the table. A key may consist of several columns. You can use keys as a reference between tables.

CustomerID is the primary key of the Customers table, which is called a foreign key when it appears in other tables, such as the Orders table.

2. Mode

The full design of the database complete table is called the database schema.

A pattern should display columns for tables and tables, primary and foreign keys for each table.

A pattern does not contain any data, but we may want to use the sample data in the pattern to parse the meaning of the data.

Example: Customers (CustomerID, Name, Address, city)

Orders (OrderID, CustomerID, Amount, Date)

An underlined element indicates that the element is the primary key of the relationship, and the italic element is the foreign key of the relationship.

3. Relationship

A foreign key represents the relationship of two tabular data. These relationships can be divided into one or one-to-many, many-to-many, depending on the number of objects that are contained in the relationship.

Design Database

1, consider the actual object of modeling

Each real-world object that you want to model needs to have its own table.

2. Avoid saving redundant data

To avoid update irregularities in 3 situations: modify, insert, and delete irregularities.

3. Use atomic column values

Only one data is stored for each property of each row. Books ordered inconsistent

When there are many-to-many relationships for two objects, such as the relationship between orders and books, you need to create one of the following new tables, Order_items

4. Select a Meaningful key

You should confirm that the selected key is unique.

5. Avoid the design of multiple empty attributes

A lot of empty values in the database are a bad thing. It is a huge waste of space and can cause errors when counting the total number of columns or applying calculation functions to other numeric columns.

6. database table Type

Simple table: A simple table that describes real-world objects. These tables may also contain keys for other simple objects, with one-to-many relationships between them.

Correlation table: Describes the many-to-many relationships of two real-world objects.

Web Database Schema

A typical Web database transaction consists of the following steps:

Basic concepts and architecture of MySQL database