Data Post 33: The Logical Model — Structure, Primary Keys, and Foreign Keys (Data Modeling Series #6)
Two students can share the same name, but never the same student ID — and that one small rule is what makes an entire database work.
We spent the last post talking about why we’re shifting gears from the conceptual model to the logical model, without actually diving into what that shift looks like in practice. This post is where we roll up our sleeves and do exactly that, using the same four entities we’ve been building with all along: student, teacher, course, and classroom.
Let’s start with what “getting more structured” actually means. In the conceptual model, we said a student has attributes like name, date of birth, and enrollment date, and we left it at that. In the logical model, we get specific about how each of those attributes is actually going to be structured, without yet worrying about which database software will store it. For our student entity, we’d now define first name and last name as two separate fields rather than one combined name field, since that tends to be more useful in practice, whether you’re sorting a list alphabetically or addressing someone by their first name in an email. Date of birth becomes a proper date field rather than something vague. Enrollment date gets the same treatment. This is the kind of precision the logical model is asking for, turning loosely described attributes into clearly defined fields.
Get Satti Data’s stories in your inbox
Join Medium for free to get updates from this writer.
Now let’s get into the part of this post that really matters: how do we make sure each of these entities can be uniquely identified, and how do we actually connect them to each other in a way that holds up? This is where primary keys and foreign keys come in, and honestly, once these two ideas click, a huge chunk of data modeling starts to make sense.
A primary key is simply a field, or sometimes a small combination of fields, that uniquely identifies each record in a table. Think back to our student entity. We could try to identify a student by their first and last name, but that falls apart the moment two students happen to share a name, which happens more often than you’d think, especially in a school with hundreds of students. That’s exactly why we gave each entity its own ID field earlier in the series. Student ID becomes the primary key for the student table. Teacher ID becomes the primary key for the teacher table. Course ID becomes the primary key for the course table, and room number, assuming it’s genuinely unique, could serve as the primary key for classroom. The whole point of a primary key is that no two records in that table are ever allowed to share the same value for it.
Foreign keys are where the real magic happens, because this is how one table actually reaches over and connects to another. A foreign key is a field in one table that holds the primary key value from a different table, creating a link between the two. Let’s use the relationship between teacher and course, which we already established back in the cardinality post as a one-to-many relationship, one teacher can teach many courses. To make that relationship real, the course table would include a field called teacher ID, and that field would hold the primary key value from the teacher table. So if a specific teacher’s ID is, say, 104, every course that teacher teaches would have 104 sitting in that teacher ID field. That’s the entire mechanism behind a one-to-many relationship in a logical model, the “many” side simply stores a reference back to the “one” side.
But what about the trickier relationship we flagged in the cardinality post, the many-to-many relationship between student and course? A student takes many courses, and a course has many students, and neither table can simply hold a single foreign key pointing to the other, because there’s no single value that would work. This is where a junction table comes in, and it’s worth understanding because you’ll run into this pattern constantly once you start working with real data. We’d create a new table, something like enrollment, and its entire job is to sit between student and course, holding two foreign keys: one pointing to student ID, and one pointing to course ID. Each row in this enrollment table represents one specific student being enrolled in one specific course. If a student takes four courses, that student shows up in four separate rows in the enrollment table, each paired with a different course ID. This is the structure that finally makes a many-to-many relationship possible, and it’s honestly one of the more satisfying “aha” moments in learning data modeling.
Once you’ve got primary keys identifying each record uniquely, and foreign keys (sometimes through a junction table) making relationships real, you’ve essentially built the skeleton of your logical model. What’s still missing is a bit of cleanup, since real-world data tends to have redundancy hiding in it that isn’t obvious yet, and that’s exactly what we’ll tackle in the next post when we talk about normalization.
