trouble wrapping my head around using many-many tables

[SpectralDesign.Net]

Hello again, It's been so long since I've been on the PHPFreak boards I had to create a new login. Anyway, I'm trying to do my first DB with 3 many-many tables...

names: name_id, first, last, middle, title
addresses: address_id, street, suite, city, province, postcode, role
phones: phone_id, number, extension, description

to handle the many-many relationships I have 3 additional tables:
name_address: id, name_id, address_id
name_phone: id, name_id, phone_id
address_phone: id, address_id, phone_id

Any name could have one or more addresses, plus one or more phone numbers
Any address could have one or more names (residents), plus one or more phone numbers
Any phone could have one or more names (users), plus it would be linked to a specific address

Complicated enough yet?

Well, I'm admitedly an armchair coder, and I'm sure there's a more elegant way to get the DB (via PHP/HTML) to report: for any name (person) what addresses are in the DB, and for each of those addresses, what are the phone numbers in the database?

At the point I've reached I've got it recalling the correct address(es) for any given name (person), but for some reason I'm not getting it to report the phone numbers linked to that name at each address....
[b]
(non-working code removed)[/b]

[wickning1]

Wow. I don't have time to parse all that at the moment but I'll post later if someone doesn't beat me to it.

[SpectralDesign.Net]

Yeah, I know it's a bit much to grok...

To try to break-down the main points:

1. I'm pretty sure my DB schema is properly set-up for these many-many relations, but I'll attach it to the end of this post in-case it's helpful info.

2. My web-report isn't posting the phone numbers that have a dual-link to both the: name (client) and a given address. Perhaps this is a fault in my schema, but I think it's actually a fault in my PHP logic & SQL queries. For example, say Client A has a home address & work address, the report should show both addresses, and the home address should include the home phone number(s), while the work address should show the work phone number(s)... this does need to be in three many-many tables so that any number of addresses or phone numbers can be associated with a client, and there may be cases where many clients have the same address and/or phone numbers too. You can see my current demo at [a href=\"http://jzechner.spectraldesign.net/\" target=\"_blank\"]my DB demo[/a]

3. I suspect that I'm working too hard to get the desired result -- someone who's done a few many-many DBs is probably familiar with a more elegant way to set-up my queries for more efficiency in the reporting.

So, here's the schema, in brief:

[code]
-- Table structure for table `addresses`
DROP TABLE IF EXISTS `addresses`;
CREATE TABLE IF NOT EXISTS `addresses` (
  `address_id` smallint(6) unsigned NOT NULL auto_increment,
  `address_street` varchar(45) NOT NULL default '',
  `address_suite` varchar(15) NOT NULL default '',
  `address_city` varchar(35) NOT NULL default '',
  `address_province` varchar(45) NOT NULL default '',
  `address_postcode` varchar(15) NOT NULL default '',
  `address_role` varchar(25) NOT NULL default '',
  PRIMARY KEY  (`address_id`)
) TYPE=MyISAM;

-- Table structure for table `names`
DROP TABLE IF EXISTS `names`;
CREATE TABLE IF NOT EXISTS `names` (
  `name_id` smallint(6) unsigned NOT NULL auto_increment,
  `name_first` varchar(25) NOT NULL default '',
  `name_last` varchar(25) NOT NULL default '',
  `name_middle` varchar(25) NOT NULL default '',
  `name_title` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`name_id`)
) TYPE=MyISAM;

-- Table structure for table `phones`
DROP TABLE IF EXISTS `phones`;
CREATE TABLE IF NOT EXISTS `phones` (
  `phone_id` smallint(6) unsigned NOT NULL auto_increment,
  `phone_number` varchar(25) NOT NULL default '',
  `phone_extension` varchar(10) NOT NULL default '',
  `phone_description` varchar(25) NOT NULL default '',
  PRIMARY KEY  (`phone_id`)
) TYPE=MyISAM;

