In the last lesson you learnt how to Create, Read, Update and Delete records in a database table, otherwise known as CRUD. You were also given an assignment to create three new records in your leaningglue database table sections called Courses, Services and About, in that positional order (position column) and each record visible (the visible column). How did you get on?

Here’s the code that would insert those records:

mysql> INSERT INTO sections (menu_title, position, visible) VALUES (‘Courses’, 1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO sections (menu_title, position, visible) VALUES (‘Services’, 1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO sections (menu_title, position, visible) VALUES (‘About’, 1, 1);
Query OK, 1 row affected (0.00 sec)

Note the the string values are in single quotes and integers are not. Hopefully your MySQL returned Query OK for each of these queries.

Moving on, let’s talk about Relational Tables.

MySQL Relational Tables are the cornerstone of efficient database development. Up to now we have learnt how to create a single table within a database and we saw how that table could hold as many records using as many columns as we needed. This is very powerful, and we can imagine that we can create more tables for different types of data and store it in the same way.

That all sounds great, but what if you wanted a table that stored courses that students might take, another that stored students enrolled on courses and another that stored a schedule of classes. Can you see that there might be a lot of duplicated content across those tables?

Think of your website navigation. If you had a single table that handled all navigation data (sections and pages), can you see that there will be a lot of content duplication when we start thinking about specifying which section a group of pages belong to? Also, what if you want to change a section name later, you will have to change all of the subject names in every related page record.

So that sounds like a lot of unnecessary work. Let’s consider we have two tables that contain sections data and pages data. We know that sections are going to be areas that pages will be associated with, and that for each single section there will be many pages – this is called a one to many relationship.

This relationship is important. Each table will contain information that is relevant to the other  – records in the pages table need to know which section they belong to and in what order the should appear. Records in the sections table need to know what and how many pages are associated with it and what order it should appear, and so on. This information will enable us to write code that will create a functioning and efficient navigation for our web site.

So how do we create this relationship? How do we connected these two tables? Remember that Foreign Key that we discussed a few lessons ago? Well that is exactly what you will use, it will enable you to reference a record in another (or foreign) table.

Consider the two tables. Sections already exists and contains id, menu_title, position and visible fields. The pages tables, yet to be created, contains the same type of information with an additional content field for the page content. Here is a schematic of these tables:

Right now, these two tables have no relationship with each other, except of course that they are in the same database. The sections table is on the one side and the pages is on the many side, or there will be one section with many pages. This means that the pages table will need an additional field that will reference records in the sections table, and this additional field is a foreign key. Let’s call this foreign key section_id, so the table structure and relationships will be:

The foreign key in the pages tables has a value of 1,  which relates with the value of 1 in the sections tables within the id column. Now the page records will know which section they belong to, and each section record will know how may pages they have. What we have now are relational database tables.

Now that we have the foreign key worked out let’s create the table using the CREATE TABLE query:

mysql>CREATE TABLE pages (
-> id INT(11) NOT NULL AUTO_INCREMENT,
-> section_id INT(11) NOT NULL,
-> menu_name VARCHAR(30) NOT NULL,
-> position INT(3) NOT NULL,
-> visible TINYINT(1) NOT NULL,
-> content TEXT,
-> PRIMARY KEY (id),
-> INDEX (section_id)
-> );
Query OK, 0 rows affected (0.07 sec)

There is nothing new here apart from the INDEX, this is going to instruct MySQL to use the subject_id (which is already a foreign key) as an index so that it can perform fast look-ups when performing queries. MySQL will automatically create an index for the primary key, in this case the id field. We are asking MySQL to create an additional index for the foreign key because this key will be use a lot when we are performing queries related to the pages table.

If you also see Query OK then we can be sure that the table was created. But to be sire let’s use the SHOW TABLES query to check:

mysql>SHOW TABLES;
+————————+
| Tables_in_learningglue |
+————————+
| pages |
| sections |
+————————+
2 rows in set (0.01 sec)

And there you have it, two tables are present in your database. To check the columns you could use the DESCRIBE query:

mysql>DESCRIBE pages;
+————+————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+————+————-+——+—–+———+—————-+
| id | int(11) | NO | PRI | NULL | auto_increment |
| section_id | int(11) | NO | MUL | NULL | |
| menu_name | varchar(30) | NO | | NULL | |
| position | int(3) | NO | | NULL | |
| visible | tinyint(1) | NO | | NULL | |
| content | text | YES | | NULL | |
+————+————-+——+—–+———+—————-+
6 rows in set (0.01 sec)

Note the MUL value in the Key property for the section_id column, this indicate that the section_id (your foreign key) has an index on it.

Now that you have the pages table created you can start inserting data into it. Do this using the INSERT query. One thing to watch out for here is the foreign key, this is the field that will connect your page records to individual subject records. So as you create page records you will need to know which subject each one belongs to. Let’s start inserting some page records:

mysql>INSERT INTO pages (section_id, menu_name, position, visible, content) VALUES (1, ‘MySQL’, 1, 1, ‘Welcome to the MySQL course.’);
Query OK, 1 row affected (0.00 sec)

Note that we did not insert anything in the id column, this is an AUTO_INCREMENT field and will be assigned a unique value by MySQL. Also note the section_id, this value is related to the id field in the sections table – this will make this page a child of the section with an id of 1, the Courses section.

The Query OK message indicates that the query was successful and that the record was written to the table but it is good practice to double check just to be sure, and you do this using the SELECT * FROM query. Try that now:

mysql>SELECT * FROM pages;
+—-+————+———–+———-+———+——————————+
| id | section_id | menu_name | position | visible | content |
+—-+————+———–+———-+———+——————————+
| 1 | 1 | MySQL | 1 | 1 | Welcome to the MySQL course. |
+—-+————+———–+———-+———+——————————+
1 row in set (0.00 sec)

Hopefully you too see the record you just inserted. If not, check that you are selecting from the right table, check your MySQL syntax. If everything checks out, rewrite the query and try again.

Now that you have inserted one page record try creating a few more, here are the details:

• a page called PHP under courses, position 2 and visible
• a page called HTML under course, position 3 and visible
• a page called CSS under courses, position 4 and visible
• a page called Training under Services, position 1 and visible
• a page called SEO Consulting under Services , position 2 and visible
• a page called Web Development under Service, position 3 and visible
• a page called Our Team under About, position 1 and visible
• a page called Contact under About, position 2 and visible

Once you have successfully inserted all of these new page records, move onto the next lesson where we will look at connecting your database to PHP and start building your database driven website.