What are the advantages and disadvantages of using denormalized or normalized data schemas for scalability?

Denormalized data schemas offer scalability advantages by simplifying read operations, reducing joins, and enhancing query performance. However, they may introduce data redundancy and potential inconsistencies, requiring careful management. In contrast, normalized schemas ensure data integrity and ease of updates, which can support scalability. Yet, they may lead to slower read operations due to complex joins, hindering scalability, especially in large systems. Balancing these trade-offs is crucial for optimizing scalability in database design.

A normalização é um processo essencial, tanto para melhoria de performance quanto para otimização de espaço de armazenamento nos bancos de dados. Até mesmo quando surge a necessidade de obter os dados de forma desnormalizada, uma estrutura normalizada facilita a construção de visões mais precisas e com mais velocidade.

Normalization is a common practice in database design to reduce redundancies and ensure data integrity. It allows data to be organized efficiently, minimizing anomalies in updates, inserts, and deletes.

A normalized data schema organizes data into related tables to minimize redundancy and dependency. Each table typically holds data about a single entity, with relationships between tables established through foreign keys. For example, a customer database might have separate tables for customer details, orders, and products, with foreign keys linking the related records.

Denormalized schemas offer scope for scalability as they have all fields in a single table structure offering scope to breakdown further into smaller tables at lower level of granularity and non transitive dependency.On the other hand, normalisation already has non duplication,non transitive dependency and multiple tables making it difficult to further scale.

Denormalization can lead to problems of redundancy and consistency, especially in environments where frequent updates are necessary. One possible solution for this would be the implementation of indexing techniques to optimize reading, while concurrency control strategies could be adopted to maintain data consistency. Tools such as Redis for caching, Elasticsearch for efficient search, and PostgreSQL with advanced indexing and concurrency control techniques can be applied to address these issues in denormalized schemas, providing a balance between performance and consistency.

It is uncommon to use denormalized data schemas as they deviate from the standard normalization process. However, in the SAP ERP industry, denormalization is utilized as an advanced technique to address scalability issues. Relational databases were being pushed to their limits when greater scalability was needed in the area of Finance and Controlling. SAP HANA's in-memory database allowed for denormalization to be used making it possible to create a massive table like ACDOCA with 500+ columns, in the end enabling optimized performance and real-time reporting on transactional data.

A denormalized data schema reduces the number of tables, often combining related entities into a single table to reduce the need for complex joins. This approach increases redundancy but can simplify queries and improve read performance. For instance, a denormalized customer database might store customer details, orders, and products in a single table, reducing the need for joins.

Normalization has several advantages, including reduced data redundancy, improved data integrity, and easier maintenance. By storing data in separate, related tables, you avoid duplicate entries and ensure that updates to data only need to be made in one place. For example, updating a customer's address in a normalized schema requires changing it in only one table, reducing the risk of inconsistencies.

The main disadvantage of normalization is the impact on querying the data due to the need for complex joins between tables. This can be particularly problematic in large databases. For instance, retrieving all details of a customer and their orders might require joining three or more tables, leading to slower query performance.

In the SAP ERP industry, denormalization is utilized as an advanced technique to address scalability issues. In the area of Finance and Controlling, denormalization is used to create a massive table like ACDOCA with 500+ columns enabling optimized performance and real-time reporting on transactional data. Here we can see a clear advantage of denormalization.

Denormalization can improve read performance by reducing the need for joins, making it suitable for applications where quick read access is critical. For example, a denormalized schema can speed up report generation by storing all relevant data in a single table, eliminating the need for time-consuming joins.