-- Table structure for table `name_address`
DROP TABLE IF EXISTS `name_address`;
CREATE TABLE IF NOT EXISTS `name_address` (
  `na_id` mediumint(9) NOT NULL auto_increment,
  `name_id` smallint(6) NOT NULL default '0',
  `address_id` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`na_id`,`name_id`,`address_id`)
) TYPE=MyISAM;

-- Table structure for table `name_phone`
DROP TABLE IF EXISTS `name_phone`;
CREATE TABLE IF NOT EXISTS `name_phone` (
  `np_id` mediumint(9) NOT NULL auto_increment,
  `name_id` smallint(6) NOT NULL default '0',
  `phone_id` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`np_id`,`name_id`,`phone_id`)
) TYPE=MyISAM;

-- Table structure for table `address_phone`
DROP TABLE IF EXISTS `address_phone`;
CREATE TABLE IF NOT EXISTS `address_phone` (
  `ap_id` mediumint(9) NOT NULL auto_increment,
  `address_id` smallint(6) NOT NULL default '0',
  `phone_id` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`ap_id`,`address_id`,`phone_id`)
) TYPE=MyISAM;
[/code]

and the fictitious data-set:
[code]
-- Dumping data for table `addresses`
INSERT INTO `addresses` VALUES (1, '123 New Street', '', 'London', 'Ontario', 'M1C 1T5', 'Home Owner');
INSERT INTO `addresses` VALUES (2, '423 Bright Avenue', 'Suite 4330', 'Toronto', 'Ontario', 'M4X 3F8', 'President - Ramco');
INSERT INTO `addresses` VALUES (3, '14 The Donway West', '', 'Don Mills', 'Ontario', 'M3B 1B6', 'Teacher - Don Mills Collegiate');

-- Dumping data for table `names`
INSERT INTO `names` VALUES (1, 'James', 'Sobel', 'W.', 'Mr.');
INSERT INTO `names` VALUES (2, 'Janice', 'Sobel', 'A.', 'Mrs.');

-- Dumping data for table `phones`
INSERT INTO `phones` VALUES (1, '647-555-1234', '', 'home');
INSERT INTO `phones` VALUES (2, '647-555-4321', '', 'office cell');
INSERT INTO `phones` VALUES (3, '647-555-9862', '', 'personal cell');
INSERT INTO `phones` VALUES (4, '416-555-6789', '', 'office');
INSERT INTO `phones` VALUES (5, '416-555-6780', '', 'office fax');
INSERT INTO `phones` VALUES (6, '416-555-7182', '', 'school');

-- Dumping data for table `name_address` id - name - address
INSERT INTO `name_address` VALUES (1, 1, 1);
INSERT INTO `name_address` VALUES (2, 1, 2);
INSERT INTO `name_address` VALUES (3, 2, 1);
INSERT INTO `name_address` VALUES (4, 2, 3);

-- Dumping data for table `address_phone` id - address - phone
INSERT INTO `address_phone` VALUES (1, 1, 1);
INSERT INTO `address_phone` VALUES (2, 1, 3);
INSERT INTO `address_phone` VALUES (3, 2, 2);
INSERT INTO `address_phone` VALUES (4, 2, 4);
INSERT INTO `address_phone` VALUES (5, 2, 5);
INSERT INTO `address_phone` VALUES (6, 3, 6);

-- Dumping data for table `name_phone` id - name - phone
INSERT INTO `name_phone` VALUES (1, 1, 1);
INSERT INTO `name_phone` VALUES (2, 1, 2);
INSERT INTO `name_phone` VALUES (3, 1, 4);
INSERT INTO `name_phone` VALUES (4, 1, 5);
INSERT INTO `name_phone` VALUES (5, 2, 1);
INSERT INTO `name_phone` VALUES (6, 2, 3);
INSERT INTO `name_phone` VALUES (7, 2, 6);
[/code]

