This article will cover:
The overall structure of the LiteFarm database schema
Common or particularly valuable queries for extracting data
Navigation of the schema using foreign keys
No prior knowledge is necessary, though a basic understanding of relational databases will help.
Things you’ll need to follow-along:
A tool to open a “.erd” file - DBeaver is a free example
A Postgres client
Read-only access to the beta environment: Talk to Lite Farm about this
The first page of this cheat sheet
This .erd 👇 (accurate as of March 22nd 2022)
Pre-watching
Nathan Williams does a really good introduction to SQL broken down into easy-to-learn videos, so I’ve linked to the videos in his playlist for all but the first video below. I won’t be using pgAdmin or using the Northwind data set (which he covers in the first and second video, respectively) for our LiteFarm specific session, but it probably makes sense to watch those videos when you’re getting started so that you can more effectively follow along with the videos we do cover.
What is a data model?
A data model is a structured collection of tables. Following object oriented principles, tables are generally created to hold information about a conceptual idea such as a task, farm, or user. There are important exceptions to this rule, but in general, this is the case. Each table has certain attributes or columns that fit within the concept of the table. For example, the user table would have columns such as username, email, and name that relate to every user. Each row in that table is a one instance - or record - of that table. Using our user table example, you could have:
Username | Name | |
|---|---|---|
jdoe | jdoe@gmail.com | Jane Doe |
LightningBoltBoiZ | zeus@olympus.gr | Zues |
The above user table has 2 records. One for Jane Doe and another for Zues.