For more information, please see full course syllabus of Advanced PHP
For more information, please see full course syllabus of Advanced PHP
Discussion
Study Guides
Download Lecture Slides
Table of Contents
Transcription
Related Services
Linking Tables
- Database normalization is a process that involves structuring a relational database to help reduce common problems involving:
- Difficulties in searching data
- Having large numbers of empty fields
- Needing to re-structure tables frequently
- Redundant storage of the same data
- A foreign key is a column in one table whose value represents a primary key of another table.
- A linking table contains two or more foreign key values in each row that serve to link one row of one table with one row of another table.
- Tables can use more than one column to comprise its primary key. Such a table is defined using the following syntax:
CREATE TABLE depts_items (
deptID INT UNSIGNED,);
itemID INT UNSIGNED,
PRIMARY KEY (deptID, itemID)
Linking Tables
Lecture Slides are screen-captured images of important points in the lecture. Students can download and print out these lecture slide images to do practice problems as well as take notes while watching the lecture.
- Intro 0:00
- Lesson Overview 0:16
- Lesson Overview
- A Departments Table 1:08
- Departments Table
- How Could We Model This in a Table: Option 1
- How Could We Model This in a Table: Option 2
- Database Normalization 4:37
- Database Normalization
- Foreign Keys 8:47
- Foreign Keys
- Linking Table 11:26
- Linking Table
- Multiple Column Primary Keys 15:30
- Multiple Column Primary Keys
- Example
- Required Homework 22:34
- Required Homework: 1 - 3
- Required Homework (cont.) 24:10
- Required Homework: 4
Advanced PHP Training with MySQL
Transcription: 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
Start Learning Now
Our free lessons will get you started (Adobe Flash® required).
Sign up for Educator.comGet immediate access to our entire library.
Membership Overview