So now that you know about primary keys, logical keys, and foreign keys, let's start using them. So remember our goal was to be able to keep track of all this stuff. And so we're going to have a track table, a artist table, an album table, and a genre table. And we're going to get these little numbers. And then we're going to use these numbers to keep track of all this stuff. Because we're going to have to then reconstruct it from those numbers to create the vertical replication of the string data in the user interface just like our designer asked us to do. So you literally could spend months reading the concept of database normalization. Feel free, okay? I took a database class when I was in grad school and it just went totally over my head and I thought databases are a dumb idea. There's way too much complicated theory here. And then I went to work in a company and I sat in a room and by the noon on one day I'm like, I love databases. And that's because they taught me how you really do it rather than the theory. And I'm not saying theory's bad. Theory's awesome. That's why they're so fast. But from a technique perspective, it's pretty straightforward. First, don't replicate string data. Reference the data, point at data. It's a form of compression. Then use integer keys for your primary keys and for your references and put that primary key in each column, which we talked about already. So rows end up in these columns. And so when we're going to put AC/DC or Led Zeppelin in, we just say, okay, here's Led Zeppelin. And Led Zeppelin for all intents and purposes everywhere else in the system is going to be stored in a column called artist_id. And then that number goes in there. So Led Zeppelin is 1. AC/DC is 2. And once you insert them for pretty much the life of the system, although you can change them later if you really want. In general you will just use 2 to indicate AC/DC and 1 to indicate Led Zeppelin from that point forward in these foreign key columns. Now, you can have many sort of one primary key and you can have many foreign keys pointing to it. It's just in our data model, it's pretty simple. So each of these little arrows that we drew just kind of scribbling on the board becomes sort of this pair of numbers, a source number and a destination number or a parent and child is another way to think about it. So so we take this logical schema that we built of what belongs to what, which we just were talking and brainstorming and getting our data spread into more than one table. And now we're going to actually reconnect them together. And so we ended up with this picture that had these albums belonging to. And this was just a logical data model. And it doesn't even have to look all this elegant, but you have to take this data and split it into some number of tables, in our case four. And now we're going to show about the mechanics of making all these connections. And so it's pretty simple. You've got this arrow and we have to have a way in the arrow. There's no like in a database we say it's an arrow. They need columns. We need to have a column in there and that column is an integer. So we add the columns. So we augment the primary key and we add a primary key field to each one of the tables and then we have a logical key. And the logical key is just another column except we distinguish it. We just put a little asterisk by it. Say this is special. This is the one we're going to use when we have many rows to look up a particular row. Once we get to that row we'll find things like the rating, length, and count, right? And so the database by us telling that the title of the track is going to be something we're going to look up on, the database actually does stuff in how it represents the data by building what are called indexes to make it more efficient to look those things up, right? We talked about those indexes before. And so the logical key simply says something that we would like you to make an index for because we're going to use it a lot. And the faster you're capable of responding to lookups by title because we're not going to spend all that time looking it up by rating. We might sort by rating or whatever. But the thing we're going to look up and we expect to be really fast is the logical key. So logically it's just a column. It's a string column, a VARCHAR or whatever column. And these are just integer columns. But then to model the picture and this is what's called a many-to-one relationship, many-to-one. And there are many tracks that are on one album and it's like another way to think about this is it's somewhere between zero and infinite number of tracks per album. So that's also a way to think about the many side of this arrow and so on the one side of the arrow, we just put in this primary key and on the many side, we add a column. And we call it album_id where the first part of that is the name of the table and the second part of that is our little memory technique to realize oh, that's a foreign key and it's in the album table. And so that's how we sort of in an abstract way draw arrows. So we take and we map these arrows and we turn them into columns. So it's a pretty mechanical process. Literally once you got it figured out, pick the logical key, add the primary key, and add any necessary foreign keys based on the arrows that you've got. And so when you finish this over and over and over again, we had four tables. We had three arrows. And so we end up with one, two, three, four primary keys. We end up with a logical key in each table. And then we had three arrows so we end up with three starting points. And then we put foreign keys at the starting points of those arrows. And away we go. And so once you have the picture you're kind of like follow the technique. I can write these things super fast and then I can read them. They're really pretty. And again, I go back to the fact that I've used a convention to build all this stuff. And I can look at this like, yeah, of course, that's a foreign key. And it won't take you long and you'll start seeing the exact same thing that these are foreign keys. So now that we've sort of built the structure, now we're going to start typing some SQL commands so that we can insert create these tables with these special fields and then start inserting some normalized data.
