As part of the process of normalizing database tables, redundant columns are extracted from higher level tables into separate subsidiary ones. This often occurs due to some fields having a one to many relationship with the parent entity. For example, take the following model that was generated using Navicat Data Modeler:
Appraisals were initially part of the ups table, but this led to data redundancy because there can be multiple vehicles appraised in one visit. Therefore, it made sense to remove the vehicle fields from the ups table and place them in their own table.
The drawback to normalization to third normal form (3NF) is that you wind up with a lot of ID fields in the main table. As a database practitioner looking at a table, it becomes very challenging to know what entity each ID column points to. As an illustration, take a look at the ups table from the above model diagram, and notice how the CSRs, customers, and vehicles have all been reduced to numeric IDs that don't help identify the underlying entities in any way:
This is partially related to the use of auto-incrementing IDs as well as normalization, but, in any event, we can make the data much easier to read by creating a view. A database view is a subset of a database and is based on a query that runs on one or more database tables. Database views are saved in the database as named queries and can be used to save frequently used, complex queries.
In Navicat 16, we can create a new view by choosing File -> New -> View... from the main menu:
That will add a new view tab.
The next step is to add the SQL statement that will generate the view fields:
If you need any help in writing your statement, there are Preview, Explain, View Builder, and Beautify SQL buttons on the tab toolbar.
Let's say we don't want to wait for the view to be created before viewing the results, we can click the Preview button to see it now:
Now the ID columns contain more descriptive - and meaningful - textual data .
Under the tab buttons, there are three more tabs - Definition, Advanced, and SQL Preview. The Advanced tab contains additional options such as the Algorithm, Definer, Security, and Check option, while the SQL Preview shows the generated CREATE VIEW statement:
The new view is named `Untitled` until we save it. At that point, a dialog appears in which we can specify the View Name:
Upon saving, the new view will be added to the Navigation Pane on the left-hand side and may be summoned at any time:
Final Thoughts on Creating Views in Navicat 16
In today's blog, we learned about database views and went through the process of making one to help identify records in a table that links to a number of dependent tables via ID fields.
