My first python web development framework (4) -- database structure design and creation, and python Structure Design

My first python web development framework (4) -- database structure design and creation, and python Structure Design

After finishing the front-end html design, Tom immediately began to design the database structure.

　　

Before he started, Tom recalled the database design explanation that the boss talked about during training at the company:

For beginners, it is normal that many users do not know how to design the data table structure when they get the prototype. They can learn from others' Project Summary experience and take a long time.

After obtaining the prototype, we need to carefully observe the content displayed in the prototype and consider the content that remains unchanged, that is, the content is written to the page, for those that require background changes, record these changes and add them to the data dictionary. When recording these fields, you need to perform classification. A category is a data table. (Of course, if you are a beginner, you can also, like some architects, do not use prototype to directly open the design tool to draw a table structure diagram)

Then, we can increase or decrease fields based on business processes and business needs.

Finally, optimize the table structure based on the target business volume and non-functional quality attribute requirements. After this step is completed, the data table structure design is no more than ten.

Of course, during the design, we should pay attention to the scalability of the table structure, that is to say, some fields will not have a one-to-many situation. If you design one-to-one, it is possible that many obstacles will be encountered when the function will be upgraded in the future.

In addition, we should also be based on the current situation. We should not add a large number of redundant fields for future possibilities. In the end, many unused fields may exist. For python, it does not need to generate a large number of entity classes. Therefore, it is very convenient to add various fields as needed. We recommend that you add the corresponding fields at that time.

 

　　I. Table Structure Design

First, we will go to the Company Introduction page.

　　

　　

We can see that the company profile displays pictures and text information, which belongs to the information editing type, and the same is true for contact us. Therefore, you can put them in an information table for unified management, based on the prototype, we can see that the size of the pictures displayed on the homepage is different from that on the company profile page. Therefore, you need to add a cover image address field to store the image addresses displayed on the homepage, the pictures and text on the company profile page can be stored in the content field together (when displaying the content on the homepage, you can use the code to filter out the pictures in the content ), therefore, two fields are required to store the corresponding content. In addition, to facilitate the background administrator to differentiate what the content is, add a title field to describe it without front-end display.

In the previous analysis, cainiao used the data dictionary excel template provided by the boss to design the corresponding data table structure (using the postgresql database, see)

　　

PS: The primary key is required for almost every data table. Generally, auto-incrementing integer values are used. Most Distributed Data Structures with large data volumes use uuid as the primary key id. In addition, for information tables, an additional add_time will be added to store the time when this record is added.

 

Go to the Product center and product details page

　　

　　

From the Product Center page of the prototype, we can see the product category list in the left column, and the product details in the right column. Therefore, we need to store them in two tables and associate them. The product category table is used to store the category information (which requires a category Name field). It is used to manage the display of the front-end category list. It can be used to add or enable fields to facilitate the addition or removal of a series of products, batch online or offline operations (display or hide this category ).

The product information table can be added based on the Content fields displayed on the product details page, product Name, code, specifications, shelf life, origin and Product Description fields are required ). In addition to the content displayed on the page, we also need to add the product category id field to bind the product category table. When you click the product category, the background will query the corresponding product based on the category id. In addition, the cover image address field is added to display the corresponding image when the product list is displayed. Finally, whether to enable the field is used to display and hide the product.

　　

 

In addition to the above content, we also need to add an administrator management table to manage the background login users. Because the enterprise site is too small, little cainiao will be lazy and will not do this management page, haha... the subsequent stories will gradually improve the system functions.

　　

 

　　Ii. SQL code generation for Data Tables

Run exceltopostgresql.exe In the downloaded package (this is developed using C # And requires Framework 3.5 to run), select the data dictionary in the downloaded package, and enter the Excel table name Sheet1, click "run" to view the generated SQL Execution Code. (If you open the data dictionary with WPS, clicking "run" May cause no response because WPS excludes the excel file, it must be disabled before it can run normally)

　　

PS: because the software is hand-written, it is not very complete and must follow the following requirements:

The Chinese description of the first row cannot be deleted; otherwise, an error will occur during the running; a blank row is required between the table and the table; primary key columns support PK (primary key Creation), IX (index generation) and UX (create unique index). If you want to create a composite index, you can only manually add it. If you want to allow null columns, you only need to add no to add non-null restrictions. The default value column, the default time field is null. If you want to set it to now (), that is, to generate the current time, you need to set the allowed empty column to no, because now may be a keyword in excel, this default value cannot be read by the program. In addition, the default value of the text field is '', which is automatically added during generation.

 

　　3. create databases and data tables

Open pgAdmin to connect to the local postgresql database

Click

In the displayed window, enter the database name simple_db and click OK to create the database.

　　

　　

Click the created database, and then click the SQL query analyzer in the title bar.

　　

 

Paste the previously generated SQL statement in the pop-up SQL editor window, and click execute query. The data table is created.

　　

Then, clear the code in the SQL Editor, enter the following statement, and click to create the background Administrator Account to facilitate subsequent development operations.

INSERT INTO manager(login_name, login_password, is_enable) VALUES ('admin', 'E10ADC3949BA59ABBE56E057F20F883E', 1);

Now we have finished designing and creating databases. click the following link to download relevant files.

Download data dictionary and SQL statement Generation Tool

 

 

Author: AllEmpty
Source: http://www.cnblogs.com/EmptyFS/
If you are interested, you can addPython development QQ Group669058475For discussion. If you have any questions, you can ask questions in the group. Of course, if I am busy at work, I may not reply in time.

This article isAllEmptyOriginal, you are welcome to reprint, but you must keep this statement without consent, and provide a connection to the original article clearly on the Article Page, otherwise you will reserve the right to pursue legal liability.