A Case Study: Implementing Many-To-Many Relationship in Web Applications that Generated by PHPMaker 10

As we have already known, when designing database relationships, Many-To-Many relationships are not a good idea, because they are not allowed. We must find a way of making them Many-To-One or One-To-Many. In databases, a join table is generally used to create the relationships.

A typical example is the orders and products tables in the PHPMaker demo project sample database. An order can include many products and a product can be included in many orders. Unfortunately, we cannot create Many-To-Many relationship directly between those two tables. We have to use the intermediate table between those two tables in order to create a Many-To-Many relationship. Let's say the intermediate table is orderdetails.

So, the relationship between orders to orderdetails, and products to orderdetails tables are respectively One-To-Many.

orders <--- One-To-Many ---> orderdetails <--- Many-To-One ---> products

As we can see, the orderdetails table is on the many side of two One-To-Many relationships — the first with the orders table and the second with the products table. By bridging the two tables, it lets you manage the Many-To-Many relationship between the orders and products tables.

This tutorial will show you step by step how to implement a Many-To-Many relationship in the web applications that generated by PHPMaker 10. Let's use another example for this relationship. We will use students and subjects tables. A student can take many subjects, and a subject can be taken by many students. So, we will create an intermediate table as a bridge between those two tables, let's called it with students_and_subjects table. The next step, we will setup the Master/Detail and some of the related settings among those three tables.

There were some enhancements and improvements for Master/Detail feature since PHPMaker 10. One of them is now we can setup the Referential Integrity which will enforce foreign key points to a valid record in the master table. The others that related to this feature are: Cascade Delete (specifies that if a record in the master table is deleted, all corresponding records in the detail table will be deleted, too) and Cascade Update (specifies that if the primary key for a record in the master table changes, all corresponding records in the detail table will be updated, too). We will implement all those abilities, too.

[hidepost]

1. First of all, let's create a new database named manytomany for this demonstration. Inside this database, let's create three new tables by using the following SQL script:

   SET FOREIGN_KEY_CHECKS=0;
   
   -- ----------------------------
   -- Table structure for students
   -- ----------------------------
   DROP TABLE IF EXISTS `students`;
   CREATE TABLE `students` (
     `Student_ID` varchar(5) NOT NULL,
     `Student_Name` varchar(50) DEFAULT NULL,
     PRIMARY KEY (`Student_ID`)
   ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
   
   -- ----------------------------
   -- Table structure for students_and_subjects
   -- ----------------------------
   DROP TABLE IF EXISTS `students_and_subjects`;
   CREATE TABLE `students_and_subjects` (
     `Student_ID` varchar(5) NOT NULL,
     `Subject_ID` varchar(3) NOT NULL,
     `Semester` int(11) NOT NULL,
     `Year` int(11) NOT NULL,
     PRIMARY KEY (`Student_ID`,`Subject_ID`,`Semester`,`Year`)
   ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
   
   -- ----------------------------
   -- Table structure for subjects
   -- ----------------------------
   DROP TABLE IF EXISTS `subjects`;
   CREATE TABLE `subjects` (
     `Subject_ID` varchar(3) NOT NULL,
     `Subject_Description` varchar(50) DEFAULT NULL,
     PRIMARY KEY (`Subject_ID`)
   ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
   

2. The next step, create a new PHPMaker project using PHPMaker 10 application. Define your MySQL connection to that database we have just created on the first step above, from the Database tab in PHPMaker, and then click on the Connect button.
3. Now you should see three tables: students, students_and_subjects, and subjects selected in the Database pane at the left side of PHPMaker 10 application.
4. Click on students_and_subjects table on the Database pane, and now you should see a Fields tab is opened and selected at the right side of PHPMaker 10 application. A Student_ID field is now being selected.
5. Now we will define the Lookup Field for the Student_ID field. From the Edit Tag pane, click on Select, and then select or click on Use lookup table. From the Lookup table pane, click on Table name and then choose: students, from Link field, choose: Student_ID, and then from Display field #1, choose: Student_Name.
6. The next step, we will define also the Lookup Field for the Subject_ID field. From the Edit Tag pane, click on Select, and then select or click on Use lookup table. From the Lookup table pane, click on Table name and then choose: subjects, from Link field, choose: Subject_ID, and then from Display field #1, choose: Subject_Name.
7. Now we will define the Master/Detail relationship among those three tables above. Make sure the students_and_subjects table is being still selected from the Database pane on the left side of PHPMaker 10 application, afterwards, click on Table tab next to the Fields tab. From the Master/Detail pane at the bottom right side, click on Modify button.
8. Now you should see a new window named Master/Detail - students_and_subejcts appears. Drag and drop those three tables from the left side to the right side. Make sure the position of those three tables are: students (left), students_and_subjects (middle), and subjects (right).
9. The next step, we will create the relationship among those three tables. Click on the Student_ID field in the students table, drag and drop it into Student_ID field in the students_and_subjects table. This will create a One-To-Many relationship between students and students_and_subjects tables. Make sure you have done this correctly, otherwise, the relationship will get wrong.
10. Click on the Subject_ID field in the subjects table, drag and drop it into Subject_ID field in the students_and_subjects table. This will create a One-To-Many relationship between subjects and students_and_subjects tables. Make sure you have done this correctly, otherwise the relationship will get wrong. Then, click on OK button to save the changes and close Master/Detail - students_and_subjects dialog window. Now you should see there are two One-To-Many relationships have been defined in the Master/Detail pane.
11. The next step, we will define the Referential Integrity, Cascade Delete, and Cascade Delete. Give a checked mark at Referential Integrity, Cascade Delete, and Cascade Update columns, for both the relationship that have been just defined in the previous step.
12. Now we will define another Master/Detail settings for the students_and_subjects table. Make sure that table is being selected from the Database pane, and then give the checked mark at the Master/Detail Add (as Detail), Master/Detail Edit (as Detail), and Master/Detail View (as Detail).
13. Click on PHP Settings icon on the toolbar, then click on Page Options (Global) sub-tab, and give a checked mark at Paging section in View page and Paging section in Edit page. This will generate the pagination both for View and Edit pages.
14. Click on Generate tab, adjust the Application root folder, and the other settings (if necessary), and finally, click on the Generate button, and then click on the Generate button on the Generate dialog window to generate the script files.
15. From the generated web application, please add some records for students and subjects tables first, as they are the Master tables. Afterwards, you may add some new records into students_and_subjects table.

[/hidepost]