Michael Southwell wrote:
>
> 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.......
Michael, thanks so much for taking the time to explain things. I tend to
get stubborn about trying to figure things out on my own, but this is
obviously one time where I should have come here for advice first. I
actually started out with separate tables for everything, but then I got
confused, panicked, and dumped everything into one big honking table. I
know, rookie mistake. Later, when I realized my error, I separated most
of the stuff back into separate tables:
customers
inventory
manufacturers
users
vendors
The problem was that I still wasn't sure what to do about the inventory
table, so I created this whole Frankenstein thing in an effort to make
things work. And things do indeed work, but it's pretty clear now that I
built everything on a poor foundation. *sigh*
>> 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.
Point taken. *headdesk*
>> 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.
Got 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.
Yep, I'm fine with my WHERE clauses.
>> $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.
Understood. Please bear with me here while I try to work through this:
We'll call the client I'm building this for Company A.The main purpose
of the whole thing is to allow them to keep track of the maintenance
contracts (and their accompanying expiration dates) for products they've
sold. The data I'm dealing with looks something like this:
--Company A's customers (other companies) may have more than one
division/location. This is the "customers" table containing unique
company name/division, address, contact info. etc.
--Company A also sells equipment to vendors (smaller companies in their
same line of business, who in turn resell the equipment to their
customers). This is the "vendors" table and only contains 2 fields
(vendor_id, vendor_name).
--Like the vendors table, the "manufacturers" table is brief.
--The "inventory" table has, or rather *should* have, inventory only.
--The "users" table is for a pretty simple login/user authentication
system--Company A users are all admins who can add/edit/delete records;
their customers can only view their respective records.
Aw, for crying out loud! Typing this out and thinking about what you
said has now made me realize that just about every-freaking-thing needs
to be broken down further! Which means that almost *all* of my queries,
forms, PHP variables, etc. are going to have to change. I'm toast.
Okay, let me disregard that fact for a minute because at present I'm
more worried that the list will be dead over the weekend and I'll be
stuck alone in the wilderness.
I'll get the tables worked out, but I desperately need help with the
disconnect that is happening in my head: If I remove the redundant
fields, how do I stitch everything together so that 1.) the front-end
add & edit forms correctly INSERT/SELECT/UPDATE/DELETE data coming from
& going into multiple tables, and 2.) the client sees a web page that
contains all the relevant bits related to each item?
I have at least a dozen books on MySQL & PHP, so I'm not asking you to
do it for me. It's just that I'm feeling pretty overwhelmed at the
thought of all those tables right now and I need a basic overview of the
best way to approach things and a nudge towards what I should be looking
for in my books. Do I have to create lookup tables (I was just reading
about those the other day) or can it all be done with joins?
Thanks again for all the explanations. Now please excuse me while I go
find a paper bag to breathe into...
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
