Hi everyone,
Boy was I was glad to see familiar names in the archives of this list!
Anyway, I'm at the tail end of creating an inventory management system
for one of our clients to keep track of product warranty expiration
dates. This is the first time that I've used multiple tables to attempt
to normalize a database. For the most part I think (hope) I did okay,
but there's one part that has me a little concerned. The two tables in
question look like this:
customers
----------
cust_id
cust_name
cust_div
cust_addr
cust_city
cust_state
cust_zip
cust_phone
cust_email
inventory
---------
inv_id
cust_name
cust_div
cust_city
cust_state
contract_num
prod_id
serial_num
prod_descr
manufacturer_name
manufacturer_svc_lev
so_num
vendor_name
invoice_num
sales_rep
exp_date
opt_out
When a user edits a customer's info I've used INNER JOIN (see below) to
update the city & state fields in both tables as those are the only
fields they share. It's working fine so far, but I'm concerned that I
might be missing something that'll trip me up later because I'm not real
clear on exactly what the USING clause does. There's a remote
possibility that one day there could be two customers with different
company names (cust_name) but with the same division name (cust_div) and
I can't tell if that would muck things up...?
Can anyone explain the USING clause to me in really simple terms? I've
read about it in my MySQL book and tried to find more detailed info via
both the online MySQL documentation and Google, but I still can't quite
wrap my head around it.
$row = $db->query("UPDATE customers INNER JOIN inventory USING
(cust_name, cust_div) SET customers.cust_addr = '$cust_addr',
customers.cust_city ='$cust_city', customers.cust_state ='$cust_state',
customers.cust_zip ='$cust_zip', customers.cust_phone ='$cust_phone',
customers.cust_email ='$cust_email', inventory.cust_city ='$cust_city',
inventory.cust_state ='$cust_state' WHERE customers.cust_id='$cust_id'");
One last thing: When I'm done with this project, would it be asking too
much to come here and describe the entire project to you and then
request that you guys take a look at how I set up my db tables so I'll
know if/how I can do it more efficiently next time? Compared to what
most of you guys do, it's a pretty simple project (i.e. there will only
be a few users and the db will never have more than a couple of hundred
records at a time).
Thanks,
Bev
_______________________________________________
New York PHP Community MySQL SIG
http://lists.nyphp.org/mailman/listinfo/mysql
NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com
Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php
