Table vs. View — The unnoticed database wrestling match

Published in

Women in Technology

5 min readSep 17, 2023

Are you looking to enter the database world? Beware of the fierce competition that often goes unnoticed. Two intimidating opponents are at play, both with their unique set of strengths and weaknesses. Make sure you’re well-equipped to navigate this challenging landscape because every decision can significantly impact data management, efficiency, and accessibility.

So what are we waiting for? Let’s witness the powerful display of data dominance in this battle, each of our fighters showcasing its remarkable abilities and strategies.

Meet our fighters

Like in professional wrestling, our fighters are eager to prove their dominance and establish who can pave the way to a great database structure, so allow me to introduce them.

(Clears throat 🥁🎤)

Ladies and gentlemen, in the left corner we have the SQL Table, the heavyweight champion of raw data storage, the invincible structured foundation of any relational database. Robust and reliable it can handle massive volumes of data with ease. It’s not without reason that it’s often referred to as The backbone of any application.

In the other corner, we have the SQL View, the most agile and strategic rival. Even though they aren’t meant for storing data, they provide a dynamic way to get a visual representation from one or more tables. It allows users to query and manipulate data without any traces on the main tables. It represents a valuable rival to tables regarding the support it ensures for reporting and simplifying the most complex queries.

Know each of them better

What is a Table?

In any DBMS, tables serve as the primary database objects used to store and organize data in a structured format. Think of them as containers for data.

A table is essentially a combination of rows and columns. Each column represents an attribute, while each row represents a set of values for those attributes. When naming your tables, it’s crucial to exercise care, as they must have unique names and should not employ any SQL-reserved words

Some key characteristics are:

Structure: Data is organized in a structured format using rows and columns
Primary key: A table can have one or multiple columns that are designated as the primary key.
Relationships: In relational databases, the tables are related to one another through a relationship that is created based on the keys (primary and foreign keys).
Constraints: Various types of constraints can be applied to tables to enforce rules on data integrity. The most used ones are Check and Default constraints.
Indexes: We already know that indexes are optional database structures used to enhance the performance of queries and are created on one or more columns based on the needs.

What is a View?

A view is known as a virtual table that has no data of its own but is derived from one or more tables (it represents a stored SQL query’s result set). It’s like a ‘window’ through which we can see the needed records, without storing a separate copy of the data.

Acknowledge the difference between them

In an interview, I was asked what is the difference between a view and a table. For many of us is easier to give an example, but there are cases when the employer wants a kind of definition.

A view is a virtual table that is based on the result of an SQL query and tables, on the other hand, are database structures used for to store data.

They might seem quite similar, especially when it comes to retrieving data, but there subtle differences, particularly concerning storage, dependency, and many others.

The secret ability of each fighter

Just like in the real world, each fighter has some secret skills and strategies that help him be the winner of the match; the same thing applies here, so let’s see what they bring to the ring.

Table

There are some cases when you’ll decide to go on with a table such as:

Data storage: Need to store raw data? Then a table is the perfect approach for this. It will help you store your data in a stunning structured format.
Data manipulation: As we know there will be changes to the records you already have, so you’ll have to use the famous DML operations (insert, update, delete).
Data integrity and accuracy: No one likes dirty database records, so to prevent this we use constraints to “force” the database to accept or set default values.
Indexing: In our fast-paced era, we’re accustomed to having information at our fingertips. That’s why we implement indexes in our tables, as this approach optimizes data retrieval and boosts query performance.

View

If we’re worried that our View won’t be able to make a move, well, here’s its strategy:

Data Transformation: when an analysis is required, it’s almost impossible to avoid joining tables and applying different aggregations to the data. So when you need a new “table” but with aggregation applied on various fields and filters, a view is the best approach possible.
Data abstraction: Let’s assume you need to provide a simplified view of the data to a specific department, so you can give them a comprehensive view that contains all the data they requested.
Query reusability: have you found yourself in that situation when you need to write a query multiple times because someone keeps asking you for a specific set of data? Now you can create a view based on the query you used multiple times to avoid the annoying colleague and their boring request. Tell them to do a basic SELECT on it; the problem is solved and everyone is happy😁.
Security: all of us have that overly curious colleague who wants to know everything (and probably mess everything up), even though is not their business; in such cases, a view can hold a bit of his/her horses by providing only the rows and columns that are relevant to them.

Conclusion

As you’ve seen in this article, both of our fighters have their strategy and capabilities when it comes to data. Throughout your data-driven journey, you’ll encounter situations where you’ll utilize both tables and views and in those cases, the match will end in a tie.

Remember, the choice of the winner in your case is highly related to your needs and objectives.

Thank you so much for your support, it means a lot to me😊