Introduction
Recently, I’ve been working more with Supabase and SQL-related tasks, which honestly are not my strongest area.
Especially when it comes to views, I realized my understanding was lacking. I’ve been doing some research lately, so I decided to write this article as a personal memo and beginner-friendly explanation of tables and views.
This may cover very basic content, but if you’re new to databases or just curious about how tables and views work, I hope this will be useful.
Explaining Tables and Views
First, let’s summarize the concepts of tables and views.
Table
- A container that stores data in a database
- Consists of rows and columns, and supports adding, updating, and deleting data
- Examples of tools/systems: Excel sheets, SQL database tables (MySQL, PostgreSQL, etc.)
- Example use cases: customer lists, product inventory tables
View
- A virtual table that stores the result of a query (
SELECTstatement) on a table - Does not hold actual data; each time you access it, the underlying table is queried
- Useful for showing only specific columns, or reusing complex joins and queries
- Commonly available in SQL databases (MySQL, PostgreSQL, SQL Server, etc.), often referred to as a “saved query” or “virtual table.” Similar concepts exist in MongoDB or BI tools as well.
Key Differences
| Item | Table | View |
|---|---|---|
| Data Store | Yes (stores actual data) | No (definition only) |
| Updates | Possible | Not by default (sometimes yes) |
| Use Case | Data storage | Simplifying or customizing views |
Diner Analogy 🍔
The explanation above may feel a bit abstract, so let’s use an analogy. Imagine you’re in a diner.
- 🗄️ : A table in a database is like the diner’s refrigerator.
- Inside are hamburger patties, lettuce, cheese, tomatoes—actual ingredients (data) that can be used.
- You can add new ingredients (
INSERT), throw out expired ones (DELETE), or update the stock (UPDATE).
- 📁 : A view is like the menu that customers look at when ordering.
For example:
- Cheeseburger = bun + patty + cheese
- Breakfast meal = pancakes + sunny-side-up eggs + bacon + coffee
The menu shows how items are combined, but the menu itself does not contain the actual food.
Each time an order comes in, the refrigerator (table) provides the ingredients, the cook prepares them (SQL execution), and the dish is served.
In short: the real data is in the refrigerator (table), and the menu (view) just defines how it’s presented.
Tip 1: What is a Query?
In databases, you often hear the word query. A query is simply an instruction or question to the database. In SQL, queries are commands like SELECT, INSERT, UPDATE, and DELETE.
Using the diner analogy:
- A query is like an order/request to the waiter.
Examples:
- “Show me all customers living in Tokyo” →
SELECT * FROM customers WHERE city='Tokyo'; - “Add product A to the inventory” →
INSERT INTO inventory ...
Tip 2: What is RLS?
RLS (Row Level Security) is a mechanism often used when multiple users share the same table. It restricts which rows (records) a user can view or manipulate, ensuring secure data management. Using the diner analogy, think of it as different customers having access to different menus:
- Regular customers → can only order from the standard menu (burgers, sandwiches, etc.)
- Children → cannot order alcohol
- Regulars → can order secret menu items (special shakes or limited burgers)
- Manager → can access everything, even test unreleased menu items
- Roles for food preparation:
- Part-time staff → can take orders but cannot change recipes or discard food
- Manager → can modify recipes (
UPDATE) or remove menu items (DELETE)
So, while the refrigerator (table data) is the same for everyone,
what each customer can order (which rows they can view or manipulate) is controlled by rules.
That’s the essence of RLS.
When Should You Use Views?
So when should you actually use views? Of course, it depends on the situation, but here are some common cases:
When you only want to expose certain columns or rows
→ For example, limiting what end users can see by publishing only part of the table.When you need to hide developer-only metadata
→ Internal columns (like internal IDs) that should not be exposed to end users can be excluded in a view.When you don’t want to write complex queries every time
→ Predefine views for common queries, so you can reuse them easily without repeating long SQL statements.
Conclusion
In this article, we looked at the difference between tables and views, along with related concepts like queries and RLS, using a diner analogy.
- Table = a container for storing data
- View = a virtual table that defines how data is presented
Although these are basic concepts, they are fundamental in actual development and operations.
Start by getting the intuition through simple analogies, then try running queries in SQL or tools like Supabase to deepen your understanding.
This time I focused on the concepts, but I may write a follow-up covering actual SQL code and syntax in detail (no promises though 😅).
Thank you for reading!
Top comments (0)