In class, you asked a baseball database which team has lost the most games in history, and it couldn't really answer. It could give you the worst single season (the 1899 Cleveland Spiders, 20-134) or the most losses added up by team name (the Philadelphia Phillies, 10,426). But a fan asking that question means the franchise, and there is no franchise anywhere in the database. Cleveland alone shows up under seven different names.

No amount of clever SQL fixes that. The problem isn't the query; it's the design of the tables. Deciding what tables a system should have, and how they relate to each other, is called domain modeling, and it's the subject of this post.

Models

A domain is the slice of the real world your software is about: a school, an airline, a store, a baseball league. A domain model represents that world in software, using the words the people in it actually use. An airline and a retailer end up with different models because they talk about different things.

We'll use a school as our example. We call each real-world thing in the domain a model, and all of the models, together with the relationships between them, make up the domain model.

What are the real, tangible things in a school? Two obvious ones:

• Student
• Teacher

Students and teachers aren't theoretical. They're people who exist outside the software. Students attend the school; teachers teach its courses.

You might ask: why not a single model for Person? Students and teachers are both people. It's a fair question, and the answer is that models are distinguished by their attributes, the data we need to keep about them. In our school, teachers have a bio and students don't. Students have contact information (email and phone number) and teachers don't. So they're different models.

Where do those attributes come from? From what the product needs to do. If the product shows a teacher's bio on the course page, a teacher needs a bio. Next week, we'll write that down properly as user stories, and the attributes will fall out of them. For now:

• Student: first name, last name, email, phone number
• Teacher: first name, last name, bio

Models become tables

If you've been following along with SQL, you've probably already guessed where this is going. A model and its attributes map neatly onto a database table:

• Each column is an attribute
• Each row is one instance of the model: one student, one teacher

Each model gets its own table, because each model has different attributes:

students

teachers

A good start. Next, a Course. A course isn't a physical thing you can hold, the way a student or teacher is, but it's real and it has data we need to keep: a name and a description.

courses

An attribute, or a new model?

You might be wondering whether that's really all the data a course needs. What about the times it meets, and who teaches it?

Knowing what belongs as an attribute on a model and what should be its own model is one of the keys to being good at domain modeling. So let's try putting the times and teachers on the course and see what happens:

courses

Seems reasonable, until Taco-Making gets popular and needs a third time. Then a fourth. Every new time slot means two new columns, and every course that has fewer slots is left with blank cells. The table is growing sideways.

That's the smell. A well-designed table grows down: new data means new rows, not new columns. When a table keeps needing more columns for "another one of these," that thing wants to be its own model.

One-to-many

Each time a course is offered, with its own teacher, is a Section. One course can have many sections:

sections

Much better. Courses now only hold what's true of the course itself, and sections hold the rest. A new section is just a new row, and a course can have none, one, or a hundred.

Section makes sense as its own model because we don't know how many there will be, and because it has attributes of its own (a time, a teacher) that might grow later.

This is a one-to-many relationship, and the way you build one is the foreign key you met in SQL 2: put a column on the "many" side that points at the "one" side. One course has many sections, so course_id goes on sections.

Many-to-many

Notice that sections has two foreign keys. That's because there are two one-to-many relationships here: a course has many sections, and a teacher has many sections.

Put those together and you get something new. A course can have many teachers, and a teacher can teach many courses. That's a many-to-many relationship, and it never lives directly on either table. It's always two one-to-manys through a model in the middle:

Two one-to-many relationships = one many-to-many relationship

The model in the middle is called a join model. Here, it's Section. Unlike Student or Teacher, Section isn't really a thing you'd point at in the physical world; it exists because of the relationship between two things that are.

Back to the franchise

Now the baseball question has an answer. The database was missing a model:

franchises

teams (one row per team, per season)

One franchise has many team-seasons, so franchise_id goes on teams, the "many" side. The team's name stays on the season, because names change over time; the franchise is what stays the same. "Which team has lost the most games in history?" becomes a SUM of losses grouped by franchise instead of by name.

It isn't free, though. Someone has to decide which seasons belong to which franchise, and in Cleveland's case not all seven names are the same club (the 1899 Spiders were a different team from the one that became the Indians). The data can't make that call for you. That's what domain modeling actually is: decisions about the real world, written down as tables.

Naming conventions

These are the conventions we've been using, and we'll keep using them for the rest of the course. None of them is technically required, but following them makes everything easier to read, and it'll matter when we get to Rails.

• Models are singular and capitalized: Course, Section.
• Table names are plural and lowercase: courses, sections.
• Column names are lowercase: name, description.
• Multi-word names use underscores: first_name, teacher_id.

Next week

We picked the attributes in this post by saying what the product needs to do. Next week, we'll do that properly: start from user stories, and let the domain model come out of them.

This one is optional. In class, COUNT, SUM, AVG, MIN, MAX and GROUP BY were reading vocabulary: you need to recognize them when a chat writes them, not write them yourself. If you want to know how they work, here they are. It picks up where SQL 1 left off, with the same 👶 baby tables.

Aggregate Functions

Often, the questions you want answered will involve math. That is, instead of simply seeing a subset of the rows in a table, we'll want to aggregate the results together in some way, e.g. adding the number of rows, summing a single column, seeing an average, etc.

How many reviews have been written?

• Identify the table that holds the information you need - Reviews
• Identify the columns that hold the information you need - not applicable in this case, since we're looking for an aggregate number
• Add any other conditions - none
• Then, ask the question:

SELECT COUNT(*) FROM Reviews;

There are a couple of crazy things going on here! Let's break it down.

First, instead of a column name like Name, Price, Body, etc. in our SELECT clause, we're calling a SQL function. There are many SQL functions built into SQL, but they will always contain a set of parentheses, which denote arguments to the function. In this case, we're using the COUNT function, which accepts an argument of "what columns are we counting?" Since we're simply looking for the aggregate number of rows in the table, the columns aren't relevant and we just use *.

And even though we're asking for an aggregate, that is, a single number representing the count of all the reviews, our result is still a 👶 baby table. It simply contains a single column -- called COUNT(*) -- and a single row. We can call several SQL functions, or a combination of SQL functions and column names, which will result in a 👶 baby table containing several columns*.*

Let's look at another aggregate function, where the argument does matter:

What is the average rating across all reviews?

• Identify the table that holds the information you need - Reviews
• Identify the columns that hold the information you need - an average Rating
• Add any other conditions - none
• Then, ask the question:

SELECT AVG(Rating) FROM Reviews;

Similar as before, with our COUNT function, but this time, the argument matters. We're using the AVG function, which takes a column name as an argument - which column are we averaging?

As mentioned, there are many functions built-in to SQL. The ones that are relevant to this lesson are aggregate functions - ones that do math to aggregate information. Examples of aggregate functions include:

• COUNT - counts the number of rows
• AVG - average of a column value or expression
• SUM - sum of a column value or expression
• MIN - minimum of a column value or expression
• MAX - maximum of a column value or expression

Grouping

Occasionally, a question like How many reviews have been written? will be enough to satisfy business requirements. However, it's more likely that your boss, client, or other stakeholder will want more specific information than simply aggregating the entire table's worth of data. We'll often want to categorize or aggregate (group) results by the value of another column. For example, a more common question might be:

How many reviews have been written per product?

• Identify the table that holds the information you need - Reviews
• Identify the columns that hold the information you need - the Product and the aggregate number of reviews
• Add any other conditions - none
• Then, ask the question:

SELECT Product, COUNT(*) 
FROM Reviews
GROUP BY Product;

We see that the values separated by commas in the SELECT clause, whether they are column names or SQL functions, end up being the columns in the 👶 baby table. And, because we used a GROUP BY clause, our results are aggregated (grouped) by the distinct values in the Product column.

This brings us to Rule #1A, the First Law of all SQL... when using a GROUP BY clause, the columns listed in the GROUP BY must also be in the SELECT clause. Makes sense, right? In this example, if the Product column wasn't also in the SELECT, the results would just be a list of numbers with no context:

SELECT COUNT(*) 
FROM Reviews
GROUP BY Product;

Not very useful, is it?

The rule in the other direction is the one that actually bites: every column in the SELECT must either be in the GROUP BY or be inside an aggregate function. Most databases, PostgreSQL included, refuse to run a query that breaks it. SQLite runs it anyway, and quietly fills in a value from one row of each group. In the baseball database:

SELECT year, name, MAX(wins)
FROM teams
WHERE year = 2002
GROUP BY year;

The Oakland Athletics also won 103 games in 2002. One row per group has room for one name, so SQLite picked one and didn't mention it. When a chat hands you SQL with a column like name sitting next to a MAX or a COUNT, check whether that column is in the GROUP BY. If it isn't, the name may not mean what it looks like it means.

Order of Clauses

SQL 1 ended with the order of clauses; here it is again with GROUP BY in its place. Because of the way SQL is executed, the order in which the clauses are written does matter. This may differ slightly based on the flavor/implementation of SQL being worked with (i.e. some are more forgiving than others), but generally speaking, the order should be as follows:

• SELECT - the columns or executed functions you want in the result
• FROM - the table(s) involved
• WHERE - the conditions or constraints
• GROUP BY - columns in the SELECT used to aggregate the results
• ORDER BY - columns by which the results shall be ordered, either numerically or alphabetically
• LIMIT - how many rows to return

To put it all together:

What is the average rating per product, taking into account only those reviews with a rating of 3 or above, from lowest to highest average rating?

• Identify the table that holds the information you need - Reviews
• Identify the columns that hold the information you need - the Product and the aggregate average rating of reviews
• Add any other conditions - rating of 3 or above
• Then, ask the question:

SELECT Product, AVG(Rating)
FROM Reviews
WHERE Rating >= 3
GROUP BY Product
ORDER BY AVG(Rating);

Try it: aggregation

In class, aggregates were reading vocabulary: you will see them in SQL a chat writes for you far more often than you will write them. Reading is easier once you have written one or two, so:

• How many teams played in 2020? One COUNT(*) and one WHERE. (30.)
• Which team name has been used for the most seasons? GROUP BY name, COUNT(*), sorted, and one row. Then ask for three rows, and see what one row was hiding.

JOINs

Sometimes the answer to a question is contained within more than one table. Consider our Products table:

And Reviews:

And the question we want answered is:

Can I see all the reviews for the products in the Furniture department?

The results – our 👶 baby table – and the WHERE clause would both need to contain columns from both the Products and Reviews tables. Let's follow our usual formula and see what happens:

• Identify the table(s) that contain the information you need – Reviews and Products
• Identify the columns the hold the information you need – the Product, Rating, and Body from Reviews, as well as Department from Products
• Add any other conditions – products in the Furniture department

Before being able to ask the question, though, we need to add one additional step to this recipe.

• Identify the thing the tables have in common – the Name from the Products table is equivalent to the Product column from the Reviews table

Now, we can ask the question:

