What are the benefits of creating a view in a database?

Views are the concepts that are used in most database development. There are roughly two reasons to use a view.

1. Package Query

Although the database can store large amounts of data, it is not possible to create a data table for each relationship on the datasheet design. For example, for student tables, students ' information is stored, and students ' attributes include information such as student number, name, age, home address, and so on, and the Student score table only stores information such as student number, subject, grade, etc. Now that you need to get the student's name and score information, you need to create a relationship that needs to include the student's name, subject, and score. However, it is inappropriate to create a new data table for the relationship and populate it with actual information in case the query is used. Because of this, it is obvious that the data in the database is heavily redundant.

Views are the best strategy for solving this problem. Because views can store query definitions (or relational operations), once a view is used to store a query definition, it is as if a new relationship is stored. Users can perform various operations directly on the relationships stored in the view, just as if they were facing a real data table.

2. Flexible control of security

A data table may contain many columns. However, the information for these columns may be different for users of different roles. For example, in the employee table, there may be employee worker number, employee name, employee age, employee position, employee's home address, employee social relationship, and other information. For ordinary users (such as regular employees), it may be necessary to access the employee table to view information about the employee's name, position, and so on for a work number, and not to view information such as home address, social relations, etc. for advanced users, such as personnel managers, you need to follow all the information. So, this involves the security of the data table.

This strategy can be implemented flexibly with views. For example, you can first create a view named Vw_employees. The query for this view is defined as 3 columns, such as employee work number, employee name, employee position, and so on in the employee table. This is equivalent to in the employee relationship, the projection operation, that is, the selection of employee work number, employee name, employee position 3 attributes, forming a new relationship, 1 shows.




Figure 1 Using projection operations to get a view of ordinary users Vw_employees
Similarly, for advanced users, you can create a view named Vw_employees_hr, which selects all the columns in the Employee table, as shown in 2.

Figure 2 Using projection operations to get the view vw_employees_hr for HR users

Then, for the two roles, assign two views of the query permissions, and the actual data table employees isolation, so as to control the security of data access.

What are the benefits of creating a view in a database?