登录查看更多内容

Last updated on 2024年8月18日

全部
RDBMS

How do you decide when to use a view or a table in your database design?

由人工智能和领英社区提供技术支持

Views and tables are two common components of a relational database management system (RDBMS). They both store and display data, but they have different characteristics and purposes. In this article, you will learn how to decide when to use a view or a table in your database design, based on some key factors and scenarios.

此文章中的业界达人

由社区从 9 条内容中精选。了解更多

Pranav Kumar

Pursuing B.tech in Artificial Intelligence and Machine Learning ||Looking for Internship Opportunities|| MERN Stack…

1 What is a view?

A view is a virtual table that shows the result of a query. It does not store any data physically, but rather retrieves it from one or more underlying tables when accessed. A view can simplify complex queries, hide sensitive data, provide consistent formatting, and enhance performance.

添加您的观点

Pranav Kumar

Pursuing B.tech in Artificial Intelligence and Machine Learning ||Looking for Internship Opportunities|| MERN Stack Developer || Codes in ( C/ C++/Java) || DSA || Machine Learning (Python)
举报内容
Deciding whether to use a view or a table in your database design depends on the specific requirements of your application and the nature of the data you're dealing with. Here are some factors to consider when making this decision: 1.Purpose and Usage: Tables: Use tables to store and manage the actual data in your database. Views: Use views to create virtual tables based on the results of a SELECT query. 2.Data Complexity: Tables: Use tables for storing raw data in its original form. Tables are suitable for storing complex data structures with many columns and relationships. Views: Use views to simplify complex data structures or to present data in a more understandable format.

已翻译

赞
Surajit Das

Serving Notice Period | Database Engineer | RDBMS Development, Data Migration, Data Warehousing, Visualisation | Oracle, PostgreSQL Database | Tableau | AWS S3
举报内容
Views are typically read-only tables that provide a virtual representation of data. You can perform actions based on your requirements without directly manipulating the underlying data or table structure. This can be helpful when data needs to be presented in a specific format. Views' performance can vary. They can be faster than querying underlying tables for smaller datasets, but joining multiple views can sometimes impact performance. Additionally, repeatedly calling a view can also introduce overhead. It's essential to consider these factors when deciding whether to use views in your database design.

已翻译

赞

加载更多内容

2 What is a table?

A table is a physical structure that stores data in rows and columns. It is the basic unit of a relational database, and it can have constraints, indexes, triggers, and other features to ensure data integrity and efficiency. A table can be created, modified, deleted, and queried directly.

添加您的观点

Surajit Das

Serving Notice Period | Database Engineer | RDBMS Development, Data Migration, Data Warehousing, Visualisation | Oracle, PostgreSQL Database | Tableau | AWS S3
举报内容
Tables are the fundamental building blocks of a database, storing actual data in a structured format with columns and corresponding data types. Directly querying tables can often be faster than querying views, as you're working with the raw data. However, complex queries involving multiple tables or large datasets can still impact performance. CRUD (Create, Read, Update, Delete) operations directly affect the data within tables. Proper table design and indexing are crucial for maintaining data integrity and efficient query execution.

已翻译

赞

加载更多内容

3 Benefits of using views

Using views can bring several benefits to your database design. Abstraction allows for creating views that present only the relevant data for a specific task or user, without exposing the underlying tables or queries, making your database more modular, secure, and easy to maintain. Reusability enables you to reuse the same view for multiple purposes, without having to write the same query multiple times. This can save time and reduce errors. Moreover, optimization enables you to create views that perform complex calculations or aggregations and cache the results for faster retrieval. This can improve the performance of your database, especially if the underlying tables are large or frequently updated.

添加您的观点

加载更多内容

4 Benefits of using tables

Using tables can provide several advantages for your database design, such as flexibility, persistence, and functionality. With tables, you can store any type of data you need and modify them as your requirements change. You can also add, update, or delete data directly from tables without affecting other components of your database. Moreover, you can store data permanently in tables and access it anytime, even if the source of the data changes or becomes unavailable. This can guarantee the reliability and availability of your data. Tables can also be used to implement various features and functions of your database, such as constraints, indexes, triggers, foreign keys, and joins. These can help ensure data quality, consistency, and relationships.

添加您的观点

加载更多内容

5 When to use views

When designing a database, you should use views to simplify complex queries, protect sensitive data, provide consistent formatting, and improve performance. Views can also be used to hide query details from users or applications, as well as restrict access to certain columns or rows. By caching the results of expensive queries, views can significantly enhance the performance of your database.

添加您的观点

加载更多内容

6 When to use tables

Tables are an important part of database design when you need to store data that is not derived from other sources or tables, or that needs to be modified frequently. Tables also ensure the persistence and availability of your data, regardless of the changes or status of the source data. Additionally, tables give you more control and flexibility over your data structure and design, as well as access to various features and functions to manage and manipulate your data.

添加您的观点

7 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?

添加您的观点

Bryan Hall

Data Engineer | AI | Spatial | IOT | Electronics
举报内容
Simple views are just named pre-defined select statements. They can be used to present select data from various tables in an easy to reference format for applications, and sometimes to enforce security for sensitive table columns. Materialized views (Oracle, with similar concepts in many other databases) instead persist (materialize) data outside the tables (or underlying sub views). This allows for much better performance for complex view queries while greatly reducing the load on a database.

已翻译

赞

RDBMS

+ 关注

给文章评分

我们借助人工智能创建了此文章。您认为这篇文章怎么样？

很棒不太好

举报此文章

How do you decide when to use a view or a table in your database design?

1

2

3

4

5

6

7

1 What is a view?

2 What is a table?

3 Benefits of using views

4 Benefits of using tables

5 When to use views

6 When to use tables

7 Here’s what else to consider

RDBMS

给文章评分

感谢您的反馈

更多相关阅读内容