[wickning1]

See how this works for you. I think the big thing you were forgetting was that you only want phone numbers for each name and address both. For instance, when you print out Joe and the address for Joe's apartment, you only want to print phone numbers for reaching Joe at his apartment. You'll print out his summer home and its phone numbers later.

A few joins helped clean up the code, too.

[code]<?php
// if a client has been selected and submitted, view the
//entire set of records related to that client...
if ( $_POST['submit'] == "view" ) {

$id_name = $_POST['client'];

// grab the client names and title
$name_populate = mysql_query("SELECT * FROM names WHERE name_id='$id_name'")
    or die('ERROR: '.mysql_error());
$row = mysql_fetch_assoc($name_populate);
$f_name = $row['name_first'];
$l_name = $row['name_last'];
$m_name = $row['name_middle'];
$title_name = $row['name_title'];

?>
<div id="main">
<div class="content">
<hr class="hide" />
<h2>Client Records</h2>
<table summary="client records" border="0" cellpadding="4">
<tr><td class="left"><h3>
<?php echo $title_name." ".$l_name.", ".$f_name." ".$m_name ?>
</h3></td></tr>
<?php
// grab all addresses linked to the selected client
$address_id_populate =
    mysql_query("SELECT a.*" .
                "FROM addresses a INNER JOIN name_address n USING (address_id)" .
                "WHERE n.name_id='$id_name'")
    or die('ERROR: '.mysql_error());
    
while ($row = mysql_fetch_assoc($address_populate)) {
    $street_address = $row['address_street'];
    $suite_address = $row['address_suite'];
    $city_address = $row['address_city'];
    $province_address = $row['address_province'];
    $postcode_address = $row['address_postcode'];
    $role_address = $row['address_role'];
    $id_address = $row['address_id'];
    // present the address to the client-application...
    echo '<tr><td class="left">' .
         $role_address . '<br />' .
         $street_address . ' ' . $suite_address . '<br />' .
         $city_address . ', ' . $province_address . '<br />' .
         $postcode_address . '<br />';
    
    // grab the phone records linked to the selected client at
    // the selected address
    $name_phone_id_populate =
        mysql_query("SELECT p.*" .
                    "FROM phones p INNER JOIN name_phone n USING (phone_id)" .
                    "INNER JOIN address_phone a USING (phone_id)" .
                    "WHERE n.name_id='$id_name' AND a.address_id='$id_address'")
        or die('ERROR: '.mysql_error());
        
    while ($row = mysql_fetch_assoc($name_phone_id_populate)) {
        $id_phone = $row['phone_id'];
        $number_phone = $row['phone_number'];
        $extension_phone = $row['phone_extension'];
        $desc_phone = $row['phone_description'];
        // present each phone record to the client-application
        echo "<br />" . $desc_phone . " - " . $number_phone .
             " extension: " . $extension_phone . "<br />";
    }
}
?>
<tr><td><br />
<form method="post" action="<?php echo $_SERVER['PHP_SELF']?>">
<input type="submit" name="submit" alt="new search" value="new search" />
</form></td></tr>
</table>
<?php
include_once $_SERVER['DOCUMENT_ROOT'].$basedir.'includes/foot.inc';
} // end of case where client selection has been submitted
?>[/code]

[SpectralDesign.Net]

wickning1,

Thank you! That's almost exactly what I need... now I can study it to figure out what the join-logic is that drives the proper functionality! I did have to make a couple minor tweaks, but I was able to use the code almost exactly as you corrected it. (for instance, I made a change so that the formatting would be clearer, and it would only print "extension: <blah>" if there was an extension in the row.)

[code]
        if (!$extension_phone == '') {$extend = " (extension: ".$extension_phone.")"; }
        echo $desc_phone." - ".$number_phone.$extend."<br />";
        unset($extend);
[/code]

How much do I owe you? :)

[SpectralDesign.Net]

Side-Note:

