chapter two

2 Standard RDBMS capabilities

 

This chapter covers

  • Creating database objects
  • Querying and manipulating data
  • Using JOINs and transactions
  • Executing application logic within a database
  • Enforcing data integrity
  • Restricting access to data

This book assumes you're familiar with the basics of relational database management systems (RDBMSs) and Structured Query Language (SQL). At a minimum, you should understand what relational databases are used for and how to run basic SQL queries. If you meet this bar, this chapter offers an overview of standard capabilities typically supported across all SQL databases, including Postgres. If these concepts still sound new to you, no problem—enjoy reading this chapter to see the standard features in action, and refer to other resources whenever you'd like a deeper understanding of any topic.

Let’s take a quick tour of the widely used RDBMS capabilities that Postgres provides while designing a multitenant eCommerce platform used by thousands of merchants worldwide. First, we’ll learn how to create a database structure that stores and isolates each merchant’s (tenant’s) data within a single Postgres instance. Then we’ll explore how to query, modify, analyze, and secure access to that data using the abundant features the database offers.

2.1 Creating the database structure

2.1.1 Creating databases

2.1.2 Creating schemas

2.1.3 Creating tables

2.2 Querying and manipulating data

2.3 Data integrity

2.3.1 Constraints

2.3.2 Foreign keys

2.4 Transactions

2.4.1 Implicit transactions

2.4.2 Explicit transactions

2.4.3 Multiversion concurrence control

2.5 Joins

2.6 Functions and triggers

2.6.1 Functions: A practical example

2.6.2 Triggers: A practical example

2.7 Views

2.8 Roles and access control

2.9 Summary