Project Summary (2) -- build a database

I am working on a B2C Online Shopping System (all clothes). At first, the customer requested to only sell men's clothes, and later I needed to buy women's clothes.

We recommend that the customer solve the problem by entering the product name and category management. Let's talk about some of the experiences and lessons I have learned from my system!

1.The establishment of data models is very important.The data model I am talking about here refers to the relational structure between the real-Type [Entity Type], using the database terminology

It is to contact [Relationship ]. The more detailed the analysis, the deeper the understanding of the relationship between entities. For example, when I analyzed the clothing entity, there was a deviation.

ClothesDimensionsYesThe attributes of clothes are also an independent entity. At that time, I analyzed the dimensions as only one attribute designedClothesThis will produce

There are multiple records in the database for clothes of different sizes. This causes data redundancy.

2. Let's talk about the basic things. It must be meaningful when we name a table. For example, when I first named an Order table: Order. Someone may

At a glance, we can see that Order is the reserved word of the system. But at that time, I didn't realize that Order is a reserved word. Therefore, after naming a table, we should

Check whether the system reserved words are used. In addition, for the sake of database security, although it should be of great significance when we name a table, in order to prevent others from cracking

Some special characters can be added to the surface of the database to name the database. For example:Order $ MySys. We often filter out the special character "$" entered by users ".

3. process the deletion. For a good business system, the design of Money should be prohibitedDelete permission, Not only should the system be not designed to be deleted, it is best to count

The delete permission is also disabled in the database table. Of course, you can also design the user permission level in the system. Only users with specific permissions can have the delete permission, but this is not the safest.

. In our system, the deletion processing is to setStatus fieldFor example, for deleted data, you can set a Bit field to identify the deleted data and

Deleted Data. For example, if an order or a product associated with the order is deleted, an error may occur when the order corresponding to the product is deleted. Therefore, my processing is to identify the status field.

4. field size. For a system with a small amount of data, the field value can be as large as possible. However, for a system with a relatively objective data volume, the field size is divided.

Otherwise, if the field value is too large, the data growth will be very rapid.

A. Mobile phone numbers should be stored in the Bigint, Varchar, or Nvarchar type.

B. The amount should be stored in Decimal. [Note: The Decimal size in SQL Server is inconsistent with that in. NET !]

5. data consistency. If we set a foreign key when designing a database table, it may be troublesome if we delete it. We need to delete it in cascade mode. However, if we do not set a foreign key

Foreign keys, that is, constraints, will cause Database Inconsistency. Therefore, the integrity of the database is damaged without corresponding constraints. It is not worthwhile to sacrifice consistency for convenience.

, Especially a qualified business system.

6. Use stored procedures as much as possible in the system. Stored Procedures have many advantages and are efficient in execution. They also prevent the injection vulnerability caused by splicing SQL statements. In addition

The time is very convenient. the finer the function of a system, the better the maintainability. [of course, it is also a prerequisite. For example, the module coupling should be within the acceptable range ]. But some services

Where logic is particularly strong, it should be done through a program.

7. When using SQL statements and stored procedures, do not forget the view. view is also a good mechanism for database applications. For complex queries-most table connections, we can

When a view structure is used, for example, querying the information of an order in the system, we may need to: commodity information, delivery information, discount product information, etc, this multi-Table connection problem

It is more appropriate to use the view to solve the problem. Of course, the view is no different from the SQL statement for the program. After all, a view is exported using one or more SELECT statements.

8. trivial applications

A. It is best to use multiple fields for the place where the user receives the goods in the order. Such as the harvest address, province, city, and region.

B. In the database, the data type should be unified in terms of the amount. Do not use Decimal in a place or Float in a place. If the two tables are associated

Data type conversion is required.

C. The attribute field of an object [A table] only has a few values that can be enumerated. The status representation method is used and the question tinyint is used for identification, it is implemented through a program during identification.

D. If the field can be empty, it is best to add the default value to prevent the program from handling non-null cases.

E. It is best not to use sa for database operation accounts, but to create an account separately and set the corresponding query, deletion, and update operation permissions. You can set multiple connection characters in WebConfig.

String, which is used by the senior administrator and a common user respectively. The accounts used to log on to the database are different.

F. Note that the full name of the database object [table, view, stored procedure, etc.]. For example, if a table is MyOrder, its full name may be dbo. myOrder. If you are using a hosted machine,

After the database has been created, you need to delete the structure of the previous table, and then create a new table with the structure inserted, YourName will appear. in the case of MyOrder, if "SELECT * FROM MyOrder" is used in the system"

The default value is dbo. MyOrder. If this happens, the query will fail.

These are some of my experiences during system development.