Database Structure & Views

Introduction

Before diving into SQL Views, it's essential to understand how databases are structured and where views fit within the overall database architecture. This foundational knowledge will help you appreciate why views are such a powerful tool in SQL.

In this tutorial, you will learn:

How databases are organized at different levels
The distinction between physical and logical data storage
Where views sit in the database hierarchy
Why database structure matters for data management

Database Organization Layers

Understanding Database Layers

A database system is organized into multiple layers, each serving a specific purpose:

Generated visual representation from text diagram for Database Organization Layers.

Why Layers Matter

Layer	Purpose	Examples
User Layer	Provides customized data access	Views, Reports, APIs
Logical Layer	Organizes data into meaningful structures	Tables, Relationships, Constraints
Physical Layer	Handles actual storage mechanics	Data files, Index files, Log files

This separation allows:

Users to work with data without knowing storage details
DBAs to optimize storage without affecting applications
Developers to focus on business logic, not file systems

Three-layer database architecture showing server level, database level, and schema level with views positioned in the schema layer

Database Objects

Common Database Objects

Within a database, you'll encounter several types of objects:

Object Type	Description	Stores Data?
Tables	Primary storage structure for data	Yes - physically
Views	Virtual tables based on queries	No - derived at runtime
Indexes	Speed up data retrieval	Yes - metadata
Stored Procedures	Reusable SQL code blocks	No - code only
Triggers	Automatic actions on data events	No - code only
Functions	Return values based on inputs	No - code only

Where Views Fit

Views occupy a unique position in database architecture:

Generated visual representation from text diagram for Database Objects.

Key Insight: Views don't store data themselves. They store a query definition that produces data when accessed.

The Role of Views in Database Structure

Views as a Data Access Layer

Views act as an abstraction layer between users and the underlying tables:

Generated visual representation from text diagram for The Role of Views in Database Structure.

Benefits of This Structure

Customized Access - Each department sees only relevant data
Simplified Queries - Complex joins are hidden in view definitions
Security - Sensitive columns can be excluded from views
Consistency - Standard calculations defined once, used everywhere
Flexibility - Base tables can change without affecting view users

Practical Example

Let's explore how database structure works with our e-commerce dataset. We have several related tables that can be presented through views for different use cases.

-- Explore the structure: Tables in our database
-- Each table serves a specific purpose in the overall structure

-- 1. Customer base data
SELECT 'Customers' AS table_name, COUNT(*) AS row_count
FROM olist_customers_dataset

UNION ALL

-- 2. Order header information  
SELECT 'Orders' AS table_name, COUNT(*) AS row_count
FROM olist_orders_dataset

UNION ALL

-- 3. Order line items (products in each order)
SELECT 'Order Items' AS table_name, COUNT(*) AS row_count
FROM olist_order_items_dataset

UNION ALL

-- 4. Payment information
SELECT 'Payments' AS table_name, COUNT(*) AS row_count
FROM olist_order_payments_dataset;

Summary

Key Takeaways

Databases are layered - Physical storage, logical organization, and user access are separated
Tables store data physically - They are the foundation of data storage
Views are virtual - They store query definitions, not actual data
Views provide abstraction - They sit between users and complex table structures
Structure enables flexibility - Changes at one layer don't necessarily affect others

What's Next?

Now that you understand where views fit in database structure, let's explore DDL (Data Definition Language) - the SQL commands used to create, modify, and manage database objects including views.