Relational DB Anatomy

Two components to a relational Database:

• Client (e.g. pgAdminGUI, Psql CLI)
• Server (e.g. PSQL Server)

Users cannot directly interact with the database server.

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

Relational DB 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

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