many-to-many relationship insert / select
Posted: Fri Mar 28, 2003 12:35 pm
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:
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!
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 |
+--------------+-------------------+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!