[email protected] wrote:
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:
Bev, if I am understanding your description, you seem not have your
normalization organized correctly. The principle here is that you want
*never* to duplicate information across tables.
As described below, you have four cust_ fields which appear also in the
inventory table; this would cause information duplication. Instead, you
would typically put a cust_id field in that inventory table which would
link to the specific customer information that appears in the customers
table and applies to the inventory record. This is called a "foreign
key" and allows you to have many records in the inventory table [but see
below] all associated with the same customer but without duplicating any
specific information.
Further, your inventory table doesn't exactly seem as though it contains
*only* information about inventory, since you have both manufacturer and
vendor information in there. Again, you would normally want separate
tables for each of these, and you would link everything together with
the foreign keys. You also have invoice information which would
typically be associated with an order. So you might think about
reorganizing everything with more tables, something like this:
customers
products
manufacturers
vendors
inventory
orders
Something like this would allow all sorts of one-to-many relationships,
which is the essence of a relational database. One customer could be
ordering many products. One manufacturer could be producing many
products. One vendor could be selling many products. Inventory would be
nothing more than a product id and a quantity. An order would be one
customer, one vendor, and many products. etc etc.......
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
"both" -- That is *exactly* what normalization is intended to avoid.
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...?
A situation like this would be utterly unimportant when div information
is confined to the customer it applies to.
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.
I've always found it easier to use specific WHERE clauses which may
result in a wordier query but seem much clearer (you can easily
understand your WHERE customers.cust_id='$cust_id', right?). But then
maybe this is just my own ignorance.
$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'");
You are apparently here trying to update customer information but that
should have nothing whatsoever to do with an inventory (or any other) table.
--
=================
Michael Southwell
Vice President, Education
NYPHP TRAINING: http://nyphp.com/Training/Indepth
_______________________________________________
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
