Difference between Table and View

Database management is already a complex topic, but their creators have found ways to make it even more abstract: adding some virtual layers. In this article, I’ll explain the difference between tables and views, and how to use them efficiently.

As a whole, tables are a static way to keep information in a database, while views are dynamic visualizations of the result of a query on one or more tables. Views are typically used to increase performance and hide database complexity.

Keep reading if you still need more details after reading this short answer, as I’ll explain everything in the following. I’ll start with a reminder about tables, then what are views exactly to finally compare these two notions point per point.

Tables

Definition

A table is a collection of related data held in a table format within a database. It consists of columns and rows.

Wikipedia

You can imagine a table like a spreadsheet: there is one column for each field and one row for each dataset.

Field types

When creating a table, we need to define the number of fields (column), but also their types. Unlike spreadsheets, you can’t fill each field with random values, you need to define the format when creating the table. Then each new row needs to respect this format.

Common field types are:

• Strings: for field like names for example. Its possible to set the maximum size for each field.
• Numbers: they can be from different sort (integer, float) or size
• Boolean: True or false
• Dates
• etc.

Example

For example, let’s say we have a table named “cars” in a database (for a car rental service, for example).
This table will have fields (columns) like brand, model, buying date, plate number, etc.
Each line correspond to a specific car in the company, with all the information filled.

Car ID	Brand	Model	Plate
1	Tesla	Y	586 5GX
2	Ferrari	F8 Spider	861573
3	Ford	Mustang	MUS 45C

In this example, the car ID would be an integer, the brand a string (or a list) and the buying date would be another format (date for example).

Tables usage

Tables are the main component of databases, they are used to keeping information in memory in most companies.

For example, this website use tables to store each article (title, content, link, etc.), but also for back-end users (username, password, email), images files (filename, size, …) and various configuration options.

Database servers

A database server is required to create these information structures. Popular solution are tools like Oracle, MySQL or SQL Server. Each of them work slightly differently, but tables are always a critical element in building those kinds of databases.

Some of them are free, others are commercial products and may cost a lot of money. It highly depends on the requirements for each specific software.

Create a table with MySQL

You can use graphical tools like PHPMyAdmin to do this, but just to give you an example, here is how to create a basic table structure with MySQL:

CREATE TABLE cars (
cars_id INT NOT NULL AUTO_INCREMENT ,
cars_brand VARCHAR(32) NOT NULL ,
cars_model VARCHAR(32) NOT NULL ,
cars_plate VARCHAR(32) NOT NULL ,
cars_buying_date DATE NOT NULL ,
PRIMARY KEY (cars_id)
)

Views

Definition

In a database, a view is the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object.

Wikipedia

The thing to keep in mind is that a view is a dynamic result, given by a specific query. A query is a way to filter table results in a database.
If a table is a spreadsheet, a query is the filter and a view is the result of this filter.

Structure

The structure of a view is automatically defined by the query used to generate it. Each field will automatically respect the field type of the original table.

Advantages of a view

A view represents a subset of data that may come from different tables. It’s often used to improve reading performances, restrict access permissions to tables or simplify the software code to access some data.

As the result of a view is precalculated, it will generally be faster to access the data in a view than by using a standard query (which need to process the data each time).
In terms of permissions, if someone requires 3 fields from different tables, you can create a view to give them access directly, without having to see the other information or using database queries.

And finally, pre-computing data for any software usage is also a good practice in some cases. For example, you can have a developer who doesn’t necessarily know the full database but can access it through views created by the database manager. Also, in this case, any change in the database doesn’t have any impact on the software, as long as the views stay the same.

Create a view with MySQL

As an example, here is the syntax to create a basic view in MySQL:

CREATE VIEW tesla_cars AS SELECT * FROM cars WHERE cars_brand='Tesla'

This is really a basic example, but I hope you can see the idea. You’ll create your MySQL query as usual, and add the “CREATE VIEW” statement just before to keep it in your database. Each time a Tesla car is added or updated in the original table, the view will be updated automatically.

Differences between table and view in a database

As a whole, tables are standalone objects in a database, defined with columns for each field. Views are virtual objects depending on tables content.

Definition

Tables are static objects, used to store information in a database. They are represented with rows and columns. One column for each field, and one row for each object.

Views are dynamic representation of data contained in other tables. They are created by using a specific query, they represent the result of this query in real-time.

Goals

A table is used to store information in a database. Each table has a specific role and contains specific columns and data types to achieve this goal. A new row is created in this table for each new dataset.

The goals of using views can be performances improvement (result is precomputed), permissions management (only give access to specific information to specific users) or convenience for large database.

Performances

Tables have no performances issues in themselves, but running a query using several tables as a source can create waiting time on the front end for the user. By precompiling data in views, it will largely increase the reading time and so the user experience.