Some Background on the Tables

 

Introduction.  While this is definitely beating this issue over the head in many ways, it is important to determine as much as you reasonably can about the tables you will use before starting your developments.  While, in reality, it is nearly impossible to do this with total foresight, it is still important to do this.  But, as you should know, since this is academia and because I have developed these sorts of webs before this presentation should be quite accurate.

Tables. Remember we have several different types of things that we already know we need to consider in our store.  In some people's jargon, these are our entities.

Browsing Capabilities

  • departments
  • products
    • associations between departments and products
  • actual attributes of the products
    • associations between products and their attributes

 

Shopping Basket

  • current shopping basket
    • include totals
  • current items in the basket

 

Profile Pages

  • shopper billing and shipping information
    • uniquely identified by email address and password
  • must decide whether to retain any payment info such as credit card
    • we will retain credit card information

 

Checkout Resources

  • actual shipping address
    • which may differ from that in the profile
  • shipping costs
  • taxes
  • payment information

 

Each of these aspects of the ecommerce store require us to develop tables to retain appropriate information.

Some other issues/entities we could integrate into our over all functionality are for

  • sales inside/outside the US
  • different types of shipping
    • free shipping
    • overnight
    • two day
    • outside the country
  • different types of currency
  • the product source particularly if different form our firm
    • particularly important if handled separately/differently
  • availability
    • time until available
    • we will assume everything is in stock
  • integrating inventory/production considerations
  • maintaining information about the order status
  • related products
    • many firms try to make certain that a buyer is reminded of products related to those they are already interested in
  • more sophistication with order status and dealing with problems in the order
    • maybe orders get partitioned into smaller orders
    • maybe some items are backordered
      • the customer changes their mind
    • maybe there are delivery/tracking problems
  • different taxes and surcharges

Obviously, since this is a semester course we need to limit what sorts of options we consider.  But it is hoped that you will learn more than enough within this course to be able to develop these sorts of options.

Fields.  Now I want to reiterate the preceding outline and include the appropriate fields under the appropriate table.  The following list of tables gives the important characteristics of each table required for our database.

 

department
Key Field Data Type Allow Nulls
id_department int no
  department_name varchar yes
  department_description varchar yes
  department_image varchar yes

 

products
Key Field Data Type Allow Nulls
id_product int no
  product_name varchar yes
  product_description varchar yes
  product_image varchar yes
  weight float yes
  price float yes

 

department_products
Key Field Data Type Allow Nulls
id_department_product int no
  id_department int yes
  id_product int yes

 

The following diagram should help illustrate how the department_products table can be used to place the products in an associated department.  Notice how the keys within a table are used to identify particular departments and products as foreign keys within the department_products table.

I have rotated and reduced the key symbol to represent a foreign key.

 

 

Now we start presenting the tables associated with the product attributes.

 

attribute_category
Key Field Data Type Allow Nulls
id_attribute_category int no
  category_name varchar yes

 

attribute
Key Field Data Type Allow Nulls
id_attribute int no
  attribute_name varchar yes
  id_attribute_category int yes

 

product_attribute
Key Field Data Type Allow Nulls
id_product_attribute int no
  id_attribute int yes
  id_product int yes

 

The following diagram displays how each of the products are assigned their attributes.

 

 

While we don't truly "need" the following table, including it will make our queries and code much easier to structure.  If you are wedded to the idea of maintaining a purely minimalist coverage of our needs with normalized tables, we will be breaking your rules.  Reality is often different than this, though maintaining this ideal as much as reasonable has its purposes.

Later, when we are developing code to actually display our products dynamically on pages while giving all of the appropriate attributes the customer will select I will discuss why having this additional table is more than convenient.  It actually will allow us to nest some loops based on queries much more easily.  Though, it should make sense that having a table that directly relates products with their attribute_categories is something we can make use of.  This is true even if by database theory we can derive these relationships from other tables, since writing the queries and PHP code will be much more direct and make more sense.

 

product_attribute_category
Key Field Data Type Allow Nulls
id_prod_attribute_category int no
  id_product int yes
  id_attribute_category int yes

 

