登录查看更多内容

How can you create a query-friendly database?

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

Creating a query-friendly database is not only a matter of performance, but also of accuracy, maintainability, and scalability. A query-friendly database is one that allows you to retrieve and manipulate data efficiently and reliably, without compromising the integrity and quality of the information. In this article, you will learn some basic principles and best practices for designing and developing a query-friendly database.

此文章中的业界达人

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

1 Choose the right data model

The first step to create a query-friendly database is to choose the right data model for your data and your business needs. A data model is a representation of how data is organized, structured, and related in a database. There are different types of data models, such as relational, hierarchical, network, object-oriented, document, graph, and so on. Each one has its own advantages and disadvantages, depending on the nature and complexity of your data. For example, a relational data model is suitable for structured and normalized data, while a document data model is more flexible and scalable for semi-structured and heterogeneous data. You should choose the data model that best fits your data characteristics, query patterns, and performance requirements.

添加您的观点

Ervin Weber

Makes databases and clouds more efficient. #gcp #postgresql
(已编辑)
举报内容
As technology evolves, borders between data models supported by database engine get blury. - Modern relational database, can have first-class support for unstructured data in form of JSON/JSONB - document databases can support ACID transactions (Google Firestore for example) - while traditionally it was common that document databases are better at horizontal scaling, there are now also relational databases that do so as well (Google Cloud Spanner as an example). - be sure to plan that requirements change in the future, your model needs to adapt to reality, maybe you will need to be able to use multiple models over time.

已翻译

赞

2 Follow the normalization rules

Normalization is a process of organizing data in a database to avoid redundancy, inconsistency, and anomalies. Normalization involves applying a set of rules or principles to divide data into smaller and simpler tables, and to establish relationships among them using primary and foreign keys. The main benefits of normalization are that it reduces the storage space, improves the data quality, and facilitates the data manipulation. However, normalization also has some drawbacks, such as increased complexity, reduced performance, and potential loss of information. Therefore, you should balance the level of normalization with the query efficiency and the data integrity. Generally, you should aim for the third normal form (3NF), which ensures that each table has a single purpose, each column has a single value, and each value depends only on the primary key.

添加您的观点

Léo Florea

Founder - Lead Developer @ Claireity consulting | Information Security, Data compliance and Cloud infrastructure
举报内容
With the rise of NoSQL databases like MongoDB or Cassandra, the strict rules of normalisation don't always apply. These databases are designed for scalability and can handle denormalised data more efficiently in certain use cases. That being said, as with many things in database design, normalisation is a balancing act, and still crucial because it helps in designing efficient data models, even in NoSQL environments. While it's essential to avoid redundancy and ensure data integrity, it's equally important to ensure that the database meets the performance of the application. Regularly monitoring query performance and understanding the specific needs of your application will guide decisions about when to normalise or denormalise data.

已翻译

赞
Ervin Weber

Makes databases and clouds more efficient. #gcp #postgresql
举报内容
As normalization is tradeoff between storage/consistency and speed, you may want to use views defined in database to simplify queries. - In modern database engines simple views are also 'writable'. This means they can be used as regular tables and will automatically propagate changes to normalized tables used in view definition. - Materilalized view can act as fast cache. This allows you to pick "cache expiry problem" and get both speed and accurate denormalized data in return. sometimes have best of both and introduce writable or even materialized views. Doing so allows to switch to having "cache expiry" problem which in many cases can be prefered for speed.

已翻译

赞

3 Use appropriate data types and constraints

Another important aspect of creating a query-friendly database is to use appropriate data types and constraints for your data. Data types define the format, size, and range of values that can be stored in a column. Constraints define the rules or restrictions that apply to the data in a column or a table. Using appropriate data types and constraints can help you improve the data quality, the query performance, and the data security. For example, you should use numeric data types for numbers, date and time data types for dates and times, and text or binary data types for strings or files. You should also use constraints such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT to enforce the data validity, uniqueness, referential integrity, and consistency.

