4
\$\begingroup\$

I am new to database design but am fairly familiar with SQL and its syntax. I want to create a database for an e-commerce website that will sell a single product type such as a shirt. They could be different sizes, colors, and design. I formulated list of information I thought would be necessary to collect from a visitor and are included in the tables I made.

Right now I have tables being:

  • Products
  • Customer
  • Credit_Card_Details
  • Order_Details
  • State_tax
  • Shopping_Cart
  • Purchase_history
  • Shipping type

The tables I created:

 CREATE TABLE `shipping_type` 
  ( 
 `type_of_shipping` VARCHAR(30) NOT NULL DEFAULT '', 
 `price`            DOUBLE(6, 2) NOT NULL, 
 `aprox_delivery`   INT(11) NOT NULL, 
 PRIMARY KEY (`type_of_shipping`) 
 ) 
 engine=innodb 
 DEFAULT charset=latin1; 



 CREATE TABLE `shopping_cart` 
  ( 
 `shopping_cart_id` INT(11) UNSIGNED NOT NULL auto_increment, 
 `inventory_id`     VARCHAR(11) NOT NULL DEFAULT '', 
 `price`            DOUBLE(6, 2) NOT NULL, 
 `date`             DATE NOT NULL, 
 `user_id`          VARCHAR(30) NOT NULL DEFAULT '', 
 `quantity`         INT(11) NOT NULL, 
 PRIMARY KEY (`shopping_cart_id`) 
 ) 
 engine=innodb 
 DEFAULT charset=latin1; 



 CREATE TABLE `state_tax` 
 ( 
 `state_name`     VARCHAR(11) NOT NULL DEFAULT '', 
 `sales_tax_rate` DOUBLE(6, 2) NOT NULL, 
 PRIMARY KEY (`state_name`) 
 ) 
 engine=innodb 
 DEFAULT charset=latin1; 



 CREATE TABLE `purchase_history` 
 ( 
 `user_id`      VARCHAR(30) NOT NULL DEFAULT '', 
 `inventory_id` INT(11) NOT NULL, 
 `date_ordered` DATE NOT NULL, 
 `order_id`     INT(11) NOT NULL, 
 `quantity`     INT(11) NOT NULL, 
 `price`        DOUBLE(6, 2) NOT NULL, 
 PRIMARY KEY (`user_id`) 
 ) 
 engine=innodb 
 DEFAULT charset=latin1; 



CREATE TABLE `products` 
( 
 `inventory_id` INT(11) UNSIGNED NOT NULL auto_increment, 
 `shirt_name`   VARCHAR(50) NOT NULL DEFAULT '', 
 `shirt_size`   VARCHAR(30) NOT NULL DEFAULT '', 
 `shirt_color`  INT(11) NOT NULL, 
 `price`        DOUBLE(6, 2) NOT NULL, 
 PRIMARY KEY (`inventory_id`) 
) 
engine=innodb 
DEFAULT charset=latin1; 



 CREATE TABLE `order_details` 
 ( 
 `order_id`         INT(11) UNSIGNED NOT NULL auto_increment, 
 `user_id`          VARCHAR(30) NOT NULL DEFAULT '', 
 `reciever_name`    VARCHAR(100) NOT NULL DEFAULT '', 
 `address`          VARCHAR(30) NOT NULL DEFAULT '', 
 `city`             VARCHAR(30) NOT NULL DEFAULT '',
 `zip`             VARCHAR(11) NOT NULL DEFAULT '',  
 `state`            VARCHAR(30) NOT NULL DEFAULT '', 
 `type_of_shipping` VARCHAR(30) NOT NULL DEFAULT '', 
 `date_ordered`     DATE NOT NULL, 
 PRIMARY KEY (`order_id`) 
 ) 
 engine=innodb 
 DEFAULT charset=latin1; 


