Advanced PHP Linking Tables

Hello again, and welcome back to Educator.com's Advanced PHP with MySQL course.0000

In today's lesson, we are going to be talking about what is known as a linking table, which is a type of table in a database0004

that is going to allow us to move our departments database from a file-based system over into our MySQL database.0010

We are going to talk about the concepts of how we could model our departments in the database table,0018

which is going to progress us forward into this discussion of linking tables, which is the goal of this lesson.0024

As part of that discussion, we are going to be talking about a concept called database normalization,0031

which is a way to make your database work and act more efficiently.0036

One of the topics related to that is something known as foreign keys.0041

Then, we are going to talk about what a linking table is, and then also (related to a linking table)0045

how you can have a table with a primary key that is comprised of multiple columns.0050

Then, we are going to go over required homework that you are going to need to do, to set up your advanced_php database,0056

so that you can run the future versions of our sample web application.0064

We know that a Department object from our web application has three properties.0070

It has a department ID and a name, and then it has an indexed array of item ID's, where each item ID represents an item in the department.0079

How could we model this in a table?0088

There a couple of different things that we could do.0091

For example, we could create a table that maps one column to each property of our object.0092

We could have a column called deptID, which would be the primary key of the table: it is a unique ID for each department.0099

We could have a column called name, which would be the name of the particular department.0105

And then, we could have a column called items, which would be used to hold the information0109

about all of the different items that are in our particular department.0114

In this case, as has been the case in our departments.txt file, we have been storing all of the item information in a comma-delimited list0117

that shows all of the items in the particular department.0127

We could simply move that comma-delimited string into this items column of our department table,0130

so that here would be a department with three items, and here would be a department with two items, for example.0138

The problem with that that we are going to talk about in the next slide with database normalization is: it makes it difficult to search the database.0144

One of the reasons we use relational databases is because of the way it can quickly search information.0151

Well, now in order to look up an item, to see if, for example, item 1004 was part of department number 1,0156

we would have to pull this column up, using the SELECT query from the database,0163

and then parse through that, as we had been doing in our departments.txt file, extracting each of the items' ID's in that,0169

using, for example, the explode function that works on separating items by the comma delimiter.0177

And it just adds another step.0183

So, it takes away our advantage of using the database to automatically search for things.0185

Now, we have to write some manual code to parse that item string.0188

Another option, you might think, would be: let's have a deptID column again and a name column again.0194

But then, this time, we just create a column to store the item ID of each item in the department.0201

So, we would have a different column for each item in the department.0210

So, we might have one called itemID1, itemID2, and itemID3.0212

In this particular case, the first item, if it was the same exact data as up here, would have three values: 1001, 1004, 1007.0218

And so, all of those columns would be filled up.0227

However, our Electronics department only has two items, so it has this empty column here.0230

Well, let's say...right now, this limits us to only having three items in our database.0236

So, if we want it to be able to have a fourth item in the department, we would have to add another column over here,0240

which is going to involve restructuring our table, which is a procedure you want to avoid, if possible.0245

Additionally, if we add a fourth item (for example, 1008) to our first department, and it still only has two items for department #2,0253

now we have two empty spaces in our database.0263

And so, this is stuff that takes up space in our database.0266

And one of the goals is that you want to avoid having a lot of empty space within your database.0272

That is going to bring us to the concept known as database normalization.0279

And that is a process that you go through to structure your relational database to help reduce a number of these problems we have been talking about.0283

We talked about the difficulty with searching data.0292

It is under the concept called atomic values; in our first table that we had, where we had three item ID's all in one column,0297

that is considered non-atomic; atomic means a single value.0308

So, we had three values contained in one column, which makes it difficult to search.0311

If we have only one item ID in each particular column, that is known as an atomic data value; it makes it easier to search.0315

Database normalization is going to help us change the structure of our table, so that searching is easier.0322

It is going to avoid having a large number of empty fields.0328

We saw, for example, in the second table that we had, where, let's say, we have a new column for each item,0330

so that we can have up to four items, that if one of those departments only has one item in it, it is going to have a lot of empty columns.0337

And that is something we want to avoid in our database.0343

Normalization is going to help with that.0345

It is also going to avoid the problem of having to restructure our tables frequently.0348

As we saw before, the second table originally started so we were only able to have three items in a particular department.0352

Well, ideally, we would like to have as many items as we want in a department,0358

which means every time we wanted to add an additional item to a department--let's say right now our database enabled a department0360

