Project database design

preface

Database design, in the whole software engineering process, is a very important link. The preparation of the early stage is for the later development project, to smooth some. Including subsequent iterations, you need to leave the possibility of extensibility in the database table design.

The whole “bookkeeping” project has two core modules, the first is the user module, the other is the billing module. In addition, three data tables are configured. Although there are not many tables, the basic functions are involved, so there are follow-up requirements for expansion.

Principles of database design

1. Take plenty of time to design the database

Database is the mapping of user needs, designed to be user needs as the center, try to keep in touch with the user, the user here can refer to the product manager, or people who raise requirements. If the requirements are not clear, the table should be designed with flexible fields in mind. This is called “planning for a rainy day”.

2. Think more about performance and optimization

Predict in advance which data will be relatively large, for such data table structure, the design is often coarse-grained, in order to achieve the minimum table, the weakest relationship to store a large number of data.

3. Add necessary (redundant) fields

Like “creation time”, “time”, “note”, “operation user IP”, and some for other requirements, such as statistical fields, and so on, in each table must have, not to say that the only system used in data storage in the database, some redundancy field is to facilitate the maintenance, analysis, expand and add in the future, it is very important, For example, hacker attacks, tampering with data, they can be based on the modification time and the operation of the user IP to locate.

4, design reasonable table association

When the relationship between tables is complex, it is recommended to use a third mapping table to maintain the relationship between two complex tables to reduce the direct coupling degree between tables.

If multiple tables involve large amounts of data, the table structure should be as simple as possible and association should be avoided as much as possible.

Next, design the data table needed for this project.

2. New project database

1. The user table is user

The whole bookkeeping project is a C-end project for multi-person registration, so it needs a personal center module, and users can set their own head picture and personality signature. However, the user name cannot be changed, because the user name is used for login. Currently, the system does not have the concept of mobile verification code, so the user name is equivalent to the unique identification of the user. When writing the registration interface, it also checks to see if the same user name exists in the database. In this case, we need to give another creation time for users to expand service functions, such as the number of years users use the system, and registration based on time.

According to the above requirement analysis, the following user table can be designed:

• id:idField is set to an autoincrement field to avoid data duplication. Set it to increment after each incrementuserNew data in the table, will defaultidPlus 1, even if you delete the previous data, it doesn’t matteridSince the increase. After authentication, user information is generated. Billing tables are stored with the user in mind. The equivalent ofAUsers, when they store their own bills, they all putAThe user’sidWith, this bill marks the userA 的 id 。
• Username: indicates the login name of the storage user.
• Password: indicates the password for logging in to the storage user.
• signature: According to the figure above, one more is neededsignatureField as a personality signature.
• Avatar: Stores user profile picture information.
• Ctime: stores the user creation time field.

Open SQLyog, select local database localhost, and create an Accoun book for the development database of this project. Once created, create a user table under Accoun book with the same attributes as the six fields mentioned above, as follows:

The table creation process is not described here. You can view the related work of creating a table before (3). The important thing to note here is that you must set the primary key for the table, so let’s set id as the primary key.

2. Bill

Above is the billing front page, with two pieces of information at the top representing “total expenses” and “total revenue” for the month. Each bill is recorded as an item, and each bill includes several key attributes, namely, the type of bill (income or indication), the type of bill (clothing, transportation, bonus, etc.), the amount of the bill, the date and time of the bill, and the remarks on the bill (used when the type cannot be clearly described).

Based on the above requirements analysis, the bill table can be designed as follows:

• idEach table needs a primary key, so we use the same primary keyidAs an attribute of the bill table.
• pay_typeTypes of billsThere are only two types of billsexpenses and incomesWe usepay_typeAs a type field, this is the convention1For spending,2For the income.
• Every bill should have a price attribute that indicates how much you spent or earned on that bill.
• Date: indicates the date of the bill. You can select the date and store it as a timestamp.
• Type_id: indicates the id of a bill label, such as catering, transportation, daily use, learning, and shopping.
• Type_name: indicates the TAB name, such as catering, transportation, daily use, learning, and shopping.
• user_id: User to which the bill belongsid, this small volume is a multi-user project, equivalent to a number of users can register to use, so storage bills, the need to usersidTo filter out the user’s bill when querying the bill list later.
• Remark: Remarks on the bill.

Create a new bill table from the account-book table as follows:

Save and execute, the bill table takes effect.

3. Label table Type

The idea was to make tabs on the front end, such as clothing, transportation, medical, etc., into a static object for use by the front end project. But there is a downside to this, if you want to allow users to add their own custom tags, it is not easy to expand. So here we set a Type table in the database, so that users can flexibly set their own custom labels.

You need to set the following properties for the label table:

• Id: unique identifier, set as the primary key.
• Name: Label name, such as catering, transportation, daily use, learning, and shopping.
• type: Label type, default1For the income,2For spending.
• user_idBy default, 0 indicates that the label is visible to all users. The label is set by a user individually.user_idIs the user of that useridTo facilitate filtering when obtaining the list.

Create a new table type in the data account-book as follows:

Build table work, basically complete.

3. To summarize

A set of system, the most important thing is to user authentication, as long as the user authentication after implementation, around the user can open multiple modules to add and delete, is now set up a bill list to add and delete the demo, the subsequent can expand according to their own needs, you can also add a table, for example, called note table, specially for the diary record, At this point is the note table to add, delete, change and check.