Relational DB

Two components:

• Client (e.g. pgAdminGui, psql CLI)
• Server (e.g. Psql Server)

Users cannot directly interact with the server

• Instead, the provided client can be used to interact with the server
• This structure allows even remote clients from another machine to interact with the server

Hierarchy

There has to be many tables to build an app

Grouping of these multiple tables are called a schema

• When there are a lot of schemas, they are grouped into a database

A machine can have many databases running, grouping of these databases is called cluster (database server)

We use language called SQL to send instruction as a client to server

The structure of a SQL query is therefore reverse of the DB hierarchy

• You select the db, then schema, then the table (largest to smallest)

pgAmin

MySQL vs PostgreSQL

MySQL is a relational database management system that lets you store data as tables with rows and columns

PostgreSQL is an object-relational database management system that offers more features than MySQL

• It gives more flexibility in data types, scalability, concurrency, and data integrity

Caveats

Dealing with many-to-many relationships

• good idea to create an intermediate join table
  • with join table, you don’t need to create a foreign key column to connect the two tables

Scope is a pre-built filter in the model

Scopes are good because it is more declarative than chaining more methods

DB indexes have character limits, can be aliased instead as a solution

• can be accomplished via t.references in Rails migration
• t.timestamps adds time stamp columns to the table

SQL

Practice: sqlzoo