My first Python Web development Framework (4)--database structure design and creation

Small white do the front-end HTML design, immediately began to enter the database structure design steps.

　　

Before the beginning, Xiao Bai recalled the boss in the company training in the database design explained:

For beginners, many get the prototype do not know how to design the structure of the data table, this is very normal thing, you can draw lessons from other people's project experience, slowly will.

After we get the prototype, we have to look carefully at what is shown in the prototype, and consider what is immutable in the content, that is, those that are written to die on the page, those that need to be changed in the background, and the records of those changes to be added to the data dictionary. The other thing to do when recording these fields is the classification, a category is a data table. (Of course, if the beginner is very bull, then you can also like some of the architecture of the normal God, without the prototype directly open the design tools to draw table structure diagram and no problem)

We then optimize the fields based on the business process and business requirements.

Finally, according to the Target business volume, and some non-functional quality attribute requirements, to optimize the table structure. After this step, the data sheet structure design is sorta.

Of course, in the design, we should pay attention to the scalability of the table structure, that is, some fields will not exist in a pair of situations, if you design a one-to-one, it is possible to follow up on the upgrade of the function will encounter a lot of obstacles.

In addition, we must be based on the current, not for the future possible things to add a lot of redundant fields, to the end there may be a lot of unused fields exist. For Python, it doesn't need to generate a lot of entity classes, so it's very handy to add a variety of fields later on as needed, and it's recommended that you add the corresponding fields when you need to.

　　First, table structure design

First we open the page with the company introduction

　　

　　

Can see the company introduction is displayed in the picture and text information, it belongs to the type of information editing, and contact us is the same, so you can put them in an information table Unified management, according to the prototype we can see the homepage and the company introduction page show the picture size is not the same, So you need to add a cover image address field for the first page to display the image address, and the company introduction page picture and content text can be stored together in the content field (the first page to display content, you can use the code to filter out the content of the picture), So here are two fields to store the corresponding content. In addition, in order to facilitate the background administrator to distinguish what this is the record, so add a title field to explain, not to do front-end display.

The previous analysis, the small rookie use the boss to the data dictionary Excel template, design the corresponding data table structure (using the PostgreSQL database, see)

　　

PS: The primary key almost every data table is necessary, generally using the self-increment type of shaping values, for the larger data volume of the distributed data structure, most will use the UUID as the primary key ID, and for the information table, there will be an additional add_time to store this record added time

Open 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, so we need to divide the two tables to store them and correlate them together. The Product classification table is used to store the classification information (need to have the category Name field ), to manage the front-end category list display, can increase whether the field is enabled , to facilitate the new or lower shelf of a series of products, bulk online or offline operation (show or hide the category).

The product information sheet can be added according to the Content field shown on the Product Details page (as can be seen on the Product Details page, product name, coding, specifications, shelf life, origin and Product description fields ). In addition to the page display content, we also need to add product Category ID field , used to bind the product classification table, used to click on product Classification, the background according to the classification ID to query the corresponding product. Also add the cover picture address field to display the corresponding picture when displaying the product list. whether the field is finally enabled is also used to show and hide the product.

　　

In addition to the above content, we also need to add an Administrator management table, used to manage the background login users, because this enterprise station is too small, so small rookie lazy do not do this management page, haha ... The later story slowly expands and gradually improves the system function.

　　

　　Second, data table SQL code generation

Run the ExcelToPostgreSql.exe in the download package (this is developed in C #, requires the framework 3.5 to run), select the data dictionary in the download package, and then fill in the Excel table name Sheet1. Click Run to see the generated SQL execution code (if you open the data dictionary with WPS, the point run may be unresponsive, because WPS exclusive Excel files, need to shut down before the normal operation)

　　

PS: Because this software is readily written out, so not very perfect, you must follow the following requirements.

The Chinese description of the first column cannot be deleted, otherwise the run-time error will be a blank line between the table and the table, the primary key column supports PK (create primary key), IX (build index) and UX (create unique index) three, if you want to create a composite index, can only be added manually; Allow empty columns, add no to empty limit The default Value column, the default time Type field is null value, if you want to set to now (), that is, to generate the current time, you need to allow the empty column to be set to No is possible, because now may be the keyword of Excel, this default program can not read, and the text type fields default value ", is added automatically when it is generated.

　　Iii. creating databases and data tables

Open pgadmin to connect to the local PostgreSQL database

Click

In the pop-up window, enter the database name: simple_db, click OK to complete the database creation.

　　

　　

Then click on the database you just created, then click on the title bar of SQL Query Analyzer

　　

Paste the generated SQL statement in the pop-up SQL Editor window and click Execute Query, and the data sheet is created.

　　

Then empty the SQL Editor code, enter the following statement, click on the execution to create a background administrator account, easy to follow the development operation

INSERT  into VALUES ('admin' 'e10adc3949ba59abbe56e057f20f883e'1 );

To complete the database design and creation work here, click the link below to download the relevant documents

　　Download data dictionary with SQL statement Generation tool

Allempty
Source: http://www.cnblogs.com/EmptyFS/
Interested friends can gaga python development QQ Group : 669058475 , we discuss together. If you have a problem, you can ask questions in the group, of course, I usually work is very busy not necessarily timely reply.

This article is allempty original, welcome reprint, but without consent must retain this paragraph statement, and in the article page obvious location to the original link, otherwise reserve the right to pursue legal responsibility.

My first Python Web development Framework (4)--database structure design and creation