2. PHPRunner General questions
  3. implementation of many to many relationships
This topic is locked

implementation of many to many relationships

 		1/26/2011 9:23:37 AM
PHPRunner General questions
P
 pvdemael author

I have a question on many to many relationships and how to implement it in PHPRunner.

After a lot of thinking, swearing and searching the forums I did not end up with a satisfactory answer, that's why I am posting this question again in the hope to get an answer.
This is a very basic question and essentially has nothing to do with PHPRunner because it is a general database design issue.

But we happen to be (novel/experienced) PHPRunner web application designers who want to implement this with our favorite software package.

A good answer is very valuable because this kind of relationships is essential in applications that make extensive use of databases.

And if the answer is valuable, it should be included in the tutorial and tips and tricks, and the poster should get an award because he will save many hours of suffering.
Here is a small list of conditions the solution should comply with:

  • Logical

    The database design, form design, views, lists, etc should be designed logically to reflect the relationship.

    General tips on how to design stuff are also very welcome.
  • User friendly

    All data is visible to the user in a such a way that the relationship is clear and the data is presented in a human readable fashion.

    Low level stuff should be hidden to the user, data input and exploration should be intuitive without knowing a lot about the hidden stuff.
  • Implementable in PHPRunner

    I would like to use as many PHPRunner constructs as possible: scripts, events, design tools,... to implement the relationship.

    Programming is not that much an issue, some PHP and/or javascript is not a problem.
  • General

    The solution should be general.


I would like to challenge you all to propose a solution to this question.

I am sure that a lot of the PHPRunner users will be very happy and grateful, at least I will be.

Other comments, additions, remarks, etc. are also welcome
To keep it fun, let's have the example of Belgian beers. We have several brands which are sold by several pubs.

All pubs are also selling several brands, a classic many to many relationship.
To follow good database design practise, we need a third table linking the beers and pubs tables.
Table Beer

beer_id beer_name

001 Gentse Tripel

002 Leffe

003 Augustijn

004 Westmalle

005 Westvleteren

006 Hoegaarden

007 Stella Artois
Table Pub

pub_id pub_name

A01 The Corner

A02 The Red Horse

A03 The Cyclist

A04 At the Abbey
Table beer_pub

beer_id pub_ID

001 A01

001 A03

002 A01

002 A02

002 A03

002 A04

003 A02

003 A03

003 A04

004 A03

004 A04

005 A03

006 A01

006 A02

006 A04

007 A02

007 A03
offtopic: Westvleteren is the best beer in the world (http://www.ratebeer.com/RateBeerBest/bestbeers_012010.asp)
Sergey Kornilov admin 1/26/2011

We are working on a full featured video tutorial that explains how you can setup many-to-many relationship in PHPRunner. Meanwhile here is a quick example of how you can setup this kind of relationship.
There are multiple offices and multiple employees. Employee may work for more than one office. We going to use the following tables: Office, Employee, Office_Employee.
Office

ID - int, primary key

Address - varchar
Employee

ID - int, primary key

FullName - varchar

Phone - varchar
Office_Employee

ID - int, primary key

EmployeeID - int

OfficeID - int
Here is a live demo:

http://demo.asprunner.net/volinrok_yahoo_com/ManyToMany/Office_list.php
You can browse offices and associated employees. You can add (assign) new employee to any office or delete one (doesn't delete the employee just removes the reference).
Step-by-step setup
1. Add all three tables to the project. Link Office and Office_Employee as Master-Details using ID and OfficeID as link fields. In fact at this point you already have some functionality like Browse and Add though it's not that usable.


All other customizations apply to Office_Employee table.
2. Modify Office_Employee SQL query to pull and display Phone number from Employee table.

SELECT Office_Employee.ID,

EmployeeID,

OfficeID,

FullName,

Phone

FROM Office_Employee

inner join Employee on Employee.ID = Office_Employee.EmployeeID
3. 'Choose pages' screen of Office_Employee

Select EmployeeID and OfficeID as key columns. Enable Add, Inline Add and Delete pages.
4. 'Choose fields' screen

Select EmployeeID and Phone fields to appear on List and InlineAdd pages. Removed other fields from those pages.
5. Visual Editor

Setup EmployeeID as a Lookup wizard, select FullName field from Employee table to be a 'Display' field.

Enabled 'Autofill' feature choosing to fill Phone field.
6. Since we cannot actually save Phone field in Office_Employee table we need to proceed to Events screen, Office_Employee table, Add page, BeforeRecordAdded event and put the following code there:
unset($values["Phone"]);
Download project and database
You will need to modify database location and output directory.
P
 		pvdemael author 1/27/2011

Sergey, thank you very much for your explanation, you earned the award.
I will study this example carefully and I am looking forward to the video tutorial (any idea on when the tutorial could be online?)

I suppose it is possible to do the reverse too, like assigning offices to employees by defining a similar master-detail relationship?
Sergey Kornilov admin 1/27/2011

We expect video tutorial available in couple of weeks.
You can make it work in either direction, it's fully symmetrical.