to hold 5 items; well, now maybe we want 8 items in there--we need to restructure our database; and we have to continually do that.0369

Normalization is going to help us avoid having to do that.0375

And then, something else that wasn't mentioned on those other slides that it is going to help us avoid is something called data redundancy.0378

Let's say, for example, we had a table called Orders that represents an order that a user places on our web store.0384

Well, maybe we have a primary key for the Orders table called orderID, which is a unique ID for that particular order.0397

We would store the amount of the order in a column.0403

We would store the customer's name, and then their city and state.0406

We would probably have more information here, like address, apartment number, and stuff like that.0410

But because of the size of this slide, we are just going to limit it to this; but I think you can see the point.0413

Let's say, for example...one of the things about having customers on our website, or people placing orders on our websites,0419

is that we might have the same customer place multiple orders.0425

And that is something we would want.0429

For example, let's say our first order was placed by Joe Smith, from Houston, TX, for $150.0431

Then Jane Jones comes along, and she places an order for $200, and she is from Boston, MA.0436

Joe Smith comes back to our web store; he says, "I like this store; I want to buy something else."0441

So, we add another row to our Orders table.0446

It is going to have the amount that is ordered, and then it is going to have Joe's name and his address again.0449

Well, this right here is redundant data; assuming it is the same customer (because there could be two Joe Smiths from Houston, TX),0455

we have that same data in our database twice.0463

For example, let's say we wanted to update Joe Smith's address information.0467

And let's say it was stored in this Orders table.0474

We would have to go through, and we would have to update this row here, and we would have to update this row here.0476

Maybe he becomes from Boston, MA.0480

So now, because we have the same data in multiple spots, we have to make multiple updates.0484

And also, what it does is: now, we have this information that is stored in our database that takes up space on a server.0489

This data is stored in memory somewhere, so we have the same data stored in two different spots,0496

when really we could have it stored in one spot, which we are going to see on the next slide.0505

And theoretically, maybe Joe could place 10 different orders; now, we have this same information in 10 different spots within our Orders table.0511

We have the information repeated multiple times, and then any time we want to go and update it, we have to update it in multiple spots, as opposed to one.0518

Well, there is a way we can improve on that, and it uses a concept known as a foreign key.0524

What a foreign key is: it is a column in one table that refers to the primary key column in another table.0532

For example, let's say we changed our Orders table so that it would just have the orderID; it would have the amount;0538

and then, it would have this other column called custID.0546

And you can see that it is in italics; typically, italics, in these database descriptions, represents a foreign key.0549

And what this customer ID is going to do is refer to the primary key of a row in another table.0555

For example, if we instead move all of the information about Joe Smith and Jane Jones into its own Customers table,0563

we can create a new table called Customers that has a unique ID for each customer, which would be the primary key of that table.0570

And then, it has columns for their first name, their last name, their city, and their state.0577

So now, what we can do is: instead of having to have Joe Smith from Houston, TX written out here and here in our Orders table,0582

we simply can use the primary key ID from the Customers table.0591

And we can insert it into this foreign key column of our orders database.0598

And what that is going to do is link this order to Joe.0603

So now, what we have is: we have Joe Smith's information, but instead of having it twice in the table,0609

by having his customer ID in two spots in this customer ID column of our Orders table,0613

they both can link to the same spot; we have it in one place.0620

If Joe changes his address, we only need to update it here.0623

Additionally, we don't have that in two different spots.0628

That is what a foreign key can be used for.0630

One thing to note is that, if you are going to use a foreign key (which, again, is a value that represents a primary key in another table,0633

so we know a primary key value indicates a unique row in a table), if this is a foreign key,0643

it is going to indicate a unique row in another table (in this case, a unique row in Customers).0648

You just have to make sure that the data types match up here.0654

For example, if the custID primary key column of our Customers table was an unsigned integer,0657

then you would want the foreign key column to also be an unsigned integer.0664

You wouldn't want it, for example, to be a VARCHAR.0673

You want the types of those to match up.0679

And so, that is how a foreign key can be used to link one row in a table to a row in another table.0680

We are going to expand on that a little more, to come up to the concept that this lesson is mainly about,0687

or the ultimate goal of this lesson, which is known as a linking table.0693

What a linking table is: it is a table where each row of the table contains two or more foreign keys, representing rows in another table.0696

That is probably best explained by an example.0707

Let's say we create a Departments table; and this is the goal of this lesson--0709

we are trying to efficiently model our Departments table in our database.0713

And as we saw earlier, the way we were doing it in the first slide, it wasn't really an efficient method.0718