CREATE TABLE `customer` 
 ( 
 `user_id`      VARCHAR(30) NOT NULL DEFAULT '', 
 `password`     VARCHAR(30) NOT NULL DEFAULT '', 
 `fname`        VARCHAR(30) NOT NULL DEFAULT '', 
 `lname`        VARCHAR(30) NOT NULL DEFAULT '', 
 `email`        VARCHAR(30) NOT NULL DEFAULT '', 
 `address`      VARCHAR(30) NOT NULL DEFAULT '', 
 `city`         VARCHAR(30) NOT NULL DEFAULT '', 
 `zip`          INT(11) NOT NULL, 
 `state`        VARCHAR(30) NOT NULL DEFAULT '', 
 `phone number` VARCHAR(11) DEFAULT NULL, 
 PRIMARY KEY (`user_id`) 
) 
engine=innodb 
DEFAULT charset=latin1; 

CREATE TABLE `credit_card_details` 
( 
 `cc_num`    VARCHAR(30) NOT NULL DEFAULT '', 
 `card_type` VARCHAR(30) NOT NULL DEFAULT '', 
 `sec_code`  INT(3) NOT NULL, 
 `exp_date`  DATE NOT NULL, 
 `user_id`   VARCHAR(30) NOT NULL DEFAULT '', 
 PRIMARY KEY (`cc_num`) 
) 
engine=innodb 
DEFAULT charset=latin1; 

Is this the most efficient way to do this? I am pretty sure all tables would be in third normal form, and all can be linked. If this is not the correct way to do this, could somebody please point out to me what I could be doing differently to improve my design or how to implement this design in a better way?

\$\endgroup\$
6
\$\begingroup\$

You need to add foreign keys that reference the primary keys of the tables they are linked to.

I don't usually do a whole lot with building the actual tables but I know that you are missing the foreign keys.

In this Table:

CREATE TABLE `shipping_type` 
( 
 `type_of_shipping` VARCHAR(30) NOT NULL DEFAULT '', 
 `price`            DOUBLE(6, 2) NOT NULL, 
 `aprox_delivery`   INT(11) NOT NULL, 
 PRIMARY KEY (`type_of_shipping`) 
) 
engine=innodb 
DEFAULT charset=latin1;

type_of_shipping is not a good primary key here. You should have a generic Table ID that is auto incremented and unique, so that each record has a unique ID on that table.

This table also needs a Table ID column:

CREATE TABLE `state_tax` 
( 
 `state_name`     VARCHAR(11) NOT NULL DEFAULT '', 
 `sales_tax_rate` DOUBLE(6, 2) NOT NULL, 
 PRIMARY KEY (`state_name`) 
) 
engine=innodb 
DEFAULT charset=latin1;

Even though state_name will be unique and isn't doubled up when you create the table, you still need a table ID for a table like this. If the table ever grows there is the possibility for duplicates in that column. It's not good practice to write those two tables the way that you have them.

The Password column in your Customers table needs to be encrypted. This blog post looks like it talks about hashing and storing the password.

\$\endgroup\$
9
\$\begingroup\$
  1. Never assume that you're only going to have one product. That will end up being a limiting factor of your design and can cause you a lot of pain later down the line and rules out any kind of code re-usability. Make your products table generic enough to handle any kind of product. Create color/size attribute tables that you can tie to the product if you need to.

  2. What about handling the case where people want to order as a guest or will you rule that out entirely?

  3. How will you handle returned items/payment credits, cancelled orders, or an order where the credit card is declined? Are you going to keep history for those?

  4. You will probably need more than 1 line for address. Are you going to handle international orders? If so you'll need to allow for non-numerical zip codes and ones that don't tie to a state.

  5. If you tie shopping carts to cookies then you can reload someone's shopping cart when they come back based on their cookie's ID.

  6. If you're going to keep credit card numbers in the database then you need to be PCI Compliant (https://www.pcisecuritystandards.org/).

\$\endgroup\$
  • 1
    \$\begingroup\$ Since this is for a school assignment we were given guidelines. 1 product is to be assumed (Im guessing he will show us why this is bad and how to fix it later) 2.) We are to rule guest out 3.) Will look into this. 4.) Ill add addressline2, No international customers ( ill remove country) 5.) we are told we will do this with sessions later on. and 6.) creditcards at this point we just want them to enter directly. (Again we will go over encrypt methods at a later date) \$\endgroup\$ – NerdsRaisedHand Nov 11 '13 at 21:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.