Database Design Series [02] Why learning database design?

ArticleDirectory

• 1. What are the problems caused by poor database design?
• 2. Example scenario
• 3. Summary

1. What are the problems caused by poor database design?

In the conceptual design stage of the database, different modelers have different results for modeling in the same field, and the converted relational models are also different. In this way, there are advantages and disadvantages of the relational model. To learn about database design, we need to learn the rules and common representation methods summarized by our predecessors. Before learning how to design, we should first understand the problems caused by poor design.

2. Example scenario

In the procurement scenario, the order number, product information, and operator information must be recorded. If we design it like this

 

There are data redundancy, insertion, update, and deletion exceptions;

2.1 data redundancy

In this application design, ordernum and createname are repeatedly stored. When the order volume is large, this waste will be considerable.

2.2 insertion exception

From the design of this relational mode, we can see that ordernum and goodsname constitute the primary key. When no one buys the product, the product cannot be imported into the database. Obviously, this is unreasonable.

2.3 update exception

If Wang Xiaoji, the Creator, is renamed as Wang Xiaoyi, many lines need to be updated. Or, due to some verification problems, data such as "sanbao" and "sanbao" appears. In terms of semantics, this is the same product, but it cannot be identified from the database, it is a headache when a report is generated.

2.4 deletion exception

If, two years later, the three treasures product was discontinued, we would like to delete the Three Treasures product so that it cannot be purchased again. However, we also deleted a purchase history of 111123. This is also unreasonable.

3. Summary

In general, for the same application field, we can design a good model and a business trip model. So how can we design it to be closer to a good model. We need to follow some design paradigms. What is the design paradigm? Write it again in the next article.