SQL Server database design

Welcome to the Windows community forum and interact with 3 million technical staff. I. Necessity of Database Design in actual software projects. If the amount of data to be stored in the system is large, there are many tables to be designed, and the relationship between tables is complex. Therefore, we need to set up a standard database. Without the database design,

Welcome to the Windows community forum and interact with 3 million technical staff> 1. Necessity of Database Design in actual software projects. If the data volume to be stored in the system is large, there are many tables to be designed, and the relationship between tables is complex. Therefore, we need to set up a standard database. Without the database design,

Welcome to the Windows community forum and interact with 3 million technicians>

I,Database Necessity of 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

1. undesigned databases or poorly designed databases are likely to cause

1. database operation efficiency underground

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

2. Well-designed Databases

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.

1. entity-relationship (E-R) Data Model

An Entity-Relationshipdatamodel is 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.

2. 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.

3. Attribute)

Objects are generally specific features. These features are called attributes of objects. Each attribute has its own value range, which is called a valueset 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.

4. 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

　　1. 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.

　　2. Demand 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

　3. Outline Design Phase: drawing a 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.

[1] [2]