SQL Server database design

I. Necessity of Database Design

In actual software projects, if the amount of data to be stored in the system is large, many tables need to be designed, and the relationship between tables is complex, then we need to set up a standard database. Without the database design, the database we build is unreasonable and inappropriate, so the database maintenance and operation efficiency will be very high. This will directly affect the running and reliability of the project.

2. What is database design?

Database Design is actually the process of planning data objects in a structured database and the relationship between these data objects.

Iii. Importance of Database Design

A database that has not been designed or a poorly designed database may cause

1. database operation efficiency underground

2. An error occurred while updating, deleting, and adding data.

Good Database Design

1. High execution efficiency

2. Make the application easier to develop

3. good scalability

4. good maintainability

Iv. Data Model

The data model is like an outline of the relationship between data. The whole model is like a framework.

Data models can be classified by means of inter-record Association, including hierarchical models, mesh models, and relational models. The first two types are also known as formatted data models. The quality of the data model directly affects the performance of the database. Therefore, the selection of the data model is the primary task of the database design.

Entity-relationship (E-R) Data Model

Entity-Relationship data model, an Entity-relational data model. The E-R data model is different from the traditional relational data model. It is not oriented to implementation, but to realistic objects.

Entity)

Data is used to describe objects in reality, and the objects to be described are all kinds of specific and abstract objects. There are physical and conceptual objects. All things, things, and concepts that can be differentiated from each other can be abstracted as entities. Multiple entities of the same type can be called Entity sets ). Therefore, in the E-R data model, there are also types and values; entities can be defined as types, each entity can be its instance and value.

Ø Attribute)

Objects are generally specific features. These features are called attributes of objects. Each attribute has its own value range, which is called value set in the E-R data model ). In the same object set, the attributes and Value Sets of each object are the same, but different values may be taken. Attribute corresponds to the columns in the database table.

Ø Relationship)

There are various relationships between entities. These relationships are abstracted as links. Not only can entities have attributes, but also links can have attributes.

5. Database Design Steps

The database design can be divided into the following stages:

1. Demand analysis stage: analyze the customer's business needs, especially the data needs

2. Outline Design Phase: drawing the E-R diagram of the database, and confirming the correctness and integrity of the requirement document, the E-R diagram is the project design personnel, developers, testers, important creden for communication with customers

3. Detailed design stage: The E-R diagram of the outline design stage is converted into a database table, the logic design is carried out, the relationship between the primary and Foreign keys is determined, and the three paradigm of the database is used for review, and conduct technical review. Finally, you can choose which database (Oracle, SQLServer, MySQL) to create databases and tables.

Ø requirement analysis stage: Database System Analysis

The focus of the show analysis stage is to investigate, collect, and analyze the customer's business data requirements, as well as the data security and integrity requirements.

Requirement Analysis steps:

1. Confirm Business Requirements

2. Mark a link object

3. Identify the attributes of each object

4. confirm the relationship between entities

Brief design stage: Drawing E-R Diagram

As a Database Designer, you need to share your design ideas with other members in the project team, discuss the rationality, security, and integrity of the database design, and confirm whether it meets the customer's business needs. So the use of E-R, This graphical representation of the most intuitive.

* Entities, attributes, and relationships in a E-R Diagram

The above simple E-R diagram can see the relationship between the user and income and expenditure. It can be seen in the following section that a rectangle is used to represent an object. An entity is a common term. An ellipse is used to represent an attribute. A diamond represents a link. It is generally a verb.

* Ing Base

The ing base number indicates the number of objects that can be connected by a link. For the binary relationship between entity set A and entity set B, the possible ing bases include:

1. One-to-one: that is, the object A has only one association of entity B at most, and the object B has only one association of entity A at most. Represented in a E-R diagram:

2. one-to-many: Entity A can be associated with any number of entity B, and entity B can be associated with one entity in Entity A at most. E-R diagram representation:

3. Many-to-one: Entity A can be associated with A maximum of entity B, while entity B can be associated with any number of Entity. E-R diagram representation:

4. many-to-many: Entity A can have multiple B entities, and entity B can also have any number of A entities. E-R diagram representation:

* E-R Diagram

The E-R diagram can graphically represent the entire logical structure of the database, which consists:

1. rectangle indicates the object set

2. elliptic representation of attributes

3. Diamond represents the relationship,

4. A straight line is used to connect object sets and attributes, object sets, and relationships.

5. A straight arrow indicates the ing base between entity sets.

Ø detailed design phase: converting a E-R diagram Into a table

The procedure is as follows:

1. convert each object to a corresponding table and convert each attribute to a corresponding column.

2. Identify the primary key of each table

3. Convert the relationship between entities to the primary/foreign key relationship between tables.

Vi. database design standardization

Common Problems in Database Design

1. Large data redundancy

2. An error occurred while inserting data.

3. Deletion exception

4. Update exception

Ø standard design

For a good relational database model, the attributes of each link must meet certain inherent semantic conditions, that is, to design the relational model according to certain specifications, which is the standardization of the design.

In database design, there are some special rules called the database design paradigm. Following these rules, you can create a good database. The famous three paradigm theories of the database:

1. 1NF)

The first paradigm is the most basic condition paradigm to meet the needs of the relational database model. Each attribute in a relationship must be a simple item that cannot be divided, and cannot be a combination of attributes, that is, the attribute value is an atomic value that cannot be split.

2. 2NF)

The second paradigm is based on the first paradigm to ensure that each column in the table is related to the primary key. It is defined that if a link satisfies 1NF and all columns except the primary key relationship depend on this primary key, the second paradigm is satisfied.

3. Third Paradigm (3NF)

The third paradigm is based on the second paradigm. The goal of the third paradigm is to ensure that each column is directly related to the primary key column, rather than indirectly related. It is defined as: If a link meets 2NF, and other columns except the primary key are not passed dependent on this primary key.

Ø relationship between standardization and Performance

To meet the three major paradigms, the database performance may be reduced to a certain extent. Therefore, in actual database design, we should try our best to meet the three major paradigms, so as to avoid data redundancy and various database operation exceptions, and also consider the data access performance. Sometimes, in order to improve database access efficiency, the most suitable database design scheme is to appropriately allow the existence of a small amount of data redundancy.