many-to-many relationship insert / select

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Miles
Forum Newbie
Posts: 3
Joined: Fri Mar 28, 2003 12:35 pm
Location: Saskatoon, Canada

many-to-many relationship insert / select

Post by Miles »

I had a really nice simple database that would keep track of point-to-point video calls. It worked so well, that it was decided that we should extend it to be able to track multi-point videoconferences. Here's where my troubles start...

MySQL - here are the tables:

Code: Select all

conference
+---------------------+------------------+
| Field               | Type             |
+---------------------+------------------+
| conf_ID             | int(10) unsigned |PRI
| conf_event          | text             |
| conf_begin          | datetime         |
| conf_end            | datetime         |
| org_ID              | int(10) unsigned |
| site_origin_ID      | int(10) unsigned |
+---------------------+------------------+

sites
+-------------------+------------------+
| Field             | Type             |
+-------------------+------------------+
| site_id           | int(10) unsigned |PRI
| site_name         | varchar(40)      |
| site_isdn_numbers | text             |
+-------------------+------------------+

org
+-------------+------------------+
| Field       | Type             |
+-------------+------------------+
| org_ID      | int(10) unsigned | PRI
| org_name    | varchar(40)      |
| org_contact | varchar(40)      |
+-------------+------------------+

call_destination
+--------------+-------------------+
| Field        | Type              |
+--------------+-------------------+
| dest_id      | int(10) unsigned  | PRI
| conf_ID      | int(10) unsigned  |
| site_dest_ID | int(10) unsigned  |
+--------------+-------------------+
The output of a select would look something like this:

conf_event conf_begin conf_end site_name(origin) site_isdn_number(origin) site_name(list of call destinations) site_isdn_number(list of call destination numbers)

Here are the problems:
1) Weird self-join thingy where the call origin sites are pulled from the same table as the call destination sites. I think I have to use two aliases of the sites table but beyond that...

2) Many-to-many realtionship between conferences and call destination sites.

On the input side of things, how do you create a form to handle the multiple destinations? Can PHP do it or do I have to use javascript? I envisioned somthing like a dropdown menu (populated by the sites table) to select the destination and a + button after it that would create another dropdown menu to select the second call destination and so on.

Are my tables OK (normalized)? Are there things I should change in the structure?

Any and all help is appreciated!
Rob the R
Forum Contributor
Posts: 128
Joined: Wed Nov 06, 2002 2:25 pm
Location: Houston

Post by Rob the R »

Your table design looks sound to me. The mutliple join of the SITES table may look strange, but it's the right thing to do since you have sites referred to in two tables (CONFERENCE and CALL_DESTINATION).

I'm not sure I understand the many-to-many relationship between the CONFERENCE and CALL_DESTINATION tables. There could many records in CALL_DESTINATION for any given conference, but this is reflected by only one record in the CONFERENCE table. As long as you always join the tables by CONF_ID, you should be fine.

As far as PHP vs. Javascript, I think either tool could do the job. Javascript would allow you to create the additional destinations without having to reload the page, but this is more a question of user interface than capabilities. The Javascript might get a bit hairy (cross-browser compatibilty and all), so PHP might be a simpler way to do it - if the users can stand to reload the page for each new row (or simply always provide a multitude of destinations in the form, and the users could use as many or as few as they want).
Miles
Forum Newbie
Posts: 3
Joined: Fri Mar 28, 2003 12:35 pm
Location: Saskatoon, Canada

Post by Miles »

Here's what I've got for a SELECT:

Code: Select all

SELECT c.conf_event, c.conf_begin, c.conf_end, o.org_name, s1.site_name  AS Origin, s1.site_isdn_numbers AS OriginNumbers, s2.site_name AS Dest, s2.site_isdn_numbers AS DestNumbers
FROM conference c
LEFT JOIN org o ON c.org_ID = o.org_ID
INNER JOIN sites s1 ON s1.site_id = c.site_origin_ID
INNER JOIN call_destination d ON d.conf_id = c.conf_id
INNER JOIN sites s2 ON s2.site_id = d.site_dest_id
ORDER BY c.conf_begin
I'm not sure if I should be using LEFT JOIN or INNER JOIN but both give the same results in this case so I guess the choice is academic. This SELECT gives me three rows if there are three call destinations - I guess I'll have to parse the results with PHP for cleaner display.

For the insert though, how do you put the conf_ID into the call_destination when you haven't inserted the conference yet and it therefore it has no conf_ID?

I'm rethinking the interface, but I'm still stumbling on how to implement the call destination things. Maybe a list of sites with checkboxes, then there would be no need to reload the page to add a site.

Thanks for the help,
Miles
Miles
Forum Newbie
Posts: 3
Joined: Fri Mar 28, 2003 12:35 pm
Location: Saskatoon, Canada

Post by Miles »

The many-to-many relationship is between the conference table and the sites table; one conference can have many destination sites, and any site can be in many conferences. I use the call_destination table as an intermediate table between them to create 1-to-many relationships from call_destintaiton to conference and call_destination to sites.

The place where this gets weird is when I have to insert conf_ID into the call_destination table. In practice, this entry should happen at the same time as the conference is inserted and therefore conf_ID does not yet exist. The only way I can figure is if I insert the data into the conference table to create the new record there, then do a query to get the conf_ID, then insert that value into call_destination. This seems like a pretty big kludge to me.

Is there some more elegant way (the right way, dammit!) to do this?

Thanks,
Miles
Rob the R
Forum Contributor
Posts: 128
Joined: Wed Nov 06, 2002 2:25 pm
Location: Houston

Post by Rob the R »

Miles,

It may sound kludgy, but I think that's the best way to go about it. The IU could support this by having a "conference info" form before asking for the sites that will be participating. Then you can save the conference data into the CONFERENCE table, and thereby have a CONF_ID value to work with.

The only other options I see is to create a temporary dummy CONF_ID value (like -1) just as a placeholder, but then you'd have to go back and update it once you know the actual CONF_ID (and this would not handle multiple users very well). You could also control this my managing your own CONF_ID values and not use the AUTO_INCREMENT feature. This might also have problems with multiple, simultaneous users, though, depending on how you worked it.

All in all, I think the way you described is the least kludgy option you have.

- Rob
Post Reply