And we saw, from the rules of database normalization, that there were a lot of problems with that.0723

This is a solution that is going to solve that problem.0727

Let's say we create a Departments table that, instead of having an items column, is just going to have two columns:0731

a primary key column for deptID, and then the name of the department.0740

Well, we know, also, that we have an Items table in our database that has a primary key itemID,0744

has a name, has a price...I have left off the rest here, just so this would fit on the slide.0751

But we know that we can identify an item in the Items table by its primary key.0755

Similarly, we can identify a department in this Departments table by its primary key.0760

Well, now what we can do is: in order to add items to a department, we can create a linking table.0766

And in this case, it is going to be called depts_items, and that is typically the case for linking tables:0774

the name of the table contains the names of the two tables you are trying to link together.0780

And what we can do is: for each item in department 1, we can add a row to this table.0785

We can say, "Department 1 contains item 1001."0790

Now, deptID 1 is a foreign key that is going to refer to the primary key of the departments table.0795

Item 1001 is a foreign key that is going to refer to item 1001 in the Items table.0804

Now, what we can do is continually add, for each item in the department...0812

We want to add item 1004 to department #1; we simply create a new row0815

that has the department ID of the item we want to add to, and then the item ID.0820

The same thing we can do for department 2; we can add two items to it, as well.0825

Now, what this allows us to do is: now, we can add as many items to a department as we want.0828

We could have department 1 have 4 different items in it, whereas department 2 only has two items in it.0834

That is going to keep us from having to restructure this department table by adding a new column every time we want0843

to increase the capacity of the number of items in a column.0849

Additionally, it reduces the empty space in a table that would be stored for item 2.0852

Whereas it only has 2 items, if we had a table that would allow 4 items to be in a department,0859

there would be, for department 2--the row for that department would have 2 empty spots in it.0863

So, we have eliminated that empty space that is not needed.0867

And then also, we have allowed our application to grow to where we can put as many items in a department as we want0871

without having to restructure our particular table.0877

And so, this is how we are going to model our departments in our database.0881

We are going to create this Departments table that is just going to have the ID and the name of the department.0884

And then, we are going to have this separate linking table that we are going to use to add items into a department.0889

And the way we do that is: we use the primary key of the deptID and the primary key of the itemID we want to add to it,0898

and put those in the same row in this depts_items table.0906

And again, you can see that they are both italicized here; these are both foreign keys,0909

because they refer to primary keys in another table.0914

This row right here, again, is going to uniquely link one row in our Departments table to one row in our Items table, and so forth.0917

As we know, with our tables, we want to have a primary key for our table.0933

And what we are going to explain now is that, in that depts_items table, our linking table, it is going to have a primary key that is composed of two columns.0938

We know that a primary key is used to uniquely identify a row in a table.0947

Well, if we know that, let's say, we have the rule, which would make sense, that each store item can only be in a department once.0951

Then, what that is going to say is that, in our depts_items table, each deptID can only be matched up with an itemID one time.0960

Each of those pairs is unique.0971

Now, because they are unique, and they are the row of the table, we can use those to uniquely identify a row in the table.0973

We can say, "I want the row from the table that has deptID number 1, and then also has itemID 1001 with it."0979

That way, we can uniquely locate that row in the table, so we have a primary key0987

where the key of the table involves two different columns.0992

So, for our linking table, both of those columns (deptID and itemID) are going to comprise the primary key for that table.0997

Now, the way you do that--the syntax for that--is: in our CREATE TABLE SQL statement,1006

we define our deptID and itemID columns as we normally would any other column.1013

Notice that we have the type INT UNSIGNED, and here, because this is a foreign key that matches a key in another table,1020

we want it to have the same data type as the other table.1027

Well, in our Departments table, you will see in a minute: we are going to create deptID as an unsigned integer.1029

And we know, from our itemID, that it is an unsigned integer, so we give both of these data types of unsigned integer.1035

And the way that we signify a multiple-column primary key is by adding, after all of our column definitions, this phrase 'primary key.'1040

And then, we include in parentheses a comma-delimited list of the different columns that we want to use1049

to represent the primary key for that table.1054

What this is going to do is: if, let's say, we have some data from our depts_items linking table;1056

when we define this as the primary key, this is going to let MySQL know that each of those pairs1072

of deptID and itemID has to be unique, and it will help us to enforce the fact that an item is in only one department.1078