After doing some further testing, I notice that I've failed to account for one of the requirements, that being that two or more clients may share an address, but of course they may have seperate roles at said address. My thinking is that I need to remove the addresses.address_role field, and create a table called roles with the fields: role_id, role_name, address_id, name_id

So that it can have a one (addresses table) to many (roles table) relationship that properly links the roles to the correct names and addresses.... I'll have to ponder this more, but it's not a rush, as it's not a true concern in this first phase of the project. For now, I'm not seeing that it'd be important enough to go to extreme data-normalizing and creating roles as a many-many with an indexing table for each of names and addresses -- if there are a handful of records in roles that duplicate a role_name field I think the reduced complexity pays for the lack of normalizing.

(But what if a role changes from "janitor" to "maintenance engineer" -- everyone with that role should be updatable with a single change, so maybe many-many is the only real way to go.... Anyway, that's a battle for an other day!)

[wickning1]

Yeah, I would probably create a roles table and then store links to it in the name_address table. It looks like address_role should definitely come out of the address table; it just doesn't belong there.

[SpectralDesign.Net]

[!--quoteo(post=351736:date=Mar 4 2006, 08:27 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 4 2006, 08:27 PM) [snapback]351736[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Yeah, I would probably create a roles table and then store links to it in the name_address table. It looks like address_role should definitely come out of the address table; it just doesn't belong there.
[/quote]


When I first read that, I thought it would indeed be better than a role_address table with only role_id and address_id, but then after thinking more about it I though about how that would work with the requirement that two or more people might share an address but have a different role....

Your suggestion would provide more normalization, but (unless I'm missing something) still locks a single role to a single address.

I think what I need to get working is a pair of new tables.... 'roles' with role_id and role_title and then a table 'address_role' that has not only address_id and role_id, but also name_id so that it properly links a specific name that's linked to a specific address with a specific role...

When/if I get it working I'll post back with my results!

[SpectralDesign.Net]

I am sooooo close to getting this correct.....

Right now my report is almost perfect:

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Mrs. Sobel, Janice A.

home owner
123 New Street
London, Ontario
M1C 1T5

home - 647-555-1234
personal cell - 647-555-9862


Technician
14 The Donway West
Don Mills, Ontario
M3B 1B6

school - 416-555-7182[/quote]

The only problem is -- in my address_role table, Mrs. Sobel (name_id=2) is linked to the second address shown (address_id=3) with the role_title of Teacher (role_id=3) and yet the result I'm getting has assigned her to the content of the roles table as if her linked role_id were 5.... I'm not yet sure why this is happening....

Here's the query:

@mysql_query("SELECT r.* FROM roles r INNER JOIN address_role ar USING (role_id) WHERE ar.address_id='$id_address'")

I tried adding "LIMIT 1" to the tail of the query and it fixed her report, but then Mr. Blythe who shares the address at 14 The Donway West reports as being a teacher when *he's* supposed to be the technician!!!!

I'll go pull my hair out for a while -- I have some family stuff to take care of, then I'll be back at it trying to figure out what I'm doing wrong. Of course if it's obvious to someone, I'd be more than pleased to hear some details :)

[b]NEVERMIND[/b]

rookie mistake, I forgot a small part of the expression in the query, it ought to be:

@mysql_query
("SELECT r.* FROM roles r INNER JOIN address_role ar USING (role_id) WHERE ar.address_id='$id_address' AND ar.name_id='$id_name'")

[wickning1]

I think you didn't quite understand or think through what I said.

I said to store the role with name_address.. that would make it BECOME name_address_role. If you have name_address and name_address_role, you are duplicating name_address. How could you have a role at an address you're not linked to?

[SpectralDesign.Net]

My bad, that is exactly what you said, isn't it....

I think I've been putting too many late hours into this project! For now, though, I think I'm going to take a few days off from it. Thank you again for the invaluable help. You have been most kind and generous!