15.5 - Representing a Data Model in Tables

So welcome back to lunch, right? If you remember the morning the last lecture we argued, we drank a bunch of coffee, we scribbled on white boards, we erased white boards, and we argued about where the genre belonged. But when we were all said and done we ended up with this picture its still on the white board and we’re coming back from lunch now. This is now how databases represent data. We have to be a little more explicit. There’s no like belongs to magic in databases but when you’re building your data model you don’t want to be thinking explicitly too much about the database. You turn this “logical” model into what we call a physical model, by mapping it into the database. So, what we do is we just take each of the tables, and the relationships between the tables, and we have to augment them with little bits of information. And so, here’s the track table, and so we’ll make a table called Track. And we take like title, rating, length, and count, and they become just columns in the table. That’s the data we have to represent. And then we tend to add things to it. And so we’re going to add what we call a primary key. And a primary key is a way for us to refer to a particular row, and so it’s a unique number like 1, 2, 3, 4. So each album is going to end up with a number. We’ll put insert the title and the title will get a number. Right? And then we use that number in a column of a different table to sort of point to it. So these primary keys are the endpoints that we’re pointing to. So that we can create an arrow to a particular album, we’ve got to add a id to it. So we add a primary key. And then the other thing we do is we have to add the columns that are the other end of the arrow, the starting point of the arrow. And so I’m by convention, in this particular model, I’m naming primary keys all id. In track you’ve got to think all id and then album id is sort of the starting point. So album is the name of a table, followed by underscore, followed by id. Now, there’s no rules about how you name these things. These could be named X and Y if you wanted to but, as we’ve learned in programming Python, and other programming languages, making sensible names really reduces the cognitive load of understanding what the heck you meant when you were creating this. So we create a convention, a naming convention. And, the, we have terminology for each of these things. A primary key is the key for which there’s one key for every row. It’s used as the way to point to that row in other tables. The foreign key is one of these columns that we add to be the starting point of the arrows. The primary key’s the ending point of the arrow and the foreign key is the starting point of the arrow. The only other thing that we do is we may or may not designate one of the columns, sometimes more than one, as what’s called the logical key. The logical key is that unique thing that we might use to look up this row from the outside world. In this case, the album title might be a way we’d look it up. Like, oh, where is Led Zeppelin IV. Type in IV and it’ll search it. So Led Zeppelin IV is going to be the title. Now all we are doing is saying, hey database, we’re not looking these folks up by rating or length or count. But we might be looking tracks up by title. So, somehow on your storage maybe you leave some shortcuts to get to this more efficiently or more effectively. So the logical key is really our way of saying we might use this in a WHERE clause. We might use it in a WHERE clause, help me out out here. We might use it in a WHERE clause. And it also sometimes, it’s like we also might use it in an ORDER BY clause or a WHERE clause. And so that’s the logical keys. So we have these three things, primary keys, logical keys, and foreign keys. And so if we keep drawing these pictures it starts looking complex but then it turns out really simple. If we take the whole data model and sort of add these primary keys, logical keys, and foreign keys to model the beginnings and ends, the beginning and end of arrow, beginning and end of arrows, beginning and ending of arrows, we have to stick primary keys in all the tables. We stick foreign keys in those tables that have starting points. And then we indicate that certain things are our logical keys. It looks kind of complex, but once you’ve drawn the picture, and you have the naming convention for what you’re going to name these things, it’s a surprisingly manual task. The mapping from the logical to the physical is beautifully simple as long as you have a naming convention. Now, if you walk into an organization and you took this class and you say like Dr. Chuck’s naming convention is awesome. As a matter of fact, I stole this naming convention from a thing called Ruby on Rails. The name of the id, the artist id. It’s a good naming convention. And naming conventions are kind of like artistic. Some you think are prettier or not prettier, but any naming convention works. And you walk in and you go like oh, I took this class and I learned this naming convention of id and table id and they’ll say well we don’t use that, we use this thing where we start with a lowercase i and the we uppercase the iTrackID, that that would be their primary key for a track, or maybe they call it pTrack Id for primary key, right? So that would be the name of this thing and you’re in this company and you’re like whoa. pTrackId, that looks like crap. it’s a terrible, stupid idea. Well, don’t do that. I mean, this is not a superior naming convention to this other one, however they do this other one, right? What’s important is that there is a naming convention, and when you work for a company. just learn their naming convention, and don’t complain about their naming convention, unless you get to know them or maybe you’re sort of out for coffee or something later and you’re like, well, how come you guys never did the cool Ruby on Rails naming convention? I took a class and it was pretty cool as long as you’re nice and you don’t threaten them and don’t tell them how dumb they are by choosing the wrong naming convention. So now what we’re going to do is we’re going to turn this into a new table. Okay, and so I’m going to use the wizard to make new table in our little database. Let’s go over to, we’re still in our database. And I am going to go to Database Structure. Where’s the new table button? New table, new table. File. New table, create table. There we go. Edit, create table. Okay, so we are going to create a table, and I need to kind of look at I’m looking for my cheat sheet. We’ll start with the Artist table. The Artist table, and we’re going to add a field to it and we are going to name the first field an id field, and it’s going to be a number. We are going to say that it’s not null, it’s a primary key. We’re telling it that we’re going to use this a lot and if we’re linking into this table, that means we’re using it a lot. Auto increment, it means we as the programmer don’t actually have to specify this value, it’s going to be automatically specified. Because pretty much 1, 2, 3, 4, 5 are the values that we’re going to use. And then unsigned is whether it’s positive or negative. And for this one I’m going to leave that off. The other thing that’s in this in our little table, let’s go back and look at our little picture, is the name. Add another field and we’ll name that name. Oops. Lowercase name. And we will make that a text field. And in this case we’re not going to give it a length. And, there’s a way later we’ll give other ones lengths. And then we are going to hit the OK button. And when it’s all said and done we have an Artist table, okay? This is our old Users table, this is our Artist table. We could make a whole new database if we wanted, but we’re not going to, we’re just going to put all these things in. Actually, let’s just get rid of this table. Get rid of Users. Edit, Delete Table. Good bye User table. That was from the previous lecture. So now we have an Artist table. Okay? So that’s the wizard way of creating a table. The next table we’re going to create is the Genre table. Now the idea is we tend to work from outward in. The leaves of this tree outward in. So we created the Artist table then we’ll create the Genre table, then we’ll create the Album table. And then we’ll create the Track table because you kind of want to create the ends of the arrows before you create the beginnings of the arrows, okay? So we’ll create the Genre, and now I’m just going to use to SQL because that’s a lot quicker for me to type. Here’s the CREATE TABLE, we’re going to say that this guy is an INTEGER, it’s NOT NULL, it’s got a PRIMARY KEY and it’s AUTOINCREMENT, and it’s UNIQUE. And that is our way of saying give each row a little number. So we go to Execute SQL and I put this thing in here. And then I run it. It worked and if I take a look at our Browse Data or our Database Structure, Genre is in there and Genre has two columns, id and name. And then we have the other tables. So now we’re going to create the Album table. Now the thing that’s different about the Album table is that the Album table is our first example of a foreign key. So this is our foreign key. We’ve been putting primary keys in every table. Primary key pretty much say this over and over and over again, this long INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE. Just say that over and over and over again. Now, we’re going to have a foreign key which is the start, the foreign key, primary key. Right? And so, we’re just going to call that an integer. In other databases you can add some stuff to it, talk about what to do with the parent thing, but we’re not going to do that for now. We’re just going to say that’s an integer, that’s a starting point of an arrow. And we’re naming it artist id just to jog our memory that that’s what’s going on there. Put SQL, paste it, run it, poof she works. Database Structure. Now we got Genre, and let’s look at the last one. Okay? So the last one is the track. Okay? So CREATE TABLE, the name of the table. We’ve got this sort of thing that by now we’re cut and paste a bunch of times. We have a primary key, we have a logical key. It just happens to be green, it’s TEXT. And then we have two foreign keys. Because if you remember, there were two arrows coming out of Track. Both to Album and to Genre. So remember, oh, Album. album id, genre id. Naming convention is your friend. And then we just have some stuff that’s integers like the length, the rating, and the count. Then we put those in. So now we have the columns and the rows and all the foreign keys for Track. And then we will run that. And if we take a look at the data we will see we got all of them done. Album is now complex. And all these things. Wait a sec, did I not? What did I get wrong here? Oh, no, Track is the complex one. So Track has a bunch of stuff. So there we go. So if we take a look at, we’ve got all of these things, and we have translated our logical model into a physical model, and we translated the physical model into a series of CREATE statements. And we’ve run those CREATE statements, and now we’ve represented the various kinds of tables that we want to insert data into. So we’re going to stop here. But next we’re going to start inserting data into these tables.