Section 16.2. Database

   

---

16.2 Database

In order to make the store work, create a database in MySQL that follows the table description indicated below. You can use another database with little modification. I have not used a username and password in the code below for simplicity's sake. The database in the code is called "storecybertrailscomtest." The tables look like this:

16.2.1 Categories Table

 catid int(11)   PRI  NULL auto_increment  catname varchar(100) 

16.2.2 Subcategories Table

 subcatid int(11)  PRI  NULL  auto_increment  subcatname varchar(75) 

16.2.3 ProductsCategories Table

 productid  int(11)   0  catid int(11)        0 subcatid  int(11)    0 

16.2.4 Products Table

 productid int(11)  PRI  NULL  auto_increment  name varchar(150)  YES  NULL price float  YES   NULL shortdescription varchar(200)  YES  NULL longdescription text  YES  NULL smimage varchar(75)  YES  NULL lgimage varchar(75)  YES   NULL ships tinyint(1)  0 isfeatured tinyint(1) 0 

16.2.5 OptionSets Table

 optionsetid int(11)PRINULLauto_increment  optionsetname varchar(50) 

16.2.6 Options Table

 optionidint(11)  PRI   NULL   auto_increment  optionname varchar(50) optionsetid int(11) 0 priceadd float YES  NULL 

16.2.7 ProductsOptionSets Table

 productid int(11)     0  optionsetid int(11)   0 

16.2.8 Orders Table

 orderidint(11) PRI  NULL  auto_increment  customeridint(11)0 shipinfoidint(11)0 subtotal float 0 shipping float 0 tax float 0 grandtotal float 0 orderdate varchar(50) isprocessed tinyint(1)  0 

16.2.9 Customers Table

 customerid  int(11)  PRI  NULL  auto_increment  billaddress  varchar(150) binary billstate  varchar(50) billzipcode  varchar (10)0 billcountry  varchar(50) billphone  varchar(50) billemail  varchar(75) creditnumber  varchar(50) creditname  varchar(100) creditexpdate  varchar(50) billname  varchar(50) billcity  varchar(75)  YES  NULL 

16.2.10 ShippingInfo Table

 shipinfoid int(11)   PRI   NULL  auto_increment  shipaddress  varchar(150) shipcity  varchar(100) shipstate  varchar(75) shipzipcode  varchar(10)   YES  NULL shipcountry  varchar(75)   YES   NULL shipemail  varchar(75)   YES   NULL shipname  varchar(75)   YES  NULL shipphone  varchar(20)  YES  NULL 

16.2.11 OrderProducts Table

 ordersproductsid int(11)  PRI  NULL  auto_increment  orderid int(11)  0 productid int(11) 0 qty int(11)        0 price varchar(10) YES  NULL 

16.2.12 OrderProductOptionChoice Table

 ordersproductsid  int(11) YES   NULL  optionid int(11)  YES  NULL 

Once you have created this database, populate it with some test data. Then you just need a little setup and you're ready.

---

Top