Jump to content
Larry Ullman's Book Forums
Sign in to follow this  
RoyS

Chapter 17 Creating The Database

Recommended Posts

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)

 

Share this post


Link to post
Share on other sites

I have now tried implementing this order_contents table. (As an aside I am teaching my grandson – aged 18 – to program and we are working through your text). We tend to create tables by putting the create query into a script, part of which is:

 

sql = "CREATE TABLE order_contents (

                       

order_id INT(10) UNSIGNED NOT NULL,

                       

print_id INT(4) UNSIGNED NOT NULL,

                       

quantity TINYINT UNSIGNED NOT NULL DEFAULT 1,

                       

price DECIMAL(8,2) UNSIGNED NOT NULL,

                       

ship_date DATETIME DEFAULT NULL,

                       

PRIMARY KEY (order_id, print_id),

                                               

INDEX (order_id),

                       

INDEX (print_id),

                       

INDEX (ship_date),

                       

FOREIGN KEY (order_id) REFERENCES orders (order_id) ON DELETE CASCADE,

                       

FOREIGN KEY (print_id) REFERENCES prints (print_id) ON DELETE CASCADE

                       

                        )ENGINE=INNODB";

           

You may be interested to know that this works with your existing code; the oc_id primary key is replaced by the composite primary key and since the code makes no explicit mention of oc_id (it’s on auto increment) it all still works. You’ll notice we also use constraints on foreign keys to enforce referential integrity. Next step is to bolt on the stuff from the previous chapter, User Registration, and to complete the checkout process as far as is feasible, e.g print out an invoice.

Share this post


Link to post
Share on other sites

If you're going to have a lot of users and files, I would recommend create a folder for each user and then store each uploaded file within there. Rename each file, too, using the ID from the database. So you have a 'users' table and a "files" table. The files table would reflect the user ID along with the other data. Then a file might be stored as x/y, where "x" is the user ID and "y" is the file ID. 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×