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
| id | first_name | last_name | phone_number | |
|---|---|---|---|---|
| 1 | Jane | Doe | jane@example.com | 555-1212 |
| 2 | Jenny | Smith | jenny@gmail.com | 867-5309 |
| 3 | John | Johnson | john@acme.com | 456-7890 |
teachers
| id | first_name | last_name | bio |
|---|---|---|---|
| 1 | Ben | Block | Often talks to a rubber ducky. |
| 2 | Brian | Eng | Loves tacos. |
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
| id | name | description |
|---|---|---|
| 1 | Intro to Software Development | This course is focused on software development... |
| 2 | Taco-Making 101 | In this course, you'll learn how to build a proper taco... |
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
| id | name | description | time_1 | teacher_id_1 | time_2 | teacher_id_2 |
|---|---|---|---|---|---|---|
| 1 | Intro to Software Development | This course is... | Wednesday 6:30-9:30pm | 1 | Friday 1:30-4:30pm | 2 |
| 2 | Taco-Making 101 | In this course... | Wednesday 6-9pm | 2 | Thursday 6-9pm | 1 |
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
| id | time | course_id | teacher_id |
|---|---|---|---|
| 1 | Wednesday 6:30-9:30pm | 1 | 1 |
| 2 | Friday 1:30-4:30pm | 1 | 2 |
| 3 | Wednesday 6-9pm | 2 | 2 |
| 4 | Thursday 6-9pm | 2 | 1 |
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
| id | name |
|---|---|
| ... | Cleveland |
teams (one row per team, per season)
| id | franchise_id | year | name | wins | losses |
|---|---|---|---|---|---|
| ... | → franchises | 1899 | Cleveland Spiders | 20 | 134 |
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.