SELECT Products.Department, Reviews.Product, Reviews.Rating, Reviews.Body
FROM Reviews INNER JOIN Products ON Products.Name = Reviews.Product
WHERE Products.Department = "Furniture";

Whew! There seems to be a lot more here than what we've seen thus far... but don't worry; it's actually quite straightforward. Let's break it down.

Because there are multiple tables involved, we're prefixing each column name with the name of the table and a dot . – e.g. because the Body column is in the Reviews table, we write it as Reviews.Body. This is prevent conflicts in naming – for instance, what if the Products and Reviews tables both had a Body column? This syntax clears that up.

And, the FROM clause is more complex now. Previously, it was just the name of the single table we were reading from. Now, we're reading from the intersection of the two tables. This intersection is called a JOIN, and the commonality between the two tables is specified in the ON clause. The intersection between the table happens when the Name from the Products table is equivalent to the Product column from the Reviews table, so it's ON Products.Name = Reviews.Product. Furthermore, if there was a third, fourth, fifth, etc. table involved, they would be chained together in the FROM clause with more INNER JOINs.

The Primary Key

A shocking confession: the examples I've provided thus far – the Products and Reviews tables from our fictional e-commerce store that sells a rather eclectic variety of stuff – hasn't been entirely realistic. The one thing that basically all tables in a relational database have, and that's missing from these tables, is some sort of unique identifier for each record. A primary key. If you've ever seen a database table outside of this course, you may have seen a variety of different types of primary keys. Based on the database design, sometimes you'll see something like a Social Security Number, or some other strictly-enforced integer value. Sometimes it's a long alphanumeric string based on a software algorithm. Or, a lot of the time, it's simply an integer that starts at 1 and goes up by 1, forever. The auto-incrementing integer primary key is fairly common for a freshly-built application nowadays, and that will be the rule for primary keys that we'll follow for the reminder of this course.

Have a look at the permanent URL for the tweet above – that URL never changes, and will always direct you to the page for that tweet, unless Mr. Culkin decides to delete it. What is the 1298730289737293824 all about? That's the primary key. That's right, there was a tweet #1 at some point, and at the time this tweet was posted, it was the 1,298,730,289,737,293,824th tweet that had ever been posted. The number never goes down, and the number never gets reused. Luckily, there are a lot of numbers in the universe still left to use.

Foreign Keys

Now that we know what a primary key is, let's look at why it's so important to use them in database design. First and foremost, we use primary keys to make our data design less fragile. To see what I mean by that – let's look at a quick example. If we look at the existing table design we've been working with, which does not contain primary keys, we can see how we can easily break the integrity of the data. For instance, what if we change the name of the "camera" product to something else?

Anarchy! We've immediately broken the relationship between the Products and Reviews tables. Now, the rows in Reviews that previously referenced the "Camera" are now orphaned, that is, they are left with no valid parent record that is referenced in its Product column. The SQL query that we just wrote to get product reviews in the furniture department – broken. Of course, we could update each review to reference the new name of the product. That would be easy enough in a database of this size, but certainly not in a database of more complex design and possibly millions of records. It's much better to update our database design to include a primary key.

The auto-incrementing primary key ID column starts at 1 and simply increments by 1 for each new record in our Products table. Next, we slightly re-design the Reviews table to point to the product's ID instead of its Name:

And to fix our SQL from above, we join by the ID and ProductID, instead of the Name of the product:

SELECT Products.Department, Reviews.Product, Reviews.Rating, Reviews.Body
FROM Reviews INNER JOIN Products ON Products.ID = Reviews.ProductID
WHERE Products.Department = "Furniture";

The ProductID in the Reviews column is known as the foreign key. A foreign key is simply a column in a table that points to a primary key in another table.

Try it: the baseball database

The baseball database is built exactly this way. players and teams each have an id, and stats holds one row per player per team per season, pointing at both with two foreign keys, player_id and team_id. To ask anything about a player's season, you join through it:

SELECT players.first_name, players.last_name, teams.year, teams.name, stats.home_runs
FROM stats
INNER JOIN players ON players.id = stats.player_id
INNER JOIN teams ON teams.id = stats.team_id
WHERE players.last_name = 'Griffey';

Run it on the Run SQL page, and look at the id of each player in the results (add players.id to the SELECT to see it). This is the Ken Griffey question from class: two players, one name. A query that adds up his home runs by name answers a question nobody asked. A query that uses his id cannot make that mistake, which is the whole argument of this post in one line.

SQL stands for Structured Query Language - see, you're halfway through the first sentence of this course, and you're already half asleep! Simply put, SQL is a way for us humans to communicate with databases. And "database" is a pretty generic term for "way for a computer to store data". There are lots of ways for a computer to store data, including (but certainly not limited to):

• CSV, JSON, or other structured text file
• Spreadsheet
• Document database
• Relational database management system (RDBMS)

That last one -- relational databases or RDBMS -- is the one we're going to concentrate on in this course. While there are many software applications that store data in other ways, including those listed, a vast majority of business-class software applications use some flavor of relational database to store data. These "flavors" are vast - some are open-source solutions, and some are proprietary and commercial. Examples of these are:

• MySQL (open-source)
• PostgreSQL (open-source)
• SQLite (open-source)
• IBM DB2 (closed-source, commercial)
• Microsoft SQL Server (closed-source, commercial)
• Oracle Database (closed-source, commercial)

And, although there are a large number of these flavors, developed by many different companies and organizations, the core of the SQL language is a standard that may be used to communicate with any of them.

Let's dive right in.

The Basics

Relational databases store data in tabular format. If you've ever used Microsoft Excel, Google Sheets, or any other spreadsheet application to store a list of anything, you've probably invented a way to organize data that is very similar to how databases do it - perhaps:

• Your data is stored in a grid format
• The columns of the grid represent the attributes of the thing you are storing
• There is a row in the grid for each one of the things, and maybe that's called a record

For example, if I were to build a spreadsheet of my favorite movies, it might look something like this:

Pretty straightforward. And, if I want to add more attributes to my data -- say, I want to add the studio that produced the movie -- I'd simply add to the columns of data.

And, if I want to add more movies, I'd add to the rows of data.

As simple as this example is, this is precisely how data is structured in a relational database system. To offer up some definitions - the columns are known as the fields or attributes, the rows are known as records, and the rows+columns together is a table. When we have a collection of many tables, that's a database.

In practice, the main difference between a spreadsheet (e.g. Excel) and a database system is that Excel already includes a standard front-end by which an end-user (like you) is able to read and write to the data. Since the use-cases for RDBMSes are much more varied and programmable than a spreadsheet, there's no such standard for relational databases. Instead, we use SQL to talk with the database, and to pass the results on to our front-end of choice, whether that's a custom web application, a PDF report, a commercial front-end like Tableau, or whatever else might be needed.

Using SQL to read data

In this lesson, we'll be looking at ways we can use SQL to read data from a relational database. In a later lesson, we will be looking at using SQL to write to and modify a database.

The SELECT statement

We read data from a database by using the SELECT statement in SQL. Let's look at an example. Suppose we own an e-commerce store - we'd probably have a a database table used to store the products we sell. This table would likely be named Products and look something like this:

The SELECT statement allows us to ask the database questions. What products do we sell? How much does the Toaster cost? How many products cost more than $500?

It would be nice if the SQL language knew how to answer your question, in exactly the way you asked it. But, no, it's a computer, so we have to follow a simple formula so that you can ask the question in a way that SQL can understand:

• Identify the table that holds the information you need
• Identify the columns that hold the information you need
• Add any other conditions
• Then, ask the question in SQL

For instance, if my question is: what are the names of the products we sell?

• Identify the table that holds the information you need - Products
• Identify the columns that hold the information you need - Name
• Add any other conditions - none
• Then, ask the question:

SELECT Name FROM Products;

There are 3 things about this SELECT statement that I'll point out:

• It ends with a semicolon ; - this depends on the flavor of RDBMS and the front-end being used, but many databases will require the semicolon at the end of the SQL statement, so all the examples we'll use will include it
• The column and table names are case-sensitive; again, this will depend on the specific database system, but for illustration's sake, all examples will match the table/column name casing in the database
• The SELECT and FROM clauses are in ALL CAPS; this is NOT a requirement in SQL, but clauses in examples will be written this way, as it is often more readable

The result that we'll receive is actually another, temporary table - i.e. one that is not part of the database structure or schema as-designed, but is simply a temporary subset of the larger table we've targeted. I like to think of this as a "👶baby table" - a much smaller table that looks a lot like the larger, original table, but only holds the data we asked for:

To be clear, "👶 baby table" is not an official SQL term... it's just what I like to call it.

Now, it's up to you to decide if the results provided in this "👶 baby table" are everything you need, or if you need to modify your question in order to get more information.

Hmmm, it would nice if I could see the price too. Can I get a list of all the products we sell, along with the price?

Back to the formula:

• Identify the table that holds the information you need - Products
• Identify the columns that hold the information you need - Name and Price
• Add any other conditions - none
• Then, ask the question:

SELECT Name, Price FROM Products;

Sorting

Now that we've seen how to use the SELECT statement in SQL to retrieve rows of data from a database table, let's take one step toward doing something more meaningful. It's pretty rare that we'll want to get all records in a database table, in exactly the order that the records were inserted - this is what we've done thus far.

We'll often want our records in our 👶 baby table to be in a specific order - perhaps alphabetically or sorted by a numeric value. This is going to be based on how the data is ultimately consumed - e.g. in a PDF report for a stakeholder in your company, displayed on-screen for a user of a web application, and so on. Let's continue with our question and answer format for this one:

Can we order the results by price?

• Identify the table that holds the information you need - Products
• Identify the columns that hold the information you need - Name and Price
• Add any other conditions - sort by Price
• Then, ask the question:

SELECT Name, Price FROM Products ORDER BY Price;

