Dear Larry,
I thought you might be interested in my thoughts on your design for the E-Commerce product in your PHP6 and MySql book
Normalising the E-Commerce data
An invoice for a transaction would include the following fields:
TABLE0 order_id, date, cust_id, cust_name,{print_id, print_name, price, size,desc, image, artist_id, artist_name, qty}*
The fields in brackets are the repeated order items. This data is not normalised.
First Normal Form states: 1. no repeating data 2. each column (field) must be atomic
So, separating out the repeats and expanding the names, (PK = Primary Key):
TABLE1 (order_id), date, cust_id, cust_lname, cust_fname PK = (order_id)
TABLE2A print_id, print_name, price, size, desc, image, artist_id, artist_fname, artist_lname, qty
TABLE1 is now in 1st normal form as is TABLE2A but there in no link to TABLE1 so need to add the order_id into TABLE2A giving TABLE2B:
(order_id, print_id), print_name, price, size, desc, image, artist_id, artist_fname, artist_lname, qty
PK = (order_id, print_id) This table has a composite primary key
Second Normal Form states: no field in a row must depend on only part of the primary key
This definition is more specific than your definition on page 163: “every column in a table that is not a foreign key must be dependent upon the primary key”
My understanding of second normal form is that it can apply only to tables with composite keys. Thus TABLE1 is in 2nd normal form. However, TABLE2B is not. Removing those fields that violate the rule gives TABLE3 and TABLE4
TABLE3 (order_id, print_id),qty PK = (order_id, print_id)
TABLE4 (print_id), print_name, price, size, desc, image, artist_id, artist_fname, artist_lname PK = (print_id)
Third Normal Form states: no column that is not a primary key can depend on anything except the primary key or no column can depend on other non-key columns (This looks rather like your rule for Second Normal Form)
TABLE1 and TABLE4 are not in 3rd normal form. TABLE1 splits into two tables:
TABLE5 (order_id), date, cust_id PK = (order_id)
TABLE6 (cust_id), cust_fname, cust_lname PK = (cust_id)
And TABLE4 splits into:
TABLE7 (print_id), print_name, price, size, desc, image, artist_id PK = (print_id)
TABLE8 (artist_id), artist_fname, artist_lname PK = (artist_id)
Thus we need TABLE3, TABLE5, TABLE6, TABLE7 and TABLE8
TABLE8 TABLE7 TABLE3 TABLE5 TABLE6 ARTIST PRINTS ORDER_ ORDERS CUSTOMER CONTENTS artist_id print_id order_id order_id cust_id ar_fname p_name print_id date cust_fname ar_lname price qty cust_id cust_lname size desc image artist_id
My point is that the oc_id column in the order_contents table is redundant in the same way as the id column was redundant in the messages table on P165. (I may have deviated slightly from some of your columns)