First the attribute categories are developed/named.  Some example attribute categories are things like size, color and flavor.  Then the attributes are named and associated with a particular attribute category in the attribute table.  Then the attributes are associated with the products in the product attribute table.  Notice that while the attributes are still within a category, not all of the attributes in a category need to be available to a product that has this particular attribute category.

Now we return to our development of the other tables we will be using by starting on those associated with the shopping basket.  We will make use of two common strategies when dealing with the check out experience.

  • we will retain a profile for a shopper
    • based on email address
    • password
  • we will retain credit card information

If we didn't retain the credit card information in the shopper's profile, we would need to keep a separate table associated with this, probably in a table called payment_data.  Retaining credit card information allows the site to create the sorts of simplified "one button" purchasing experience that most shoppers seem to prefer.  But, there are obviously security issues to resolve when a shopper can log on to their profile and readily access their own information.

 

basket
Key Field Data Type Allow Nulls
id_basket int no
  order_placed int yes
  sub_total float yes
  total float yes
  cost_shipping float yes
  tax float yes
  date_created datetime yes

 

basket_item
Key Field Data Type Allow Nulls
id_basket_item int no
  id_product int yes
  price float yes
  weight float yes
  product_name varchar yes
  item_quantity int yes
  id_basket int yes
  attribute1 varchar yes
  attribute2 varchar yes

 

 

profile
Key Field Data Type Allow Nulls
id_profile int no
  email varchar yes
  password varchar yes
  bill_first_name varchar yes
  bill_last_name varchar yes
  bill_address varchar yes
  bill_city varchar yes
  bill_state varchar yes
  bill_zipcode varchar yes
  bill_phone varchar yes
  bill_fax varchar yes
  ship_first_name varchar yes
  ship_last_name varchar yes
  ship_address varchar yes
  ship_city varchar yes
  ship_state varchar yes
  ship_zipcode varchar yes
  ship_phone varchar yes
  ship_fax varchar yes
  card_type varchar yes
  card_number varchar yes
  expiration_month varchar yes
  expiration_year varchar yes
  card_name varchar yes
  date_registered datetime yes

 

 

order_data
Key Field Data Type Allow Nulls
id_order int no
  id_basket int yes
  email varchar yes
  bill_first_name varchar yes
  bill_last_name varchar yes
  bill_address varchar yes
  bill_city varchar yes
  bill_state varchar yes
  bill_zipcode varchar yes
  bill_phone varchar yes
  bill_fax varchar yes
  ship_first_name varchar yes
  ship_last_name varchar yes
  ship_address varchar yes
  ship_city varchar yes
  ship_state varchar yes
  ship_zipcode varchar yes
  ship_phone varchar yes
  ship_fax varchar yes
  card_type varchar yes
  card_number varchar yes
  expiration_month varchar yes
  expiration_year varchar yes
  card_name varchar yes
  date_ordered datetime yes

 

The following diagram illustrates how the keys and foreign keys are used in each table to relate the order_data back to the shopper via their profile and their current shopping basket.  It also shows how the basket is built from the basket_items which are related back to the information about individual products.

We will develop our code so that it allows for a user to override some information such as the shipping address for an order without updating their profile.

 

 

Our next table relates to the status of an order.  It makes use of only the id_order from the order_data table to refer back to its sources.

 

order_status
Key Field Data Type Allow Nulls
id_order_status int no
  id_order int yes
  id_stage int yes
  date_shipped datetime yes
  date_fulfilled datetime yes
  date_processed datetime yes
  notes varchar yes
  shipping_number varchar yes
  id_processed int yes

 

Finally, our last two tables are essentially stand alone information relating to determining shipping costs by weight and taxes by the state the client lives in.

The shipping computations can really get quite a bit more involved considering different sorts of shipping options and locations.  We will leave these sorts of issues for courses beyond this course.

 

shipping
Key Field Data Type Allow Nulls
id_ship_interval int no
  lower_limit float yes
  upper_limit float yes
  shipping_cost float yes

 

taxes_state
Key Field Data Type Allow Nulls
id_state int no
  state_name varchar yes
  state_abbreviation varchar yes
  tax_rate float yes