PHP GUI for handling many-to-many MySQL

Anon

Hi everyone, Who can help me with this one?

I am creating a web-based database using PHP and MySQL. I have several one-to-many and many-to-many relationships, such as:

tblPerson -1---< tblEmployee >---1- tblOrganisation

Using MySQL is not the problem.

I am looking for a nice user-friendly GUI (front-end) for handling these many-to-many relationships. For instance, I would like to have a screen in wich I can Add/Edit/Delete a Person and also (here it gets difficult for me!) assign the Person to one or more Organisation(s).

Please help. Thanks a lot!

Rubends
http://www.rubends.com/rpb/

Anon

Check out iDriver:
http://aiken.clan11.com/idriver/

The Inventory demo has an interface to a many-to-many relationship (Workstation-Software).

Ryan

[deleted]

Well how are you going to use a gui if you don't seem to understand your own database model yet? :-)

In the example you posted, there is no many-to-many relationship, only a normalized details table which has two one-to-many relationships.

What is difficult about linking persons to organisations?

Anon

You don't understand, this is a many-to-many relationship.

One person (p_id) could work for one or more organisations (o_id). In a organisation (o_id) could work one or more persons (p_id). Therefor a new table is necessary, I've named it employee (p_id,o_id).

The question is how to create a GUI for adding/editing/deleting person AND ALSO be able to assign the person (in the same screen) to one ore more organisations?

Cheerz. Rubends
http://www.rubends.com/rpb/

kparker

If you don't need to add new organizations at the same time, but can simply restrict the user to selecting from a preexisting list of organizations, AND the list of organization is small enough that sending the complete list is not prohibitive, then you can just populate a multi-select box with the org list, with the ones the user currently belongs to preselected. You'll probably also want to send the current org membership as hidden variables. That way, when the form is posted, you can do a simple add-delete pass on the organizations. (If it's on the current list but not the new list, DELETE it; if it's on the new list but not the current list, INSERT it; if it's on both, do nothing.)

[deleted]

The end result may be a 'many-to-many' relationship, but
in your database you don't have any many-to-many relationships.

What you have are two one-to-many relationships, and your GUI should work with both of the one-to-many relationships seperately.

Anon

I guess you never heard of database normalisation before?

IMHO this is the only proper way of dealing with a many-to-many relationship.

But I could be wrong.

Rubends
http://www.rubends.com/rpb/

Anon

Thanks for your reply Kirk! I am looking for a working example, so I could learn from the code.

kparker

I think what he means is that, from the user standpoint, you aren't trying to create a screen with all the people on one side, and all the organizations on the other, and somehow cross-connect them all on a single screen. Instead, the user will either have a single person's record as the point of focus, and want to set up all the orgs that person works for, or else have the org's master record up, and want to work with all the people who work for that one org.

Does this clarify things?

[deleted]

I am fully versed in database norlamization, and your method is quite correct,
but your approach is a bit off.

Kirk explained by view allmost perfectly.

To remove the many-to-many relationship, normalization gives you the third table.
From that moment on, your databasemodel nolonger has a many-to-many relationship, it has a one-to-many from the first table to the new table, and a one-to-many from the second table to the new table.
Your administration tool should work exactly the same way.
You either link a person to an org, or an org to a person.
Never both at the same time.