2. PHPRunner General questions
  3. Many-to-many relationships
This topic is locked

Many-to-many relationships

 		11/12/2010 09:23:52
PHPRunner General questions
G
 gcaplan author

Hi
Surprised this isn't more of an FAQ. Can't find anything in the docs. I did find a very old post but it's hard to follow and may be out of date...
I make extensive use of many-to-many relationships of the classic form:
PARTY

party_id
ADDRESS

address_id
PARTY_ADDRESS

party_id

address_id
I'm excited by the potential of PHPRunner but need to find a workable way to manage this scenario if I'm going to purchase.
Sergey Kornilov admin 11/12/2010

I recommend to start with article:

http://www.databasedev.co.uk/many_to_many_example.html
The most important part is as follows:

A many-to-many relationship is really two one-to-many relationships with a junction/link table.
The rest is easy. Simply create to master-details relationships: PARTY->PARTY_ADDRESS and ADDRESS->PARTY_ADDRESS. This is it.
G
 		gcaplan author 11/12/2010

Hi
Thanks for responding, but I'm not sure you understood my issue.
I'm aware of what a many-to-many relationship is - it's the most efficient way to create a workable interface in Runner that I'm trying to clarify.
> A many-to-many relationship is really two one-to-many relationships with a junction/link table.

> The rest is easy. Simply create to master-details relationships: PARTY->PARTY_ADDRESS and ADDRESS->PARTY_ADDRESS. This is it.
Perhaps I'm being dim, but it's not looking so easy from where I sit.
The problem is, rather obviously, that the link table contains an unreadable list of keys that has to be hidden from the user.
So first I have to display a grandchild browse list for each parent record without displaying the child link table. How would this be this done?
And second, I have to maintain the relationships in the link table while creating/ deleting records in the parent and grandchild tables, Ideally without having to write a load of error-prone code. I have cascade rules in the database, but they only handle a subset of use cases.
This may be obvious to experienced users, but as I'm still at the evaluation stage there a limit to how much time I can spend trying to figure this out. Plus, it's such a fundamental requirement that I'd expect it do be documented in an app in this price range. So I would appreciate a more complete reply, please, and I'm sure that other users would find it useful.
I'm fluent in PHP and SQL, by the way, so a code based solution is fine so long as it's not time-consuming.
P
 		pvdemael 12/7/2010

This is also my problem. I want to keep my database as clean as possible, so the junction table is the best solution.

The problem is like gcaplan suggests how to implement this in a user (and programmer <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=54968&image=1&table=forumreplies' class='bbc_emoticon' alt=';)' />) friendly way.