So now we're going to talk about the different kinds of keys, and what keys are is kind of connection points. We connect one row to another using a key, or we will go find a row in a database table using a key. And there are three kinds of keys. So each row in a table gets what we call a primary key, and that's like to give us a handle to say that row. So that's like the AC/DC row is number 42. The Led Zeppelin row is number 75. csev@umich is number 120,453. Those are the primary keys, and that's that single string. And then this number is what we're going to replicate all over the place. So that's a primary key. Then there is, in most tables, what we call a logical key. And the primary key is not something. It might show up in URLs in your user interface or something. But the primary key is almost never something that you know what your primary key is, whereas the logical key is that's you, right? And so that is your email address, or the title of the track, or the title of the album, or the email address of the logged-in user, or something like that. So if you think of the outside world saying I've got to go find a particular person, or track, or whatever, they use the logical key. Or if it was in a user interface, and there was a search button, the thing you type into the search, that's the logical key, that's the thing that the world uses to find a row. And then the other thing that's sort of internal to the database is what's called the foreign key. And that is an integer in one table that points to a row in a different table. And that's why we call it a foreign key, that far away table is the "foreign" table, this is the local table. And we have a naming convention and different organizations and different pieces of software will use different naming conventions. And so I've just chosen one of the more common naming conventions, where in every table, in this case, the album table, the id field is going to be the primary key. And so we'll just know that no matter what we name our table, we're going to name the primary key field id. Logical key will be whatever, and we use a convention of foreign keys end in underscore id, and the first part is the name of the table. So I can look because of my convention that artist_id points to a row in the artist table. And when you go to an organization, an important part of database is a set of rules and conventions that an organization uses to keep themselves sane. And so you will go in, and they'll say okay, this is how we name our fields. And you're like, oh, wait, I was in college, and my professor named fields a different way. Don't say that. Just say oh, that's great, I really like working here. And then follow their rules, because ultimately, which set of rules you pick doesn't matter as much as being consistent within those rules. So I've picked a pretty common set of rules, and you'll go a lot of places, and you'll see the same rules that I've picked. But just because an organization uses a different set of database naming rules database column naming rules doesn't mean that they're wrong, per se. So the primary key is a somewhat counter-intuitive notion for a lot of people. You think to yourself, well, your email address is your email address, and why you wouldn't you use that everywhere? And there are some database experts that might even tell you that's a good idea, but it's not a good idea, those databases experts are wrong. The key thing is that the whole thing that makes this super fast, no matter what database system you're using, is this mapping from a string to a number. And those people that would suggest that you could use a logical key as a primary key, meaning that you could put an email address everywhere, what they're really doing is using a database layer to kind of fake it, to fake that. And there's still a number, it's just a number you're not dealing with. So you might as well go ahead and deal with the number. And things like email addresses change. Here at the University of Michigan, you can come, and you can get an account, and you can get married, or divorced, or just not like your name. Because we make something up with a bit of your first name and a bit of your last name, and they don't all come out great, right? So you fill out a form, and you can change your logical key. But on the systems we have on campus, once you've started registering for classes, we can change your logical key. And remember, it's a string, if the system is properly architected, it's exactly one place. Now, at the university, you might have like 20 large systems. So if we change your email address, we've got to get ahold of all 20 systems, and say this person's email address on this day is changing from this to this. And then the 20 systems all change it, but within those systems, they have one place to change it. They change the logical key in the one table that is the user table, or whatever. And so be careful about believing too much about email addresses not changing, even though on average, they do change very slowly. And so if you're using as primary key like a string rather than a number, and people also use these things called GUIDs. Which are long strings of random numbers, and then join on GUIDs, those things are not as efficient, although in some databases, they kind of fake the efficiency. And so the simple thing is just use integers as your foreign keys, integers as your primary keys, and strings or whatever as your logical keys. And then no matter what database you're using, you're going to run very effectively. And like I said, a foreign key is a key that points to the primary key of another table. And so if we are pointing to a row in the artist table, we would name that artist_id. And again, I'll just say one last time, when the primary keys are integers, then foreign keys are integers, and matching them is something that systems can do very efficiently. So up next, we're going to start using these foreign keys and these primary keys as connecting together using what we call database normalization. We're going to like bring all of this together, and actually start kind of writing code.
