NOTES ON THE LINKEDIN COURSE

this is just a spot for us to type up notes on the LinkedIn Learning course we're watching.

0. INTRODUCTION

WHY USE A DATABASE?

0:29 => *"these are found in many places from banking and order applications to mobile apps, etc."*

It's really key to point out that databases are EVERYWHERE -- so it's almost harder to figure out something they're NOT good for than the reverse.

Every experience you have on the internet that feels large and unbounded -- shopping for necessities on Amazon, hopping from clip to clip to clip on Youtube, checking out Maine yurts on AirBnb -- all of these experiences are rendered possible by databases.

And what's cool, from a teaching and learning perspective, is that if the database as a form is capable of encoding a world as complex as Amazon's system of products, or YouTube's web of videos, or AirBnB's better-as-an-idea-than-in-reality, hipster-targeting "experiences", then I think one could argue that it's the perfect medium for academic work.

And, obviously, databases are already used across most of Harvard's disciplines for research . . . but what might it look like as a teaching and learning tool?

WHAT YOU SHOULD KNOW

talking about databases in general, not specifics. This is good for us, because Airtable isn't all that much like an SQL DB from a user's perspective.

1. DATABASE FOUNDATIONS

RELATIONAL DATABASES

KEYS AND UNIQUE VALUES

The part about unique values in tables is interesting, because in our airtable bases, we tend to use formulas nade of concatentating other attributes in two or more columns.

QUESTION: do we think of the key as that first field? or as the Airtable Record ID?

ACID AND TRANSACTIONS

note: getting into specifics, kind of a glance "under the hood"of databases. good to know as a reference, but info about ACID and CRUD and communicating with the database through SQL are mostly taken care of for us by airtable's gooey ui/ux.

question: but do we change this when we start adding automations and external data?

BASIC SQL

not necessarily relevant for us?

Though it is interesting to compare and contrast SQL queries and Airtable Views and Filters.

Like this sample query code to list out customer orders (data which exists across two tables: one for customers, one for orders) would be done with a column in customers that links to orders (and vice versa), then a filter in customers to show only customers with orders:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

If for some reason we want to be able to create

EXTRA NOTES & QUESTIONS

And here it gives us a good overview of relationships.

2. TABLES

MODELING AND PLANNING A DATABASE

This feels like a good step for us to think about performing for Bok (coming up with a list of all the entities at Bok we want to track, the properties of these entities we might care about, and then some relationships between these entities).

Capitalization conventions = Capitalize table names, upper-CamelCase (PascalCase) field names.

Avoid using spaces in field names.

NAMING TABLES

Simple notes: tables should be labeled with the plural of the object they track.

COLUMNS AND DATA TYPES

Airtable offers us different options than the ones he lists, but it's still good to check these out.

Q: is it true that the only difference between long and short text fields on airtable is in the view? or are they really stored differently? (like VARCHAR(50) or TINYTEXT vs LONGTEXT?)

NUMBERS AND OTHER TYPES

note: in AT, the date is a date object rather than a string, meaning that you can manipulate the way you view it down the line

PRIMARY AND FOREIGN KEYS

In Airtable, the primary key is the recordID, but this is hidden from you by default. Would we ever want to make this visible in a convenience column by default in every Main Base we create?

3. RELATIONSHIPS

One-to-many most common relationship used

Use primary key to represent data (it never changes and is unique) when creating relationships

One-to-one is not commonly used. Often used to protect secure information.

4. DATABASE OPTIMIZATION