Introduction to Data Modeling
Explore how to design and model database schemas in PostgreSQL for efficient application development. Understand the impact of schema choices on query simplicity and performance, and learn to test models with real-world SQL queries based on business needs.
We'll cover the following...
As a developer using PostgreSQL, one of the most important tasks you have to deal with is modeling the database schema for your application. In order to achieve a solid design, it’s essential to understand how the schema is then going to be used, as well as the trade-offs it involves.
Database schema and database model
Depending on the schema you choose for your application, some business cases are going to be easier to solve than others, and given the wrong set of trade-offs, some SQL queries turn out to be really difficult to write—or impossible to achieve in a single query with an acceptable level of performances.
As with application code design, the database model should be meant for the normal business it serves. As Alan Kay put it, “Simple things should be simple; complex things should be possible.” You know your database schema is good when all the very simple business cases turn out to be implemented as rather simple SQL queries. Yet, it’s still possible to address very specific advanced needs in reporting or fraud detection, or accounting oddities.
The testing of a database model is done by writing SQL queries for it, with real-world application and business use cases to answer at the psql prompt. Now that we’ve seen what can be done in SQL with basic, standard, and advanced features of PostgreSQL, it makes sense to dive into database modeling.