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:ProductsCustomerCredit_Card_DetailsOrder_DetailsState_taxShopping_CartPurchase_historyShipping typeThe 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?
E-commerce database design
beginner;sql;mysql;e commerce
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.