As we can see, ascending order (from the least to the greatest) is the default when sorting a numeric value. (It's alphabetically from A-Z with a non-numeric value.)

Can we order by the more expensive products first?

• Identify the table that holds the information you need - Products
• Identify the columns that hold the information you need - Name and Price
• Add any other conditions - sort by Price in descending order
• Then, ask the question:

SELECT Name, Price FROM Products ORDER BY Price DESC;

Limiting

Another common use-case is that we want to limit the number of rows in our 👶baby table - what we've done so far is just fine for our very small table with 4 rows of data. But what if our Products table contained 10 million rows? The SELECT statements we've written thus far would be very slow, and not likely to be very helpful or meaningful to the person receiving the results. So we must often reduce our results, and that usually means refining the question we're asking.

What is the single most expensive product we sell?

• Identify the table that holds the information you need - Products
• Identify the columns that hold the information you need - Name and Price
• Add any other conditions - sort by Price in descending order and only return 1 row
• Then, ask the question:

SELECT Name, Price
FROM Products
ORDER BY Price DESC
LIMIT 1;

Same results as before - but only returns a single record, answering our question. Also note that we're separating each fragment of the SQL statement here -- AKA clauses -- by putting each on its own line. As our SQL becomes more complex, I've found this easier to read and comprehend -- especially when first learning -- so we'll do this from now on.

Try it: the baseball database

Let's play ball! Open your copy of the baseball repository in its Codespace (the one you made in class; reopen it from github.com/codespaces rather than making a new one), run bin/rails server in the terminal, and open the port it offers. The app opens on the Run SQL page, a box that runs whatever SQL you type into it. The database has three tables:

teams (id, year, name, park, wins, losses)
players (id, first_name, last_name, bats, throws)
stats (id, team_id, player_id, games, at_bats, runs, hits, doubles, triples, home_runs, rbis)

Try it:

SELECT first_name, last_name FROM players;

Don't forget that semi-colon! You should see the first and last names of all 20,358 players in the database. Then try the question from class, the five winningest seasons ever, using the formula above: which table, which columns, sorted how, how many rows?

If you would rather work in the terminal, save a query in a file in queries/ and run it with bin/query queries/your-file.sql. And if you want to see the database itself, with no page in front of it, bin/rails db opens SQLite's own prompt (type .quit to leave). We will live there in week 5.

Filtering Data

The ability to filter data is probably the most powerful and often-used feature of SQL. For many people, gaining insights from arbitrary sets of data is often the objective of learning SQL in the first place.

We've already learned about the LIMIT clause, which simply whittles down the size of our result set -- the 👶 baby table -- by some number that we provide. However, we often want to specify criteria for filtering the results, rather than a set number like 10. What are our best-selling products? Which sales tactics are producing the best results this month? How many orders did we receive for the new widget this week?

For questions like these, we must use the WHERE clause. The WHERE clause allows us to add more criteria to the questions we ask. Let's jump into an example using our Products table:

Which products cost more than $500?

• Identify the table that holds the information you need - Products
• Identify the columns that hold the information you need - Name and Price
• Add any other conditions - Price is more than $500
• Then, ask the question:

SELECT Name, Price FROM Products WHERE Price > 500;

A WHERE clause is going to always have a column name (in this case, Price), a comparison operator (> or "greater than") and a value to compare (500). Because the column in question contains numbers, it's going to compare the Price of every row in the table against the numeric value 500 and filter the results to only include those with a price greater than that number. Let's look at what happens with a non-numeric comparison:

Which products are sold in the Furniture department?

• Identify the table that holds the information you need - Products
• Identify the columns that hold the information you need - Name and Price again, but we probably want Department now too
• Add any other conditions - Department is Furniture
• Then, ask the question:

SELECT Name, Price, Department 
FROM Products 
WHERE Department = "Furniture";

Again, we have a column name (in this case, Department), a comparison operator (= or "exactly equals") and a value to compare ("Furniture"). Because Department is non-numeric, the value to compare must be in quotes. This is necessary to distinguish the comparison value from columns name, other SQL clauses, variables, and the like.

Separate multiple filters/conditions with the AND clause. So if we wanted the answer to:

Which products are sold in the furniture department and cost more than $500?

SELECT Name, Price, Department 
FROM Products 
WHERE Department = "Furniture"
  AND Price > 500;

Matching a pattern

= means exactly equal, and sometimes that is not what you want. LIKE matches a pattern, where % means "anything, or nothing, here." In the baseball database:

SELECT year, name, park
FROM teams
WHERE park = 'Wrigley Field';

returns 107 rows, every season played at the ballpark in Chicago. Change the last line to:

WHERE park LIKE '%Wrigley%';

and you get 108. The extra one is the 1961 Los Angeles Angels, who played their first season at a different ballpark, Wrigley Field (LA). Neither query is wrong; they answer different questions. The question you asked and the question you typed are not always the same question, and the only way to know which one you got is to read the WHERE.

Try it: filtering

On the Run SQL page:

• Every team that has played at Wrigley Field. Then change = to LIKE and find the extra row.
• Which team won the most games in 2002? You will probably write ORDER BY wins DESC LIMIT 1. Then change the 1 to a 2. Be honest with yourself about whether you would have checked.

A Star is Born

We've already seen how SQL queries require us to provide the columns that we want to be returned in the 👶 baby table - and how we often need to think about what columns those are when providing clean, concise output. Occasionally though, the columns don't really matter. In these cases, we can use the asterisk or star (*) to denote that we want all columns in a table. Consider a Reviews table, which contains reviews about our products. If we simply want all columns, all rows from this table, we could write a query that looks like:

SELECT * from reviews;

And the result would be everything -- all columns and all rows -- from that table.

In practice, we're rarely going to use it like this, unless a table contains just a few columns, or when we don't care about concise output, like when we're testing our queries. Or, within an aggregate function, which is where the optional next post picks up.

Order of Clauses

We've seen enough clauses now to talk about the order they go in. Because of the way SQL is executed, the order in which the clauses are written does matter:

• SELECT - the columns you want in the result
• FROM - the table(s) involved
• WHERE - the conditions or constraints
• ORDER BY - columns by which the results shall be ordered, either numerically or alphabetically
• LIMIT - how many rows to return

To put it all together, in the baseball database:

Which three teams won the most games in the 2000s?

SELECT year, name, wins
FROM teams
WHERE year >= 2000 AND year < 2010
ORDER BY wins DESC
LIMIT 3;

Run it, then change the 3 to a 4. The 2002 Yankees are third with 103 wins. The 2002 Oakland Athletics also won 103, and they're fourth only because of the order the rows happen to sit in. LIMIT cuts where you tell it to, whether or not that's a sensible place to cut.

Going further (optional)

That's everything we covered in class, and everything you need to read the SQL a chat writes for you in this course. If you're curious about the math side of SQL, counting, averaging and grouping, it's in the optional next post. Otherwise, on to SQL 2: Table Relationships.

This is the longer version of the exercise at the center of Week 2: asking a chat about a database, reading the SQL it writes, and deciding whether to believe the number. If the loop went by too fast in class, or you want the reasoning behind it, start here. The SQL we covered (SELECT, WHERE, ORDER BY, LIMIT, joins, keys) is in SQL 1 and SQL 2.

You were the stakeholder

Every company that runs on data has the same three kinds of people touching it. Analysts read data and turn it into decisions. Developers build the applications that read and write it. Database administrators change its structure. And then there is everyone else, which is most people, and probably you: the stakeholder who has a question, hands it to someone who can answer it, and gets a number back.

In class, the chat was the analyst and you were the stakeholder. You did not write the SQL. Your job was harder: decide whether to believe it.

That is the job this course keeps giving you, and it gets more important, not less, as the tools get better. An analyst who is right nine times out of ten is extremely useful, as long as somebody can tell which time is the tenth.

The analyst knows only what you give it

Before the first question, you pasted a brief into your chat. It said: this is a SQLite database, these are the tables and their columns, the seasons run from 1871 to 2020, and when I ask a question, give me the SQL and the answer you expect it to return.

That brief is the whole of what the chat knew about your database. It never saw the file. It could not look at a row. Everything it got right came from what you gave it, and everything it got wrong came from what you did not.

If you skipped the brief, you saw what happens instead: the chat invents a schema. It writes a confident query against a franchise column or an HR column that does not exist, and the query fails. That is the cheap version of the lesson. The expensive version is a question with just enough context to run and not enough to be right.

The word for what you pasted is context, and it will come back all quarter.

Steps

1. Ask. Paste the question exactly as written.
2. Read. What comes back is two things: the SQL, and the number the chat expects it to return.
3. Run. Paste the SQL into the Run SQL page yourself, and compare what the database returns with what the chat predicted.
4. Save. Keep the query in queries/, with the question on the first line, so the Queries page shows it next to the others.
5. Decide. Write one sentence: trust, or don't trust, because.

The "because" is the skill. "Trust, because the SQL filters on the right year and 107 wins is a believable season" is a judgment. "Trust, because it looked right" is a coin flip with extra steps.

The number was memory. The SQL was work.

Asking for both the SQL and the expected answer was deliberate, because they come from different places.

The chat has read an enormous amount about baseball. Ask it who won the most games in 2019 and it will tell you the Houston Astros, 107, before it has written a line of SQL, because it remembers. That expected answer is a prediction from memory. It is often right. It is not evidence.

The SQL is the only part of the reply that touches your data. When you run it, the database answers the question the SQL asks, which is not always the question you asked.

On the first question, the two agreed: the query returned the Astros with 107, and the chat had said so. That agreement is what trust looks like. On the Ken Griffey question, some of you saw them disagree: the chat said 630 home runs, and its own SQL returned 782. Only one of those numbers came from the data, and it was not the one it was confident about.

Three ways a correct-looking answer goes wrong

None of these produce an error. The query runs, a number appears, and nothing on the screen tells you anything is wrong.

The question meant more than one thing

"Which team has lost the most games in history?" In this database there are at least three answers:

• The worst single season: the 1899 Cleveland Spiders, 20 wins and 134 losses.
• The most losses added up by team name: the Philadelphia Phillies, 10,426.
• The most losses by franchise, which is what a fan means: unanswerable. The database has no idea that the Cleveland Bronchos, Naps and Indians are one club, or that the Cleveland Spiders were a different one. It has team names per season, and Cleveland alone appears under seven of them.

The chat had to pick one, and most of them picked without telling you. Some asked which one you meant, or gave you both, which is what a good analyst does. Either way, the ambiguity was in the question before it was in the SQL. If you do not say which answer you want, someone else decides.

And when none of the readings is the one you meant, that is not a SQL problem. It is a data model problem: the database is missing something (here, a franchise) that the question assumes. Which is where the second half of the class went.

The query matched on a name

"How many home runs did Ken Griffey hit?" There are two Ken Griffeys: the father, with 152, and the son, the Hall of Famer, with 630. A query that finds him with WHERE first_name = 'Ken' AND last_name = 'Griffey' adds them together and returns 782, a number that is exactly right for a question nobody asked.

The same thing turned up earlier in the day, on purpose. WHERE park = 'Wrigley Field' finds 107 seasons, all in Chicago. WHERE park LIKE '%Wrigley%' finds 108, because it also catches the 1961 Los Angeles Angels, who played their first season at a different ballpark named Wrigley Field. The question you asked and the question you typed are not always the same question.

This is what the primary key lecture was for. Names are for humans: they get misspelled, shared and reused. An id is the one thing guaranteed to mean one row. When the answer is about a particular person, place or thing, look at the WHERE and ask whether it identifies that thing, or just something with the same name.

Rows went missing

"Which team won the most games in 2002?" The natural query is:

SELECT year, name, wins
FROM teams
WHERE year = 2002
ORDER BY wins DESC
LIMIT 1;

It returns the New York Yankees, 103 wins. It is also wrong, or at least half of the answer: the Oakland Athletics won 103 games that year too. That is the Moneyball season. LIMIT 1 kept the Yankees only because of the order the rows happened to be stored in. Change it to LIMIT 2 and the A's appear.

A missing row is the hardest mistake to catch, because there is nothing on the screen to look at. You can read a wrong WHERE clause. You cannot read a row that is not there.

You can test a feature. You can only check a query.

When a developer builds a feature or fixes a bug, there is a way to know it worked. Click through it the way a user would. Better, write a test: a small program that says what should happen and checks that it does, run every time the code changes. At the end of class you watched an agent do exactly that, adding a test for 140-character captions and running it.

A query does not get that. It runs, a number comes back, and nothing on the screen says whether it answered the question. Some teams build a small test database, stocked with the edge cases they already know about (the legacy rows, the duplicates, the weird old records), and run their queries against it. That helps. Mostly, though, someone spot-checks the result, and spot-checking takes knowing the data: its edge cases, its quirks, and the places where it is not as clean as it looks.

Look at the traps above again. The 2002 tie, the two Ken Griffeys, Cleveland under seven names, the other Wrigley Field: none of them was a SQL mistake. Each was a fact about the data that you had to already know to notice. And nobody hands you the expected result. If the stakeholder knew the answer, they would not be asking.

None of this is new with AI. It was true when analysts wrote every line of SQL by hand, and it is still true now that a chat writes it. What changed is how fast a confident answer arrives. The checking did not change.

Four cheap checks

None of these need more SQL than you already know.

1. Say what you expect before you run it. Even roughly: "a team, a hundred-ish wins, this century." A result that surprises you is the only alarm you get, and you cannot be surprised if you had no expectation.
2. Ask for one more row than you need. If the question is "which one", run it with LIMIT 3. Ties and near-ties show up immediately, and so does a second Ken Griffey.
3. Count what came back. If you asked for the best team in every season since 1960, you should get at least 61 rows, one per season. Fewer means something was dropped. More is not necessarily wrong (in six of those seasons there was a tie), but you should know why.
4. Read the WHERE out loud. Does it say what you asked? A name where an id should be, a LIKE where you meant an exact match, a year range that is off by one: the WHERE clause is where most wrong answers live, and it is usually one line.

When the analyst is good

Some of you watched your chat get everything right: it named both readings of the losing question, split the two Ken Griffeys by id, or wrote the 2002 query so that it returned both teams. The better chats are good at this, and increasingly so.

Check anyway. The chat caught the 2002 tie because it remembers the 2002 season. It does not remember your company's customers, your pricing table or last quarter's refunds, and on that data it will write the same careful-looking SQL with nothing to check it against but you. Good analyst. Now check that it was right. The loop does not change.

Never run UPDATE or DELETE without WHERE

Everything above is about reading. This is about the one time it stops being safe to get it wrong.

UPDATE and DELETE change data, and both apply to every row the WHERE clause matches. With no WHERE clause, that is every row in the table. DELETE FROM teams; does not ask which teams. It removes all 2,955 of them.

In class you watched an AI agent (a chat that can run commands itself, not just suggest them) get asked to "clear out the teams table so we can reload it from a fresh export." It wrote exactly that statement. It stopped and asked first only because the repository has a file, AGENTS.md, telling any agent working there to show a change to the data and wait for confirmation. Without that line, it runs.

Then it ran, and the table was empty. The database came back because in this class the database is a single file that lives in Git, and git checkout db/baseball.sqlite3 restored it. Production databases do not work like that. Most of the time there is no undo, only a backup, if someone made one, from some time before the mistake.

So: when you see UPDATE or DELETE, from a chat, an agent, a colleague or yourself, find the WHERE before anything else. If there is not one, stop.

The thread through all of it

Every trap in this post ran without an error. The SQL was valid, a number came back, and the only thing between that number and a decision was someone reading it. Writing SQL is increasingly something you will hand off. Deciding whether to believe it is not.

Most weeks from here on start the same way and end the same way. This is the short version of both, plus how to reset the database and one settings fix for Windows. Keep it open the first few times; after that you will not need it.

Before Week 2: have a chat open

In Week 2 you ask an AI chat questions about a database and decide whether to believe its answers. Bring whichever one you already use: ChatGPT, Claude, Gemini, Copilot. Any of them is fine. There is nothing to install and nothing to sign up for. Have it open in a second window before class starts.

Getting started

1. Use this template. On the class repository's page, click Use this template, then
   Create a new repository. Pick your own account as the owner, keep the name, and click
   Create repository. You now have your own copy; nothing you do can affect anyone else's.

2. Create the Codespace. In your new repository: Code, then the Codespaces tab,
   then Create codespace on main. The first start takes a few minutes. Let it finish; the
   terminal at the bottom is ready when it shows a prompt and stops scrolling.

3. Start the server. In the terminal:

   bin/rails server
   

   A box pops up offering to open the port. Click Open in Browser. If you miss the box, open the Ports tab next to the terminal and click the globe icon on port 3000.

The server keeps running in that terminal until you stop it with Ctrl-C. If you need a terminal for anything else, open a second one with the + on the terminal panel.

Coming back later. Do not create a second Codespace. Go to github.com/codespaces, or
Code > Codespaces on your repository, and open the one you already have. Your files are where you left them. The server is not; run bin/rails server again.

Windows: turn on right-click

On some Windows machines, right-clicking in the terminal does not open a menu, and the menu is how you find things like Clear. To fix it:

1. Click the gear icon at the bottom left of the editor (the one inside the editor, not the one at the far left of your screen), then Settings. Not the command palette; the setting is not there.
2. Search for right click behavior.
3. Under Terminal > Integrated: Right Click Behavior, yours probably says copyPaste. Change it to selectWord.

It saves itself. Close the Settings tab and right-click in the terminal again. If you are on a Mac, you can skip this.

Resetting the database

In Week 2 the whole database is one file, db/baseball.sqlite3, and it is committed to the repository. So if anything damages it (a DELETE or UPDATE you did not mean, a table you dropped), you can put it back exactly as it was. In the terminal:

git checkout db/baseball.sqlite3

Then refresh the page. The rows are back. The server can keep running while you do it.

This works as long as you have not committed the damage. If db/baseball.sqlite3 ever shows up under Changes when you go to commit, run the line above first, then commit.

Wrapping up

1. Stage. Open Source Control in the left bar (the icon that looks like a branching line). Your changed files are listed under Changes. Click the + next to each one, or the + on the Changes heading to stage them all.
2. Message, then commit. In the box above the list, write what you did in plain words, like answered the three questions. Click Commit.
3. Push. The button turns into Sync Changes. Click it. That sends your commit to GitHub; until you do, it exists only inside the Codespace. Check your repository's page on github.com: your commit message should be there.
4. Stop the Codespace. On your repository's page: Code > Codespaces, the three dots next to yours, Stop codespace. It stops itself after thirty minutes idle, but doing it yourself is the habit, and Codespaces hours come out of your own monthly allowance.

Stopping is not deleting. Your Codespace and everything in it are still there next time.

When it does not work

Post in #fall-2026 on Slack with a screenshot and the step you were on. If it is during class, raise your hand; an FA will come to you.

This is one of two posts from the first week. The other, Git and GitHub, is the tools: what they are and how a change gets into a codebase. This one is about whose code you are willing to build on in the first place.

In class we have (or will) spend a little time on open source and licensing -- enough to give you the shape of it and not much else. This is the longer version, for when you need it.

And you likely will need it. Not because you are going to become a lawyer, but because at some point somebody on your team is going to say "let's just use this library," and you are going to be the person who decides. That decision has terms attached to it, and the terms occasionally bite.

Closed and open

Software gets built and brought to market two ways.

Closed-source software is a commercial asset. The code is seen only by the people working on it, and the intellectual property belongs to whoever owns the company. Most software that is sold, whether packaged or as a service, works this way. If a company's secret sauce has anything to do with the code, they are not going to show you the code.

Open-source software has its source code available to the public. That is the whole definition. Note what it does not say: it does not say free, it does not say non-commercial, and it does not say nobody owns it. Plenty of open-source software is built by large companies, for commercial reasons, and is owned by someone specific.

Why anyone gives work away

The reasonable first question is why a developer or a company would put thousands of hours into something and then hand it over.

• Crowdsourcing. Publish the code and other people find your bugs, and sometimes fix them. The project gets better than you could have made it alone.
• Knowing what is in your food. Telegram publishes its source so it can prove it is not doing anything malicious with your data. A closed product cannot make that promise; it can only assert it.
• Reputation. An individual developer open-sources a piece of their work so that employers and future partners can see what they are capable of.
• Recruiting. A company open-sources something interesting so that the developers they want to hire come and look at it.
• The ecosystem, which is really marketing. Google does not open-source all of Chrome, but the engine underneath it is open. Other browsers get built on that engine, and when one of those browsers does not work out, moving to Chrome is easy, because it is the thing you were already standing on. Being the foundation other people build on is a competitive position, not charity.

The biggest reason underneath all of those is less strategic than any of them. Software is built the way science is built, by people standing on each other's work. Donald Knuth, the Stanford computer scientist, put it this way:

People think that computer science is the art of geniuses but the actual reality is the opposite, just many people doing things that build on each other, like a wall of mini stones.

That has never been more true than it is right now. The reason you can build something sophisticated in a quarter is that thousands of people already built the parts you are standing on.

Here is what that looks like in practice. Every application with users has to handle user security: passwords, sessions, encryption. Almost no business is about user security. It is table stakes, it is subtle, it is easy to get wrong in ways that hurt people, and writing it yourself costs you weeks you needed for the thing your product is actually for. So you take a library that already exists, that thousands of other applications have already stress tested, and you spend your weeks on your own problem instead.

Do not fool yourself into thinking you are going to write all of your own code. Nobody does this. You would never want to, because you would spend all your time in the foundations and never get to the interesting part.

Licensing: the two families

Open source is not a free-for-all. Every open-source project carries a license, and the license is a real legal document with real terms. There are many of them and there are endless variations, and when it matters you read the actual text or you ask someone whose job that is. But nearly all of them fall into one of two families.

Permissive. Do what you like. Use it, change it, sell it, bundle it into something closed-source. Most permissive licenses ask for one thing in return: keep the copyright notice around so people can tell where the code came from. The MIT license is the most common one you will see, and Apache 2.0 is the other big one, which adds some explicit language about patents.

Copyleft. You may use this, and you may change it, but if you distribute what you built, the result has to carry this same license. The GNU General Public License, the GPL, is the one you will run into. The point is reciprocity: the code was given to you open, and whatever you make out of it goes out open too. Sometimes it is called "share-alike," which is a better name for what it actually does.

One thing worth being precise about, because the short version in class makes it easy to get backwards: copyleft does not mean the code is frozen. You are absolutely allowed to modify GPL code. Modifying it is the point. What you owe in return is that your modified version goes out under the same license, so that the next person gets the same freedom you got. The obligation is on what you publish, not on what you touch.

It is permissive licenses, not copyleft ones, where "leave it alone" comes closest to the truth, and even there the obligation is usually just to preserve the notice.

choosealicense.com is a genuinely good, short guide to picking one, and it is the right link to send a co-founder who is about to make this decision for your company.

Case study: the Linux family tree

Linux is the most widely deployed operating system in the world. Not Windows, not macOS. Estimates move around and depend on what you are counting, but the large majority of the web servers on the internet run some flavor of Linux, as does every one of the world's fastest supercomputers, and so does every Android phone. It was written by Linus Torvalds, who also
later wrote Git, which is the other thing we covered in week one. He has had a productive career.

The core of Linux, called the kernel, is licensed under GPLv2. Copyleft. That single fact shapes an entire industry, and the shape is worth following.

Distributions. Ubuntu, Debian, Red Hat and Fedora are all built around that same kernel. Their kernel remains GPL. They wrap it in package managers, installers, desktop environments and support contracts, and those pieces carry their own licenses. Red Hat built a large public company selling support and certification for software anyone could download for nothing.

Android. In 2008 a team at Google built a phone operating system on the Linux kernel. Android's own layers, the parts Google wrote, are licensed under Apache 2.0, which is permissive. That combination surprises people, and it is worth understanding why it is allowed, because it is the single most useful thing in this whole post.

It is not because Google left the kernel alone. Google modifies the kernel substantially for Android, and those modifications are published under the GPL, exactly as the license requires.

It is because of a boundary. Torvalds attached a note to the kernel's license saying that ordinary user-space programs that call into the kernel through normal system calls are not derivative works of the kernel. They are just using it. Android's own layers sit on the user-space side of that line, so they do not inherit the kernel's license and Google can license them as it chooses. Google also deliberately built Android's C library from permissively licensed code rather than the copyleft one Linux distributions normally use, for exactly this reason.

The lesson generalizes past Android: where the boundary falls between your code and the copyleft code is the question that decides your obligations. Linking a GPL library directly into your application is a very different act from running a GPL program as a separate process and talking to it. If your company is ever seriously weighing this, that is the question to bring to a lawyer, and it is a question you can now ask precisely.

On top of Android. Because Android is permissive, anyone can fork it and build a commercial product. Amazon did, and Fire OS runs the Kindle Fire line. Meta built the software for its VR headsets on it. Peloton runs it on the screen bolted to the bike. None of those companies had to open-source the product they built.

A small correction to something easy to assume here: Amazon is not prevented from charging money for Fire OS. It could. It does not, because Amazon's interest is in selling devices and content, and giving the operating system away with the hardware serves that. The constraint is a business decision, not a licensing one.

Follow that chain once more, because it is the point of the whole case study. A copyleft kernel, given away in 1991. A permissive operating system built on it by one of the largest companies in the world. Closed commercial products built on that, by three more. Your closed-source product will sit on a stack like this one, whether or not you ever look down.

The four questions you asked in class

These came from the room, not from the slides, and they are the right questions.

How is any of this actually enforced?

Mostly the way any contract is: somebody notices. An employee, a competitor, a customer who asks for the source, or one of the organizations that exists specifically to notice. The Free Software Foundation and the Software Freedom Conservancy have both pursued GPL violations, usually by asking first and suing rarely.

The famous case is a router. In the early 2000s Linksys shipped the WRT54G, a wireless router that turned out to be running Linux inside. The GPL obliged them to release the source for what they shipped, they had not, and the community noticed and pushed. Cisco, which by then owned Linksys, released it. The released code became OpenWrt, which is still running on routers today. The violation ended up producing a piece of open-source infrastructure.

Here is the version you are more likely to live through, which happened to a company one of us worked with. They were using an open-source front-end framework. The framework changed its license on a new version. They upgraded, because they wanted the new version, and nobody on the team connected the upgrade to the terms. About a year later somebody worked out that they had been out of compliance the whole time, and it was settled with a payment.

Nobody was reckless. They upgraded a dependency, which is a thing engineering teams do dozens of times a quarter without anyone thinking about it, and the terms had moved underneath them.

Does Linux force updates on you?

No. The project keeps developing, and you run whatever version you chose to run. Nobody can reach into your product and change it.

You will usually want the updates anyway, because that is where the security patches are and because software that stays still gets stranded as everything around it moves. But taking them is your decision, and it is a decision, which matters for the next question.

Can a project change its license retroactively?

Not for the version you already have. The copy you received came with terms, and those terms came with it.

The next version is a different matter. A project can license its next release however it likes, and if you upgrade, you have accepted the new terms. That is the trap in the story above, and it is worth stating plainly: the risk does not arrive when the license changes. It arrives when you upgrade.

This is not hypothetical or rare. Over the last several years, a number of widely used projects have changed their terms on a new version, sometimes to a license that is no longer open source at all. Redis, MongoDB, Elasticsearch and HashiCorp's Terraform all did some version of this, and in several cases the community responded by forking the last version under the old terms and continuing from there. If your product depends on something, a license change upstream is a real business event, not a legal footnote.

The practical defense is unglamorous and it is the same defense as for every other dependency risk: know what you depend on, and have someone actually look when a major version lands.

Does everything built on copyleft code have to be open source?

No, and the Android section above is the long answer. The short answer is that it depends on whether what you built is a derivative work of the copyleft code or a separate thing that uses it, and that boundary is technical as much as legal.

What is not optional: the copyleft code itself, and your changes to it, stay under that license and have to be available to the people you distributed it to. You cannot take GPL code private. You can, in many architectures, build something of your own next to it.

And one you asked that we could not answer

Somebody asked why the authors of copyleft projects care, since a permissive license would get their code used more widely. It is a good question and it deserves a real answer.

The answer is that for the people who started this, wide adoption was never the goal. Richard Stallman and the Free Software Foundation designed the GPL in the 1980s to protect a commons. The worry was specific: that software written to be free would get absorbed into closed products, improved there, and that the improvements would never come back, leaving the open version permanently behind. Copyleft is the mechanism that stops that. It is a one-way ratchet, and every improvement anyone distributes lands back in the pool.

Permissive licensing makes a different bet, which is that the widest possible adoption is worth more than the guarantee of reciprocity. Both bets have paid off enormously. They are simply different bets, and a project's license tells you which one its authors made.

Reading a repository before you depend on it

The part of this that will matter to you most often is not the law. It is judgment: deciding whether some project you found is something you want your product to stand on. You will make this call many times, and usually in about four minutes.

Open the repository on GitHub and look at these, roughly in this order.

1. The license. It is listed in the sidebar. If there is no license file at all, the default is that you have no rights to use it, whatever the author intended by putting it in public. "No license" is a red flag, not a green one.
2. The last commit. Near the top of the file list. A project last touched four years ago is a project you are adopting, not using.
3. Issues and pull requests. Do not just read the count. Open the tabs. Are maintainers replying? Are pull requests getting merged or quietly piling up? An open issue list is healthy. An unanswered one is not.
4. Releases. Regular releases mean somebody is still steering.
5. Who is behind it. One person's side project and a thing maintained by a company or a foundation carry very different risk. Neither is disqualifying. You just want to know which one you are picking.
6. Stars and forks. Useful, weak signals. Stars are closer to a bookmark count than a quality score, and old popular projects keep their stars long after the maintainers have moved on. Read them alongside the last commit, never alone.
7. Documentation, and a demo if there is one. Many projects run a live demo page. It is the fastest way to find out whether the thing does what you think it does.

Three worth opening, because they are good examples of different shapes:

• microsoft/vscode, the editor you are using in your Codespace, maintained by a large company in the open.
• nasa/openmct, NASA's mission control framework, with a demo page you can click through.
• chromium/chromium, the engine underneath Chrome and most other browsers, and an example of the ecosystem argument from the top of this post.

What this is really about

You are not going to be the person reading the license text. You are going to be the person who decides whether the team takes the dependency, and who gets asked, a year later, why the company is having this conversation with a lawyer.

The three things worth carrying out of here: open source is not free of terms, the terms change on upgrade and not before, and where the boundary falls between your code and theirs is the question that determines what you owe. Everything else you can look up.

Next: Practice: GitHub, where you run the loop once on your own. If you have not read Git and GitHub yet, read that first; it covers the
difference between the two and how a change actually gets into a codebase.

In class you ran the loop once: a branch, a file, a commit, a pull request, somebody else's review, a merge. You did it in a room with forty other people doing the same thing and an FA two seats away.

This week you do it again, alone, on a repository of your own where nothing can go wrong. That is the entire point of this exercise. The first time you follow along. The second time you find out what you actually know.

It should take about thirty minutes. There is nothing to turn in.

Part 0: get your own copy

If you already made your own hello-world during class, skip this.

Go to github.com/dsgn425-fall2026/hello-world, click the green Use this template button, then Create a new repository. Owner: your own account, not the class organization. Name: hello-world. Leave the visibility on Private. Click Create repository.

Check the name at the top left of the page. It should read your-username/hello-world. If it still says dsgn425-fall2026/hello-world, you are looking at the template, not your copy; go back and click Use this template.

It is yours now. You cannot break anything that belongs to anyone else, which is why we are doing this here rather than in the class roster repo.

Part 1: the loop, by yourself

Open your copy and follow its README from step 2 to step 6. Do not read ahead and do not copy what you did in class from memory. Work it through.

Branch, change something, commit with a real message, publish, open a pull request, merge it, delete the branch.

Two differences from class, and both are worth noticing:

You are the whole team. In the class repo a classmate had to approve your pull request. Here nobody does, so you get to read your own change on the Files changed tab and merge it yourself. Read it anyway, properly, as though somebody else wrote it. That habit is the one this course is really trying to build.

Nothing is protected. Your repo has no rule stopping you from committing straight to main. You could skip the branch entirely and it would work. Make the branch anyway. The habit has to survive the absence of a rule enforcing it, or it is not a habit.

If you want the reasoning behind any of it, rather than the buttons, that is in Git and GitHub.

Part 2: the same loop, from the terminal

Now do it a second time without touching the file explorer. Everything below happens in the Codespace terminal, which you open with View > Terminal from the hamburger menu.

This half exists because the command line is the part of the first class that fades fastest if you never touch it again. Ten minutes now is worth an hour later.

Start on a new branch. Bottom-left corner, Create new branch..., call it terminal-practice.

Find out where you are.

pwd

It prints your current directory, which should end in /hello-world. Then:

ls

lists what is in it. You should recognize the files from the Explorer. Now:

ls -a

The same list, plus the hidden files, the ones whose names start with a dot. There are more files in a repository than the Explorer shows you by default, and .devcontainer is how your Codespace knew what to build.

Run the programs that are already there.

ruby code/hello.rb

prints a greeting. Then:

ruby code/infinite-tacos.rb

which does not stop on its own. Press Ctrl-C to kill it. You need this more often than you would think, and nobody tells you about it until you need it.

Make something.

mkdir notes

creates a folder. Confirm it with ls. Then:

touch notes/week-01.md

creates an empty file inside it. Open notes/week-01.md from the Explorer, write two or three lines about anything from the first class, and save with Ctrl-S.

Move something.

The repo has an images folder with a couple of files in it. Pick one and move it to the top level, then move it back:

mv images/grogu.jpg .
ls
mv grogu.jpg images
ls

The . means "here, the directory I am in." Watch the Explorer on the left as you run each one. The file jumps. You can of course do all of this by dragging with a mouse. The point is that you now know the other way, and the other way is the one that works over a terminal connection to a server at two in the morning.

One useful trick each. Start typing a directory name and press Tab; the shell finishes it for you, and if nothing happens it is telling you that what you typed does not exist. Press the up arrow to bring back the last command you ran, which saves you retyping a long one with a typo in it.

Then put it through the loop. Source Control, stage, commit with a message that says what you did, Publish Branch, open the pull request on github.com, read the diff, merge, delete the branch.

Before you close the tab

Stop your Codespace. On the repo page, Code > Codespaces, the three dots, Stop codespace. It stops itself after thirty minutes idle, but doing it yourself is the habit, and Codespaces hours come out of your own monthly allowance.

What to check before you call it done

• Your repository's main branch contains both changes.
• The commit history shows how they got there, and the messages say what happened. If any of them says update or tried again, go and look at why that is useless to you two weeks from now.
• Both branches are gone, because you deleted them after merging.
• You can say, without looking anything up, what the difference is between a commit and a pull request.

That last one is the actual assessment. The rest is just evidence.

If you got stuck

Post in #fall-2026 on Slack and say where it stopped working. Getting stuck is not a failure mode here, it is the expected experience of week one, and the stuck point is usually the same three or four things. The troubleshooting list at the bottom of the class roster repo's README covers most of them.

This is one of two posts from the first week. This one is the tools: what Git and GitHub are, and how a change actually gets into a codebase. The other is Open source and licensing, the longer version, which is about deciding whose code you are willing to build on. Start here; that one uses the vocabulary this one sets up.

Git and GitHub are not the same thing

Nearly everyone trips over these two names, and it is worth thirty seconds of precision, because more decisions hang off the distinction than you would guess.

Git is software. It is version control for source code: it stores your files, keeps their entire history, and lets several people work on the same project without overwriting each other. Linus Torvalds wrote it in 2005 to manage development of the Linux kernel. It is free and open source, it runs on your own machine (or, this quarter, in your Codespace), and no company owns it.

GitHub is a company. Founded in 2008, bought by Microsoft in 2018 for around seven and a half billion dollars. Its business is hosting Git repositories and selling the things built around them: a web interface, permissions, issue tracking, code review tools, automation.

The relationship between the two names is exactly that, and nothing more. GitHub hosts Git.

If it helps: Git is email, GitHub is Gmail. Email is a protocol that nobody owns and that many companies provide. Gmail is one company's product for using it, with its own interface and its own features layered on top. You can leave Gmail and still have email.

Git is the thing. GitHub is a place to keep the thing.

Other places to keep the thing

GitHub is the largest by a wide margin, which is why this course uses it and why it is the one worth knowing. It is not the only one, and a few of these will turn up in your working life:

• GitLab, the closest competitor, and the one most often chosen by companies that want to run the whole thing on their own servers.
• Bitbucket, from Atlassian, common in organizations already using Jira.
• Azure DevOps, also Microsoft, common in large enterprises.
• Self-hosted, using software like Gitea or Forgejo, or a plain Git server with no web interface at all. Git needs no company to function.

Why the distinction is worth money

Here is the part that matters when you are the one choosing.

Your project's entire history lives inside the repository itself, in a hidden folder that comes along whenever anyone copies it. Moving a project from one host to another is close to trivial: clone it, point it at the new host, push. Nobody holds your code hostage.

What does not move is everything the host added. Your issues, your pull requests and the discussions on them, your review history, your automation, your integrations. Those are GitHub's, in GitHub's shapes, and leaving means leaving them behind or paying somebody to convert them.

That asymmetry is the actual product. It is also the shape of most software-as-a-service lock-in you will ever evaluate: the thing you thought you were buying is portable, and the thing that accumulated around it is not. Worth recognizing the pattern here, where the stakes are low, so you recognize it later when they are not.

What Git actually does

Think about version history in a document editor: you can see what changed, who changed it, and roll back to how it looked last Tuesday. Git is that, for code, with the history treated as the important part rather than a convenience.

Because Git watches every file in a project continuously, it knows the entire history of the work: what was added, modified and deleted, by whom, and when. That is what makes the rest possible.

• Attribution. Who contributed what, and when.
• Time travel. View or return to the project as it stood at any earlier point.
• Branching. Several versions of the same project, running in parallel from a common starting point, which is how more than one person works on something at once without chaos.

Every professional software team uses version control, and in practice almost all of them use Git. Once it arrived, the alternatives largely emptied out.

Repositories

A Git project lives in a repository, usually one repository per project. Hosting repositories is GitHub's main business.

A GitHub user or organization holds repositories. Microsoft's organization on GitHub holds a great many, one of which is the source for Visual Studio Code, the editor running in your Codespace. Your own account starts empty. That changes in the first class.

There are two ways to make one:

• New repository, starting from nothing or from code already on your computer.
• Use this template, which copies an existing repository's files into a brand new repository of your own. This is the one we use most in this course, because it hands you a working starting point instead of an empty folder.

To try the second one: go to github.com/dsgn425-fall2026/hello-world, click Use this template, name the new repository hello-world, leave it private, and create it. It lands in your own account in a few seconds, and it is yours.

Have a look around. A repository is a folder of files and folders, and not much more than that. You can add files and edit the ones already there, right in the browser.

Your first commit

Open README.md and click the pencil icon. Change anything you like, then scroll to the bottom, to Commit changes.

The first box is the commit message. It is pre-filled with something generic like Update README.md, and you should replace it. The message is a note to your future self and to everybody else about what you did and why, and it is the single most durable piece of communication in a codebase. Write what changed, in plain words.

Click Commit changes. That takes the work you did, one coherent unit of it, and records it permanently in the repository's history.

A commit is a snapshot with a note attached. That is the whole idea, and everything else in Git is built out of it.

Committing straight onto the project the way we just did is fine when the project is yours and nobody else is touching it. It stops being fine immediately after that, and the rest of this post is what teams do instead.

Branches

A repository has a main line of development, called main. Treat it as the official version: the one that works, the one that ships, the one everybody else pulls from.

A branch is a separate line of work that starts from a copy of main at a moment in time. You make one, you do your work on it, and main is untouched the entire time. When the work is done and somebody has looked at it, the branch gets folded back in.

The reason this matters is not tidiness. It is that real work is unfinished for most of its life. Halfway through a change, the code is usually broken, and if you were working directly on main then everybody else is now working on top of your broken half-change. A branch is where something is allowed to be incomplete without hurting anyone.

The second reason is that several people can do this at once. Five people on five branches are not in each other's way, and the question of how their work fits together gets asked once, deliberately, when each branch comes back, instead of continuously and by accident.

Two practical notes. Name the branch for what you are doing, not for yourself: add-search tells a colleague something, bens-branch does not. And do not hoard a branch. The longer it sits away from main, the more main moves underneath it and the harder the eventual reconciliation gets. Branches are cheap and meant to be short-lived, which is the opposite of
the instinct most people arrive with.

Pull requests

Here is a distinction worth holding onto, because it follows the Git and GitHub split from the top of this post: branches and commits are Git. Pull requests are not.

A pull request is a GitHub feature, built on top of Git. It is a request, addressed to the people who own the project, saying: I did this work on my branch, please pull it into main. GitLab calls the same thing a merge request, which is arguably the better name.

It is three things at once, and the third is the one people miss.

1. A proposal. Nothing has happened yet. main is unchanged until somebody merges.
2. A place to talk. The pull request shows the diff: every line added, removed or changed. Anyone can comment on the whole thing, or on one specific line. Conversation happens against the actual change rather than in a meeting about the change.
3. A record. It survives. In two years, when somebody asks why a thing works the way it does, the pull request is where the answer is: what changed, who asked for it, what the objections were, and who decided. Most of the institutional memory of a codebase lives in old pull requests.

On any serious team, main is protected, which means nobody can push to it directly, not even the person who owns the company. The pull request is not the polite option. It is the only door.

Code review

Somebody other than the author has to look at the change and say yes. That is code review, and it is the part of this that will follow you furthest, because you may well end up on the approving side of it without ever writing the code yourself.

A reviewer opens the Files changed tab and answers three questions:

1. Does the change do what the pull request says it does?
2. Does the commit message tell you what changed without opening the diff?
3. Would you merge this?

Then they leave at least one comment on a line, and submit the review as either Approve or Request changes. If they requested changes, the author fixes it on the same branch and commits again, the pull request updates itself, and the reviewer looks again. That loop is the job.

Notice what is not on that list. Nothing about whether the code is clever, and nothing that requires you to be able to have written it yourself. Review is mostly about intent: does this do what it claims, would somebody understand it later, and am I willing to put my name on letting it in. Those are judgment questions, and they are the same questions you would ask about anything else your team proposes to ship.

The checklist grows as the course goes on. The three questions do not change.

One rule that is worth understanding rather than memorizing: GitHub will not let you approve your own pull request. Nobody reviews their own work. That is not a technicality, it is the whole point of the mechanism.

The loop, in one place

Every change to every serious codebase goes through some version of this.

1. Branch. Off main, named for what you are about to do.
2. Change. On the branch, where being half-finished is safe.
3. Commit. One coherent unit of work, with a message that says what it was.
4. Publish. The branch goes up to GitHub.
5. Pull request. The proposal to change main.
6. Review. Somebody else reads it and says yes, or says not yet.
7. Merge. It lands on main.
8. Delete the branch. It served its purpose.

You will run that loop by hand a few times in this course until it is boring. That is the intended outcome. The mechanics are meant to become automatic so that your attention can go to the only part that actually needs a person: step 6.

Where this goes next

• Practice: GitHub to run the whole loop once on your own, with nobody else's repository at risk.
• Open source and licensing, the longer version, if you came here from the class and skipped it.
• Command-line basics and the development environment for what comes after the browser.

PREPARING YOUR APPLICATION

In your Gemfile, it may already look like it does below (specifically with the sqlite3 gem nested inside a group :development block and a pg gem nested inside a group :production block).  If not, delete gem "sqlite3", "~> 1.4" and add the following:

group :development, :test do
  gem "sqlite3", "~> 1.4"
  # note: keep other gems that were already in this group (e.g. "debug")
end

group :production do
  gem 'pg'
end

Note: there was likely already a group :development, :test block in your code. If so, keep that code and just add the sqlite3 gem to that group.  Don't delete any other gems in the group.  For example, your code may now look like this:

group :development, :test do
  gem "debug", platforms: %i[ mri mingw x64_mingw ]
  gem "sqlite3", "~> 1.4"
end

Once you've moved the sqlite3 gem and added the pg gem, execute the change in your Gemfile by running:

bundle install

These changes tell your Rails application to use the industrial-strength PostgreSQL database in production, instead of the sqlite3 database we've been using for development.  The database will behave the same for our purposes, but is ready for production-level traffic and future scalability.

Next, you'll need to update the config/database.yml file.  With the pg library installed for production, we also need to configure the app so that it knows where to find the production database.  Currently, it lives in a file in the db directory (db/development.sqlite3), but in production the database is managed separately.

If it's not already there, add the following to the config file:

production:
  <<: *default
  adapter: postgresql
  database: my_app_production
  username: my_app
  password: <%= ENV["MY_APP_DATABASE_PASSWORD"] %>

Replace my_app in the example with the name of your app - in this case tacogram_final would be appropriate.  Be sure that the database value still has production appended to it.  And MY_APP in the password should be TACOGRAM_FINAL in all caps.  As an example, it might look like this:

production:
  <<: *default
  adapter: postgresql
  database: tacogram_final_production
  username: tacogram_final
  password: <%= ENV["TACOGRAM_FINAL_DATABASE_PASSWORD"] %>

Last step is to commit these changes and push them to Github.

RENDER SETUP

Sign up for a Render account at render.com - use your Github credentials if possible.  You may have to enter payment info, but you'll be using the free tier.  During signup, you'll be asked several questions about how you will use Render - you can answer them however you'd like.  Once complete, you'll be on your Render dashboard.

You'll need to create 2 services - one for the database and one for the application.   Create the database service first.  Click "Create PostgreSQL" on your dashboard (or using the "New +" button in the top right).

In the form, enter a unique name for your new database - it's helpful to use your application name followed by "-db" to easily identify it later.  For example, it might be tacogram-final-db.

Ignore the other settings in the form until the Instance Type at the bottom.  Select the "Free" type and click "Create Database".  Note: Render only permits 1 free database service.  When you want to create a database for another application, you'll first need to suspend/delete this one (or upgrade to a paid plan).

On the next screen, wait for the database service to complete its setup.  Once it's available, scroll to the "Connections" section, find the "Internal Database URL" value and copy it - you'll need it in the next step.

Next, go back to your dashboard and click the "New +" button in the top right to create a new Web Service.

When asked "How would you like to deploy your web service?", choose "Build and deploy from a Git repository".  On the next screen, you'll be asked to "Connect a repository" - you should see your Github repository listed, but if not, click "Configure account" on the right to authorize access to your Github account.  Once you've found your application's repository in the list, click "Connect".

In the following form, the "Name" field will be pre-populated for you based on the name of your application.  However, this must be unique across all of Render, so you may need to change the name slightly (e.g. tacogram-final may already be taken).  You can leave the next few settings unchanged.  Then, in the "Build Command" field, enter the 2 commands that should run each time you deploy a new version of your application: bundle install; rails db:migrate (the semi-colon separates the 2 commands).

Next, change the "Start Command" field.  This is the command that will run to start your application server, just like you do in Codespace: rails server

Then, choose the free Instance Type option.

The last change is adding environment variables needed to run the application - these are variables stored on the "server" instead of in our code and generally used to run the application or for sensitive data we don't want to store in our code (e.g. API keys).  The variables you'll need to add are:

• DATABASE_URL
• SECRET_KEY_BASE
• WEB_CONCURRENCY

The DATABASE_URL value should be the "Internal Database URL" value copied from the database service.  The SECRET_KEY_BASE value can be anything - your application name will work.  And, the WEB_CONCURRENCY value should be 2.

That's it.  You can click "Create Web Service" to complete the setup.  Render will pull your latest code from Github and begin to install all of the necessary libraries.  It will automatically run bundle install and rails db:migrate.  And then lastly, it will run rails server.  If all goes well, you'll see "Your service is live 🎉" in the logs.

From now on, each time you push new code from Codespace to Github, it will be deployed to Render as well.  You can see any deployment events listed in the "Events" tab.

Congratulations!  Your app should now be live!  There's a link to visit your live production site towards the top.  Note the url for future reference - it will be something like my_app.onrender.com where "my_app" is the unique name you entered previously (e.g. tacogram-final-tues).

DEBUGGING

Render is really just a big, industrial Linux-based computer – just like Codepsace, but more powerful.  It's worth exploring the features of your Render services to discover what's possible.  One feature that's very useful is access to the logs (see the "Logs" tab just below the "Events" tab in Render.  As users interact with your application, you will see the output to the server log there.  Just as you did with the rails server log in Codespace, reading these logs will be helpful when you inevitably find a bug in the live app.  You won't see the usual error message that you've seen in local development - that would be a bad user experience.  Instead you'll see a simple 404 error screen with no details about what went wrong.  

To see the actual error message, you'll need to look at the server log.  Render makes this especially easy by providing search and filter functionality for your logs.

CUSTOM DOMAINS

If you're interested in adding a custom domain to your application (e.g. tacogram-final.com instead of tacogram-final.onrender.com), you'll first need to purchase a domain (or use a domain you already own) at a domain registry provider like Namecheap.  Then follow Render's instructions to configure the domain to point to your server on Render.

It's Your World

The actual, physical world that is all around us is composed of many, many, billions of objects – living or not – each with their own behavior, attributes, and idiosyncrasies. It's the job of scientists and domain experts to study these objects and the ways in which they interact with each other, and to develop theories and laws to describe their observations, so that we can better understand the way the world works. It's all very complicated.

The world of the software we create is not nearly this complex. Everything that exists in this world is entirely up to us. We define what objects live in this world, their attributes and behaviors, and their relationships with one another. The result is a domain model of our own creation, and it can be as simple or as complex we need it to be.

From the Wikipedia article on domain modeling:

A domain model is a system of abstractions that describes selected aspects of a sphere of knowledge, influence or activity (a domain). The model can then be used to solve problems related to that domain. The domain model is a representation of meaningful real-world concepts pertinent to the domain that need to be modeled in software. The concepts include the data involved in the business and rules the business uses in relation to that data. A domain model leverages natural language of the domain.

A domain model generally uses the vocabulary of the domain, thus allowing a representation of the model to be communicated to non-technical stakeholders.

Oof. That's a lot of words. Let's TL;DR this thing. A domain model:

• Is a real-world concept (aka domain) represented as software
• Contains only the data and rules involved in the domain
• Is described using the actual words (i.e. vocabulary) used by those working in the domain

It's important to note that a domain model doesn't need to include everything in the domain – only the pertinent bits we need right now in order to build our system. We can always add more later – it's just a CREATE TABLE statement, right? But how do we determine what is needed right now? Let's take a look at this through the lens of a startup.

Minimum Viable Product (MVP)

Popularized in the early 2000s, most notably by Eric Ries in his book The Lean Startup, the Minimum Viable Product (MVP) approach to building software products has become the de-facto standard in the startup community. There is plenty of in-depth reading and research we can do on the topic, both in-print and online; we're going to give you the highlight reel here.

In short, building an MVP means that, when building a new software product, we build only the features necessary (minimum) in order to get feedback from real users (viable). Build any more than those core features, and you're just wasting time and money building something that customers/users probably don't want anyway.

How do we begin to enumerate what those core features are, then? To get things started, it can be useful to create user stories.

User Stories

Our product's user stories is a written document that, in a regimented way, describes the core features in a user-centric style. How does a user of your system deal with their problem, perform their task, or otherwise receive value by interacting with it?

Let's begin by looking at the typical template for writing a user story:

As a [some user role], I want to [some goal], so I can [some value]

Pretty vague, right? Don't worry, it will make more sense once we look at a practical example.

Let's continue with the idea of a "school" system that we started building the database for in the last lesson. We'll begin with a top-level, overall vision for the system. Perhaps this is something that's written down, articulated in some way, or just something we're thinking about:

This will be a system used to manage our school. A student in our school can use the system to browse the available courses and enroll in them, based on the dates/times that best fit their schedule. Students are added to the system by teachers. So are the courses they teach.

From there, we can begin the process of transforming that general vision into the user story format. Let's look at a single story, one that we could argue is truly the core purpose of the system:

As a student, I want to browse available courses, seeing the course description and bio of the instructor, so I can decide if I want to enroll in it.

By going through this process, we've immediately transformed the somewhat loose, general vision into a concise story that clearly articulates what that part of the system is supposed to do from a user perspective, and the value it creates for the user. We can also see that we're using the language that's specific to this domain, that is, we're describing things in the vocabulary that would be typically used by the people using this system.

If you like, you can stop reading here, and see if you can come up with the rest of the stories.

Here's the list that we came up with:

• As a student, I want to browse available courses, seeing the course description and bio of the instructor, so I can decide if I want to enroll in it.
• As a student, I want to see the available sections (dates and times offered) and enroll in the section of my choosing, so that I can enroll in the course I want at the time that best works for my schedule.
• As a teacher, I want to be able to maintain (add, edit, and remove) a list of students at our school, so that we know who our students are, their contact information (email and phone number), and provide access to enroll in classes.
• As a teacher, I want to be able to maintain (add, edit, and remove) a list of teachers (including myself) at our school, so that we can manage teachers' bios and a list of classes they teach.
• As a teacher, I want to be able to maintain (add, edit, and remove) a list of courses and the sections of those courses, so that we can publish it for students to browse and enroll.

In the end, there may be more stories, or a need to make certain stories more specific, but this is certainly a good list to start. Again, all we've done is taken a rather general vision for our overall product and its purpose, and used the user story template to help us think about what the pieces are, and how to articulate them in a way that illuminates the value of each part of the system.

After getting these stories down on paper, it's time to evaluate. If we have too many stories, this process may help us decide how to shave the list down to only the parts of the system needed to create value and receive feedback. If we have too few stories, this helps us learn what we'd need to add to make this a valuable system for our potential users.

It's important to note that user stories are not meant to be some sort of formal document that gets etched in stone; rather, it's more of an iterative process that help us to continually think about what the system is really supposed to do and the value it creates.

Wireframing

Wireframes are another great tool for iterating on a system's design. Once we have our user stories on paper, we can then create a visual representation of the user's experience, i.e. what will the system look like to the user?

We can use practically any tools we're comfortable with to draw our wireframes. These tools might include:

• Pencil and paper
• A presentation tool like Powerpoint or Keynote
• Graphics/design apps like Figma, Sketch, Adobe XD or Photoshop
• Wireframing tools and services like Balsamiq Mockups, Justinmind, or wireframe.cc
• If you already know it, HTML and CSS

The tool doesn't matter. The goal is to transform our user stories into a visual of what the finished MVP might look like, so we can discover more detail than we knew before, or to iterate on what the needed functionality might be. It's very common to return to the user stories and make changes after wireframing.

Here's an example of a wireframe for our school enrollment app, created using Balsamiq. For the purposes of domain modeling, there are no specifications or formal process for wireframing; it's really all about helping to visualize what we've already articulated using our user stories.

Models

User stories and wireframes is a way to think about and discover all the entities in your world, as well as the relationships between them. We'll now shift that thinking to building our database. From now on, we'll call each real-world entity a model. And all of our models, together with all of their relationships, is called our domain model.

What are the real, tangible models we're dealing with in our school system? That is, things that really exist in the real world, outside the realm of software? From our user stories and wireframes, a couple of obvious ones might be:

• Student
• Teacher

Students and teachers do not exist in theory. They exist in the real, physical world. Students are the people who attend our school. Teachers are people who teach the courses.

But you may be wondering, why we don't we have a single model for People? After all, a student and a teacher are both people. This is a valid question and one that is quite nuanced in nature. It's because models are distinguished by their unique attributes. According to our user stories/wireframes, teachers have bios. Students do not. Students have contact information (email/phone number). Teachers do not. We might decide later that students need a bio, too – at which point, we'd go back and iterate on our user stories. But not right now.

Let's try to identify the attributes – the data needed for – these two models.

• Student – name (first/last), email, and phone number
• Teacher – name (first/last), bio

Database-Backed Models

If you've put 2+2 together, based on our last lesson on SQL, a model and its attributes can be neatly represented in a database table.

• Each column represents a particular model attribute
• Each row represents a unique model instance

Each model will need a different table, because the column definitions will be different for every model. The start of our domain model, along with some sample data, might look like this:

students

teachers

A decent start. Let's add the model for a Course. A course, while not necessarily a real, physical thing like a student or a teacher, is an entity that really exists that we need to retain data for – that is, it has attributes. We can also create instances of it, i.e. each row in the database table represents the data for each available course. Let's try and determine the attributes of a course. What data would we need to keep track of for every course, according to our user stories and wireframes?

• A course name
• A course description

And that's all for now:

courses

Relationships

At this point, you might be asking yourself... wait, is that all the data needed for a course? What about the sections – the dates and times the course is available? Or the teachers who teach the course?

Knowing the difference between what belongs as an attribute on a model and what should be separated into its own model... is one of the keys to being good at domain modeling.

Is it possible to add the dates/times and teacher of each course to the Course model? Sure, let's see what that would look like:

courses

Seems perfectly reasonable. But what happens when the demand for Taco-Making becomes so large that we add a third time? Or a fourth? Or a 10th? While we could, of course, continue to grow the courses table horizontally, it turns out that it's generally a much better idea to grow a database vertically. Exponential horizontal growth of a database table is an indicator that we should be doing something different.

What's we seeing here is a one-to-many relationship – one course can have many times it is available, each with a different possible teacher – i.e. Sections.

Remember a few paragraphs ago, when we defined what a domain model is? To refresh your memory:

... we'll call each real-world entity a model. And all of our models, together with all of their relationships, is called our domain model.

The Section model, in this particular case, is not so much a tangible, real-world thing. Rather, it's a model that's made necessary due to the relationships between our real-world entities.

It makes sense for Section to be its own model because:

• We don't know how many sections of a course there could be. It might be none, only one, or a million.
• A section has its own attributes, like the time it's offered, and the teacher who teaches it. This list of attributes could potentially change in the future, making it very difficult to manage if this data lived within the Courses model.

courses

sections

Aaaahhh! So much better. Now the Course model only holds information about the course itself, delegating the data about each section to the Section model. We can now support as many sections as needed without resorting to changes in our database design.

We can also see that, in order to create a one-to-many relationship in a domain model, we simply need to add a foreign key column on the "many" side of the equation. In this example, because one course has many sections, we add a course_id to sections and we're there.

And yes, there are two foreign keys on sections, because there are actually two one-to-many relationships where Section is involved.

Here we see the last piece of the puzzle:

Two one-to-many relationships = One many-to-many relationship

A course can have many teachers. A teacher can teach many courses. The relationship between our Course and Teacher models is a many-to-many relationship, and the Section model is known as their join model.

Naming Conventions

We've seen enough of domain modeling that it now makes sense to talk about the conventions we've been using. Although there is not a technical reason to adhere to these conventions, it is certainly a set of best practices that we will continue to follow for the remainder of this course.

• When talking about models like Course and Section, we write them in the singular form, and beginning with a capital letter – it will make sense why we do this later in the course.
• Database table names like courses and sections are always plural, and in all lower-case.
• Column names are always in lower-case, e.g. name or description.
• If a table or column name is made up of more than one word, we separate the words by underscores, e.g. first_name or teacher_id.

Lab

Begin with the template project on GitHub, create a new repo from the template, and open this repo in Codespaces.

• Modify the school.sql script to make the courses, teachers, and sections tables real (the statement that creates the students table is already there). Execute the script by running .read school.sql at the SQLite prompt, and execute the .schema command to ensure that everything went as expected.
• Design the model that allows students to sign up for classes – let's call it Enrollment – do it on paper, in Excel, or any other tool of your choice.
• Add the enrollments table you designed above by writing a CREATE TABLE statement in the school.sql script, and execute the script.  (For reference, you can find the SQL for adding tables here.)

A Customer Relationship Management (CRM) system is one that allows users (typically sales teams) to keep track of companies/contacts and the interactions (emails, sales calls, in-person meetings, etc.) the user has with each contact. If you've worked in sales before, chances are you've used some kind of CRM system – e.g. Salesforce, Zoho, Freshdesk, Pipedrive, or about 1000 others. In this case study, we're going to create the domain model for our own simple CRM.

Here are the user stories and wireframes:

• As a salesperson, I want to maintain a list of contacts (along with each contact's name, email address, and phone number), so I know how to reach each person.
• As a salesperson, I want to be able to log each activity (e.g. calls/emails, with a date/time it occurred and my notes.) I have with a contact, so I can keep a diary of all the communication I have with each person.
• As a salesperson, I want to manage a list of companies (name), so I know all the companies we sell to.
• As a salesperson, I want to associate contacts with a company, so that I can get a company-wide view of my sales team's communication with all the people at a company.
• As a salesperson, I want to maintain my own contact information (first/last name and email address), so that other members of the sales team know who I am.

What real-world entities are we dealing with? Once those are determined, what relationships or join models are needed to connect the dots?

Jot your thoughts down on paper or tool of your choice (sometimes visual is a better way to get started than a bunch of CREATE TABLE statements), then write the script to create the actual SQLite database.

Go Further

Once you've designed a database to support the initial user stories and wireframes, here's a challenge to flesh out our domain model.

Additional user stories:

• As a salesperson, I want to maintain a list of industries, so that I can see how my sales team is performing across the different industries we do business with.
• As a salesperson, I want to indicate a company's industries (1 or more), so I can categorize the companies we sell to by industry.

Updated wireframe:

Tacostagram will be all the rage when it launches. People from around the world will be able to create a constantly evolving feed of photos of their favorite tacos. People will be able to "like" photos of tacos and write comments about each photo. Eventually, we will sell it to technology behemoth MetaTaco for $1 billion.

• As a user, I want to create time-stamped posts of tacos, so that I can share them in a "feed" for the world to see.
• As a user, I want to be able to "like" another user's post, so the author of the post and others will know that I liked it.
• As a user, I want to be able to see how many likes a post has, so I can see how popular it is.
• As a user, I want to not be able to like a post more than once, so I cannot artificially inflate the popularity of a post.
• As a user, I want to be able to comment (and see other's comments) on a post, so I can engage in conversation with the author and others.
• As a user, I want to be able to "follow" another user, so that I can stay up-to-date on certain users' updates.
• As a user, I want to see only my followed users' posts in my feed, so I can only see posts that I'm interested in.
• As a user, I want to sign up with my username (screen name), real name, and location, so that other people will see that information when visiting my profile.

Note: we are going to ignore the mechanics of how a photo gets uploaded and stored, and simply use a filename for the time being, e.g. tacos.jpg.

As with the last exercise, jot down your domain model visually first, then move on to the physical creation of the SQLite database.

These are the entities we came up with, based on the current user stories/wireframes:

• Salesperson – the salesperson using the system
• Contact  – a person that I have sales interactions (activities) with
• Company – the company that a Contact works for
• Activity – the interaction (like a call or email) we have with a Contact
• Industry – the market (like "Consumer Electronics") a company can belong to

If we begin our data model design, it starts by looking something like this:

CREATE TABLE salespeople (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  first_name TEXT,
  last_name TEXT,
  email TEXT
);

CREATE TABLE contacts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  first_name TEXT,
  last_name TEXT,
  email TEXT,
  phone_number TEXT
);

CREATE TABLE companies (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT
);

CREATE TABLE activities (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  occurred_at TEXT,
  notes TEXT
);

CREATE TABLE industries (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT
);

And we also have these relationships:

• A one-to-many relationship between Company and Contact; that is, a company can have many contacts, but a contact belongs to only one company
• A one-to-many relationship between Salesperson and Activities; that is, a salesperson can have many activities, but a single activity belongs to only one salesperson
• A one-to-many relationship between Contact and Activities; that is, a contact can have many activities, but a single activity belongs to only one contact
• A many-to-many relationship between Company and Industry; that is, a company can be categorized into multiple industries, and an industry can have many companies

As we learned in the last lesson, from an implementation perspective, a one-to-many relationship is built by adding a foreign key column to the "many" side of the equation:

CREATE TABLE salespeople (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  first_name TEXT,
  last_name TEXT,
  email TEXT
);

CREATE TABLE contacts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  first_name TEXT,
  last_name TEXT,
  email TEXT,
  phone_number TEXT,
  company_id INTEGER
);

CREATE TABLE companies (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT
);

CREATE TABLE activities (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  occurred_at TEXT,
  notes TEXT,
  salesperson_id INTEGER,
  contact_id INTEGER
);

CREATE TABLE industries (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT
);

And, a many-to-many relationship is actually two one-to-many relationships put together with a join model; in this case:

• A Company has one-to-many Industries
• An Industry has one-to-many Companies

What should we call the join model?

There are only two hard things in Computer Science: cache invalidation and naming things.

– Phil Karlton

Many developers like to reference this quote – a lot. Naming things is indeed very hard. What's a word we can use to describe a company/industry combination? Turns out that there really isn't one – not one that the majority of people use and would understand right away and without much explanation. So we're simply going to refer to this as an Industry Membership. Our final domain model, implemented in SQL, looks like this:

CREATE TABLE salespeople (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  first_name TEXT,
  last_name TEXT,
  email TEXT
);

CREATE TABLE contacts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  first_name TEXT,
  last_name TEXT,
  email TEXT,
  phone_number TEXT,
  company_id INTEGER
);

CREATE TABLE companies (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT
);

CREATE TABLE activities (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  occurred_at TEXT,
  notes TEXT,
  salesperson_id INTEGER,
  contact_id INTEGER
);

CREATE TABLE industries (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT
);

CREATE TABLE industry_memberships (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  company_id INTEGER,
  industry_id INTEGER
);