添加您的观点

Gemechis Tolessa

Business Architecture Analyst | Reporting and Analytics Analyst Equinix |
举报内容
Also think about making sure that your database is scalable. This means be consistent in your naming structure as to not cause confusion as new data is flowing in. Be consistent in naming of primary and foreign keys, the reason why is this can leading to data error if incorrect unique IDs are used during joins.

已翻译

赞
Ervin Weber

Makes databases and clouds more efficient. #gcp #postgresql
举报内容
Be bold in defining custom types if your database engine allows it. Not only you can have business domain terms in your database that way, but also move type conversions and validations to be part of type itself.

已翻译

赞

4 Index your data wisely

Indexing is an effective technique to create and maintain a structure that allows for faster and easier data access and searching. By storing a subset of data in a table with a pointer to the original location, indexing can significantly improve query performance, particularly for large and frequently accessed tables. Still, it comes with some costs, such as increased storage space, slower update speed, and extra maintenance. Therefore, you should index your data thoughtfully, taking into account the size and frequency of your data, the complexity of your queries, and the trade-off between speed and space. When deciding which columns to index, focus on those that are used often and have high selectivity (many distinct values). For complex queries, use the appropriate type of index (clustered, non-clustered, composite, or full-text). Additionally, monitor and evaluate the performance and storage of your indexes. Consider using index optimization techniques such as rebuilding, reorganizing or dropping unused or redundant indexes.

添加您的观点

Ervin Weber

Makes databases and clouds more efficient. #gcp #postgresql
举报内容
- Indexes are tradeoff between write speed and read speed. - update that does not change indexed columns will be faster than update that changes indexed columns - in many cases, you can have multi-column index. and database engine can use it for queries that use first columns of such index - when you index column in relational database, not all rows have to be included in the index. - in big databases, you may want to use indexes which do not return exact rows but greatly reduce search space. Example: Block Range Index (BRIN) divides data into chunks, and then only storesthe minimum and maximum values for whole chunk. if your data is ordered, or has strong correlation - this will be very effective way to reduce IO for search.

已翻译

赞

5 Document and test your database

The last step to create a query-friendly database is to document and test your database. Documentation is a process of creating and maintaining a written description of your database, including its purpose, structure, data model, data dictionary, constraints, indexes, queries, and so on. Documentation can help you and others understand, use, and maintain your database. Testing is a process of verifying and validating your database, including its functionality, performance, security, and reliability. Testing can help you and others identify and fix any errors, bugs, or issues in your database. You should document and test your database throughout the development cycle, and use tools and methods such as comments, diagrams, metadata, queries, scripts, reports, and so on.

添加您的观点

6 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?

添加您的观点

Ervin Weber

Makes databases and clouds more efficient. #gcp #postgresql
举报内容
Query friendly database is never completed, as database grows, data patterns and distributions will emerge and affect indexing meaning that often you will need to re-architect data to account for new reality. - be flexible, often you will want to have some "interface" or "API" for data fetching, and be able to refactor all the actual storage behind it, completely transparently to callers

已翻译

赞

Database Administration

+ 关注

给文章评分

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

很棒不太好

举报此文章

查看全部

How can you create a query-friendly database?

1

2

3

4

5

6

1 Choose the right data model

2 Follow the normalization rules

3 Use appropriate data types and constraints

4 Index your data wisely

5 Document and test your database

6 Here’s what else to consider

Database Administration

给文章评分

感谢您的反馈

更多Database Administration相关文章

更多相关阅读内容

How can you create a query-friendly database?

1

2

3

4

5

6

1 Choose the right data model

2 Follow the normalization rules

3 Use appropriate data types and constraints

4 Index your data wisely

5 Document and test your database

6 Here’s what else to consider

Database Administration

给文章评分

感谢您的反馈

查看其他技能