So, if we try to, for example, run an INSERT statement (and I'm just going to abbreviate it),1083

with the values 1 and 1001, MySQL is going to complain and say it can't be; a row with that primary key already exists in the table.1094

And so, that is going to allow us to be able to enforce that.1105

Let's go ahead and run these CREATE TABLE scripts that are going to set up the ability to add departments to our MySQL database.1108

It is going to create the Departments table and this depts_items linking table.1119

And that is what you are going to be doing for your homework: mimicking what we are going to be doing here,1124

so that your MySQL database at home will work with our web application.1127

First of all, I have logged into the MySQL Monitor.1135

The database I am currently using is the advanced_php database, which is our database.1140

And if I show the tables that we have, we can see that there is one table currently, Items.1145

Well, let's go ahead and create our Departments table, which as we just saw, is just going to contain1149

a primary key column, which is deptID, and then a column for the name of the department.1154

We can write a CREATE TABLE statement and call our table Departments.1161

We are going to have the first column be deptID, which is going to be an unsigned integer.1170

It is going to be auto-incremented, just as our Items table has its itemID auto-incremented.1178

We are going to denote that that is the primary key of this table, so each deptID is going to uniquely identify a department.1184

And then, we are simply going to have a name column that is going to be a variable-length string.1190

In this case, I am going to set it to 25; so our department name can be up to 25 characters long.1195

And then, we close the definition and add the closing parentheses and the semicolon, and we run it.1202

It is going to let us know that we created the table; if we run SHOW TABLES again, we can see we have two tables in our database.1208

to verify the structure of this Departments table we just created, we can use the DESCRIBE command.1216

And we can see that our Departments table has two columns, deptID and name.1223

And we can see all of the information that we just input.1229

So now, let's create the table for our linking table.1232

And because it is linking Departments with Items, again, we are going to be calling it,1238

as is common with linking tables, the names of the two tables combined, so we are going to call it depts_items.1242

The first column we are going to have is going to be deptID, which is going to be a foreign key.1253

It is going to represent a primary key in the Departments table.1257

We are going to have it match the data type of the primary key column in Departments that it refers to.1260

We are going to create the itemID column in the same way, which is going to refer to a primary key in the Items table, which is an unsigned integer.1267

And then, we are going to add that new clause that we learned about to signify a multiple-column primary key.1277

And we are going to say that we want the primary key to be comprised of the columns deptID and itemID.1287

Close the table definition and run it; it is going to let us know that it was OK.1295

If we describe this new table, we can see that there is a column here that I haven't talked too much about,1298

when you run DESCRIBE, that shows which keys are the primary key.1307

Up here, we can see, in our Departments table, that the deptID column is denoted as the primary key.1311

Here, we can see that primary key is denoted as both of these columns.1319

In this particular table, both of the columns end up representing the primary key of the table.1325

That is how we have gone ahead and created our Departments table and depts_items table1329

that is going to allow us to add departments into our database and add items to those departments.1334

And we are going to be continuing in the next upcoming lessons, migrating our database from the departments.txt file into these tables.1341

For your homework, I just want you to go ahead and do what we just did.1355

Create the Departments table that is going to have a deptID and a name column.1358

The deptID is going to be the primary key; it should be an auto-incremented column, an unsigned integer.1364

The name should be a VARCHAR, a variable-length string column.1369

Create the linking table depts_items that has the columns deptID and itemID, which together make up the primary key of that table.1373

So, when you write that CREATE TABLE statement, it is going to give you some practice with signifying a primary key that contains multiple columns.1382

And then, go ahead and populate the tables, so that they mimic the...1391

which is something we didn't do here, but you know how to use INSERT statements now.1395

Go ahead and populate the tables, so that they mimic the three default departments setup that we have in our web store.1398

And that is that our Apparel department in our default setup contains item 1001 and 1004.1404

Our Electronics department has 1002 and 1005; our Sporting Goods department has 1003 and 1006.1411

In order to do that populating, you are going to have to run some INSERT statements1419

that are going to insert information into the Departments table to add these names.1422

And then, you are going to have to run a couple of insert statements that are going to use the deptID's1427

that were generated when you added these departments to the Departments table,1433

to add rows to our depts_items linking table.1438

And you are going to be linking department ID's: for example, deptID 1, you are going to be linking with item 1001 and with item 1004.1441

And then, just make sure that the foreign keys that you defined in depts_items as we went over have the same column types1452

as the primary keys columns that they refer back to, which, for the deptID and the itemID, is an unsigned integer.1459

That ends today's lesson; thank you for watching Educator.com, and I look forward to seeing you next time.1468