Database Design and Modeling with PostgreSQL and MySQL: Build efficient and scalable databases for modern applications using open source databases [1 ed.] 9781803233475, 9781804611456, 9781803248349

Table of contents :
Database Design and Modeling with PostgreSQL and MySQL
Foreword
Contributors
About the authors
About the reviewers
Preface
Who this book is for
What this book covers
To get the most out of this book
Download the example code files
Conventions used
Get in touch
Share Your Thoughts
Download a free PDF copy of this book
Part 1: Introduction to Databases
1
SQL and NoSQL Databases: Characteristics, Design, and Trade-Offs
Understanding databases and data models
Exploring the relational data model (SQL databases)
Tables, rows, and columns
Normalization
Structured Query Language (SQL)
ACID transactions
Navigating the document data model (NoSQL databases)
Data models in NoSQL
Types of NoSQL databases
Key-value stores
Document stores
Column-family stores
Graph databases
Applying the CAP theorem and NoSQL design choices
Consistency
Availability
Partition tolerance
Consistency models in NoSQL databases
NoSQL design choices and use cases
Managing transaction management and concurrency control in NoSQL
BASE transactions in NoSQL databases
Reasons for the BASE model in NoSQL databases
Implications for data integrity and concurrency control
Analyzing the advantages and disadvantages of NoSQL databases
Advantages of NoSQL databases
Disadvantages of NoSQL databases
Summary
2
Building a Strong Foundation for Database Design
The importance of a solid foundation in database design
Key terms and data models
Understanding the relational model in detail
Identifying entities, attributes, and relationships in database design
Creating an ER diagram
Advanced database design
Normalization – reducing redundancy and improving data integrity
Achieving normal forms – 1NF, 2NF, and 3NF
Ensuring database validity and integrity
Concluding thoughts
Summary
Part 2: Practical Implementation
3
Getting Your Hands Dirty with PostgreSQL and MySQL
Understanding the sample database
EDA and preprocessing
Database schema
MySQL
Concluding thoughts
Summary
Part 3: Core Concepts in Database Design
4
Mastering the Building Blocks of Database Design and Modeling
Understanding database objects
Understanding data types and constraints
Keys and how to use them
Database checks and constraints
Check constraint
Default constraint
Not null constraint
Checking data quality with constraints
No date in the future
Value within a specific range
How to avoid redundancy
Database consistency and beyond
Transactions – ensuring data integrity
Concurrency control – managing multiple users
PostgreSQL
MySQL
Summary
Part 4: Advanced Database Techniques
5
Advanced Techniques for Advanced Databases
Creating custom views of your data
Understanding the purpose of views
The advantages of using views in database management
Indexing – how to find data faster
Understanding indexing
Types of indexes
How indexing works
PostgreSQL indexes
MySQL indexes
Stored procedures – reusable code for your database
UDFs
The essence of UDFs
Efficiency through reusability – the role of UDFs in code optimization
UDFs and performance optimization in database operations
Using UDFs to enrich SQL queries for expressive interactions
Practical insights into UDFs in MySQL and PostgreSQL
Understanding CTEs
The role of CTEs in code modularity
Components and integration of CTEs
Guidelines for efficient CTE usage
Advanced strategies with CTEs
Case studies in action – real-world examples of CTE transformations
Summary
6
Understanding Database Scalability
Introducing database scaling
Challenges in database scaling
Primary methods of database scaling
Vitess – a horizontal scaling solution for MySQL
Citus – a horizontal scaling solution for PostgreSQL
Vertical scaling – enhancing capacity within existing infrastructure
InnoDB Cluster for MySQL
Sharding and resharding
Future trends and emerging challenges in database scaling
Serverless databases
Summary
Part 5: Best Practices and Future Trends
7
Best Practices for Building and Maintaining Your Database
Designing for performance – optimizing database efficiency
Schema design
Query optimization
Advanced query optimization techniques
Using subqueries and JOIN clauses wisely
Utilizing temporary tables
Optimizing aggregates and GROUP BY clauses
Using an index to assist GROUP BY clauses
Iterative process and analysis
Understanding database scaling
Vertical scaling
Horizontal scaling
Replication in PostgreSQL
MySQL replication
Ensuring database security
Access control
Implementing robust authentication mechanisms
Permission models
PostgreSQL permission model
Roles and privileges
Example setup in PostgreSQL
MySQL permission model
Privileges and levels
Example setup in MySQL
Management and flexibility
Exploring data encryption
Protecting data at rest
Protecting data in transit
Monitoring and auditing
Data quality and governance – maintaining high standards
Learning about data cleaning
Data governance frameworks
Compliance
Collaboration and documentation
Effective communication
Documentation practices
Advanced topics in database management
Backup and recovery
Tools for monitoring PostgreSQL
MySQL performance monitoring tools
Strategies for regular performance analysis
Summary
8
The Future of Databases and Their Designs
Understanding vectorized search
MySQL enhancements and innovations
MySQL HeatWave
Prospects of use cases of HeatWave
TiDB as a MySQL drop-in replacement for distributed systems
PostgreSQL – expanding horizons
pgEdge – fully distributed PostgreSQL optimized for the network edge
Multi-master replication in pgEdge
Simplified cluster management
Enhanced backup and recovery
Advanced monitoring and alerting
EnterpriseDB – elevating PostgreSQL for the enterprise
Choosing your EDB deployment method
Introduction to columnar databases with ClickHouse
What is ClickHouse?
Choosing the right alternative
Summary
Index
Why subscribe?
Other Books You May Enjoy
Packt is searching for authors like you
Share Your Thoughts
Download a free PDF copy of this book