Analysis of Concept, Logic, and Physical Structure Design in Database Design

For beginners, the concept, logic, and physical structure design in database design are more like twin brothers. They can't tell who they are and what their main functions are. Occasionally I checked the literature and saw a relatively standardized expression in a journal, and used relatively easy-to-understand language to distinguish the relationship between the three, and borrowed the standardized schematic diagram in the journal for learning and communication only ( see the reference for the source of the journal. If there is any infringement, please Contact deleted ).
The database design should be a combination of conceptual structure design (ER diagram), logical structure design (relational mode), and physical structure design (data table, design field).

Insert picture description here


Insert picture description here

The conceptual structure design is based on the research, understanding, and analysis of the real-world relationship, which are the physical objects and the characteristics of the objects, and the relationship between them is one-to-one, one-to-many, and many-to-many. After the above problems are sorted out, the conceptual structure can be expressed in the form of a relationship diagram, that is, an entity relationship diagram (ER diagram).

Insert picture description here

The logical structure design is based on the entity relationship diagram to get how many entities there are, what attributes (features) each entity contains, and which attributes can be associated with the relationship between entities (the attributes shared by these entities are primary keys or foreign keys). This gets the relational pattern, namely: XX relational pattern (XX attribute 1, XX attribute 2, ...). According to the relationship standardization theory, the relationship is optimized to eliminate data redundancy, insert anomalies, and delete anomalies to obtain a relatively high-level paradigm to meet the normal processing of data.

Insert picture description here


Insert picture description here

The physical structure transforms the optimized relational patterns in the logical structure into relational tables in the database. Each attribute is stored with an appropriate type and length, and the primary key and related constraints are set. The data design can be completed to meet the application requirements. Data storage, insertion, and deletion requirements.

Insert picture description here


References:
[1] Qi Huangxiong, Mo Rucong. Design and implementation of jade tourism online shopping mall based on JSP technology [J]. Science and Technology and Industry, 2021, 21(05): 87-92.
CNKI website: Jade based on JSP technology Design and Realization of Tourism Online Shopping Mall