osCommerce tables

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to: navigation, search

Navigation

computing: software: web software: osCommerce: tables

Overview

This is a (probably incomplete and almost certainly out of date, but maybe still useful) listing of tables used by osCommerce as of 2005-07-26.

Tables

address_book

  • address_book_id# -- integer - not null, auto inc
  • customers_id -- integer - not null, default 0
  • entry_gender -- char(1) - not null
  • entry_company -- varchar(32)
  • entry_firstname -- varchar(32) - not null
  • entry_lastname -- varchar(32) - not null
  • entry_street_address -- varchar(64) - not null
  • entry_suburb -- varchar(32)
  • entry_postcode -- varchar(10) - not null
  • entry_city -- varchar(32) - not null
  • entry_state -- varchar(32)
  • entry_country_id -- integer - not null, default 0
  • entry_zone_id -- integer - not null, default 0

address_format

  • address_format_id# -- integer - not null, auto inc
  • address_format -- varchar(128) - not null
  • address_summary -- varchar(48) - not null

banners

  • banners_id# -- integer - not null, auto inc
  • banners_title -- varchar(64) - not null
  • banners_url -- varchar(255) - not null
  • banners_image -- varchar(64) - not null
  • banners_group -- varchar(10) - not null
  • banners_html_text -- text
  • expires_impressions -- int(7) - default 0
  • expires_date -- datetime
  • date_scheduled -- datetime
  • date_added -- datetime - not null, default 0000-00-00 00:00:00
  • date_status_change -- datetime
  • status -- int(1) - not null, default 1

banners_history

  • banners_history_id# -- integer - not null, auto inc
  • banners_id -- integer - not null, default 0
  • banners_shown -- int(5) - not null, default 0
  • banners_clicked -- int(5) - not null, default 0
  • banners_history_date -- datetime - not null, default 0000-00-00 00:00:00

categories

  • categories_id# -- integer - not null, auto inc
  • categories_image -- varchar(64)
  • parent_id -- integer - not null, default 0
  • sort_order -- int(3)
  • date_added -- datetime
  • date_modified -- datetime

categories_description

  • categories_id# -- integer - not null, default 0
  • language_id# -- integer - not null, default 1
  • categories_name -- varchar(32) - not null

configuration

  • configuration_id# -- integer - not null, auto inc
  • configuration_title -- varchar(64) - not null
  • configuration_key -- varchar(64) - not null
  • configuration_value -- varchar(255) - not null
  • configuration_description -- varchar(255) - not null
  • configuration_group_id -- integer - not null, default 0
  • sort_order -- int(5)
  • last_modified -- datetime
  • date_added -- datetime - not null, default 0000-00-00 00:00:00
  • use_function -- varchar(255)
  • set_function -- varchar(255)

configuration_group

  • configuration_group_id# -- integer - not null, auto inc
  • configuration_group_title -- varchar(64) - not null
  • configuration_group_description -- varchar(255) - not null
  • sort_order -- int(5)
  • visible -- int(1) - default 1

counter

  • startdate -- char(8)
  • counter -- int(12)

counter_history

  • month -- char(8)
  • counter -- int(12)

countries

  • countries_id# -- integer - not null, auto inc
  • countries_name -- varchar(64) - not null
  • countries_iso_code_2 -- char(2)
  • countries_iso_code_3 -- char(3)
  • address_format_id -- integer - not null, default 0

currencies

  • currencies_id# -- integer - not null, auto inc
  • title -- varchar(32) - not null
  • code -- char(3) - not null
  • symbol_left -- varchar(12)
  • symbol_right -- varchar(12)
  • decimal_point -- char(1)
  • thousands_point -- char(1)
  • decimal_places -- char(1)
  • value -- float(13,8)
  • last_updated -- datetime

customers

  • customers_id# -- integer - not null, auto inc
  • customers_gender -- char(1) - not null
  • customers_firstname -- varchar(32) - not null
  • customers_lastname -- varchar(32) - not null
  • customers_dob -- datetime - not null, default 0000-00-00 00:00:00
  • customers_email_address -- varchar(96) - not null
  • customers_default_address_id -- integer - not null, default 0
  • customers_telephone -- varchar(32) - not null
  • customers_fax -- varchar(32)
  • customers_password -- varchar(40) - not null
  • customers_newsletter -- char(1)

customers_basket

  • customers_basket_id# -- integer - not null, auto inc
  • customers_id -- integer - not null, default 0
  • products_id -- tinytext - not null
  • customers_basket_quantity -- int(2) - not null, default 0
  • final_price -- decimal(15,4) - not null, default 0.0000
  • customers_basket_date_added -- varchar(8)

customers_basket_attributes

  • customers_basket_attributes_id# -- integer - not null, auto inc
  • customers_id -- integer - not null, default 0
  • products_id -- tinytext - not null
  • products_options_id -- integer - not null, default 0
  • products_options_value_id -- integer - not null, default 0

customers_info

  • customers_info_id# -- integer - not null, default 0
  • customers_info_date_of_last_logon -- datetime
  • customers_info_number_of_logons -- int(5)
  • customers_info_date_account_created -- datetime
  • customers_info_date_account_last_modified -- datetime
  • global_product_notifications -- int(1) - default 0

geo_zones

  • geo_zone_id# -- integer - not null, auto inc
  • geo_zone_name -- varchar(32) - not null
  • geo_zone_description -- varchar(255) - not null
  • last_modified -- datetime
  • date_added -- datetime - not null

languages

  • languages_id# -- integer - not null, auto inc
  • name -- varchar(32) - not null
  • code -- char(2) - not null
  • image -- varchar(64)
  • directory -- varchar(32)
  • sort_order -- int(3)

manufacturers

  • manufacturers_id# -- integer - not null, auto inc
  • manufacturers_name -- varchar(32) - not null
  • manufacturers_image -- varchar(64)
  • date_added -- datetime
  • last_modified -- datetime

manufacturers_info

  • manufacturers_id# -- integer - not null, default 0
  • languages_id# -- integer - not null, default 0
  • manufacturers_url -- varchar(255) - not null
  • url_clicked -- int(5) - not null, default 0
  • date_last_click -- datetime

newsletters

  • newsletters_id# -- integer - not null, auto inc
  • title -- varchar(255) - not null
  • content -- text - not null
  • module -- varchar(255) - not null
  • date_added -- datetime - not null, default 0000-00-00 00:00:00
  • date_sent -- datetime
  • status -- int(1)
  • locked -- int(1) - default 0

orders

  • orders_id# -- integer - not null, auto inc
  • customers_id -- integer - not null, default 0
  • customers_name -- varchar(64) - not null
  • customers_company -- varchar(32)
  • customers_street_address -- varchar(64) - not null
  • customers_suburb -- varchar(32)
  • customers_city -- varchar(32) - not null
  • customers_postcode -- varchar(10) - not null
  • customers_state -- varchar(32)
  • customers_country -- varchar(32) - not null
  • customers_telephone -- varchar(32) - not null
  • customers_email_address -- varchar(96) - not null
  • customers_address_format_id -- int(5) - not null, default 0
  • delivery_name -- varchar(64) - not null
  • delivery_company -- varchar(32)
  • delivery_street_address -- varchar(64) - not null
  • delivery_suburb -- varchar(32)
  • delivery_city -- varchar(32) - not null
  • delivery_postcode -- varchar(10) - not null
  • delivery_state -- varchar(32)
  • delivery_country -- varchar(32) - not null
  • delivery_address_format_id -- int(5) - not null, default 0
  • billing_name -- varchar(64) - not null
  • billing_company -- varchar(32)
  • billing_street_address -- varchar(64) - not null
  • billing_suburb -- varchar(32)
  • billing_city -- varchar(32) - not null
  • billing_postcode -- varchar(10) - not null
  • billing_state -- varchar(32)
  • billing_country -- varchar(32) - not null
  • billing_address_format_id -- int(5) - not null, default 0
  • payment_method -- varchar(32) - not null
  • cc_type -- varchar(20)
  • cc_owner -- varchar(64)
  • cc_number -- varchar(32)
  • cc_expires -- varchar(4)
  • last_modified -- datetime
  • date_purchased -- datetime
  • orders_status -- int(5) - not null
  • orders_date_finished -- datetime
  • currency -- char(3)
  • currency_value -- decimal(14,6)

orders_products

  • orders_products_id# -- integer - not null, auto inc
  • orders_id -- integer - not null, default 0
  • products_id -- integer - not null, default 0
  • products_model -- varchar(12)
  • products_name -- varchar(64) - not null
  • products_price -- decimal(15,4) - not null, default 0.0000
  • final_price -- decimal(15,4) - not null, default 0.0000
  • products_tax -- decimal(7,4) - not null, default 0.0000
  • products_quantity -- int(2) - not null, default 0

orders_products_attributes

  • orders_products_attributes_id# -- integer - not null, auto inc
  • orders_id -- integer - not null, default 0
  • orders_products_id -- integer - not null, default 0
  • products_options -- varchar(32) - not null
  • products_options_values -- varchar(32) - not null
  • options_values_price -- decimal(15,4) - not null, default 0.0000
  • price_prefix -- char(1) - not null

orders_products_download

  • orders_products_download_id# -- integer - not null, auto inc
  • orders_id -- integer - not null, default 0
  • orders_products_id -- integer - not null, default 0
  • orders_products_filename -- varchar(255) - not null
  • download_maxdays -- int(2) - not null, default 0
  • download_count -- int(2) - not null, default 0

orders_status

  • orders_status_id# -- integer - not null, default 0
  • language_id -- integer - not null, default 1
  • orders_status_name -- varchar(32) - not null

orders_status_history

  • orders_status_history_id# -- integer - not null, auto inc
  • orders_id -- integer - not null, default 0
  • orders_status_id -- int(5) - not null, default 0
  • date_added -- datetime - not null, default 0000-00-00 00:00:00
  • customer_notified -- int(1) - default 0
  • comments -- text

orders_total

  • orders_total_id# -- integer unsigned - not null, auto inc
  • orders_id -- integer - not null, default 0
  • title -- varchar(255) - not null
  • text -- varchar(255) - not null
  • value -- decimal(15,4) - not null, default 0.0000
  • class -- varchar(32) - not null
  • sort_order -- integer - not null, default 0

products

  • products_id# -- integer - not null, auto inc
  • products_quantity -- int(4) - not null, default 0
  • products_model -- varchar(12)
  • products_image -- varchar(64)
  • products_price -- decimal(15,4) - not null, default 0.0000
  • products_date_added -- datetime - not null, default 0000-00-00 00:00:00
  • products_last_modified -- datetime
  • products_date_available -- datetime
  • products_weight -- decimal(5,2) - not null, default 0.00
  • products_status -- tinyint(1) - not null, default 0
  • products_tax_class_id -- integer - not null, default 0
  • manufacturers_id -- integer
  • products_ordered - integer - not null, default 0

products_attributes

  • products_attributes_id# -- integer - not null, auto inc
  • products_id -- integer - not null, default 0
  • options_id -- integer - not null, default 0
  • options_values_id -- integer - not null, default 0
  • options_values_price -- decimal - not null, default 0.0000
  • price_prefix -- char(1) - not null

products_attributes_download

  • products_attributes_id# -- integer - not null, default 0
  • products_attributes_filename -- varchar(255) - not null
  • products_attributes_maxdays -- int(2) - default 0
  • products_attributes_maxcount -- int(2) - default 0

products_description

  • products_id# -- integer - not null, auto inc
  • language_id# -- integer - not null, default 1
  • products_name -- varchar(64) - not null
  • products_description -- text
  • products_url -- varchar(255)
  • products_viewed -- int(5) - default 0

products_notifications

  • products_id# -- integer - not null, default 0
  • customers_id# -- integer - not null, default 0
  • date_added -- datetime - not null, default 0000-00-00 00:00:00

products_options

  • products_options_id# -- integer - not null, default 0
  • language_id# -- integer -- not null, default 1
  • products_options_name -- varchar(32) - not null

products_options_values

  • products_options_values_id# -- integer - not null, default 0
  • language_id# -- integer - not null, default 1
  • products_options_values_name -- varchar(64) - not null

products_options_values_to_products_options

  • products_options_values_to_products_options_id# -- integer - not null, auto inc
  • products_options_id -- integer - not null, default 0
  • products_options_values_id -- integer - not null, default 0

products_to_categories

  • products_id# -- integer - not null, defaul 0
  • categories_id# -- integer - not null, default 0

reviews

  • reviews_id# -- integer - not null, auto inc
  • products_id -- integer - not null, default 0
  • customers_id -- integer
  • customers_name -- varchar(64) - not null
  • reviews_rating -- int(1)
  • date_added -- datetime
  • last_modified -- datetime
  • reviews_read -- int(5) - not null, default 0

reviews_description

  • reviews_id# -- integer - not null, default 0
  • languages_id# -- integer - not null, default 0
  • reviews_text -- text - not null

sessions

  • sesskey# -- varchar(32) - not null
  • expiry -- integer unsigned - not null, default 0
  • value -- text - not null

specials

  • specials_id# -- integer - not null, auto inc
  • products_id -- integer - not null, default 0
  • specials_new_products_price - decimal(15,4) - not null, default 0.0000
  • specials_date_added -- datetime
  • specials_last_modified -- datetime
  • expires_date -- datetime
  • date_status_change -- datetime
  • status -- int(1) - not null

tax_class

  • tax_class_id# -- integer - not null, auto inc
  • tax_class_title -- varchar(32) - not null
  • tax_class_description -- varchar(255) - not null
  • last_modified -- datetime
  • date_added -- datetime - not null, default 0000-00-00 00:00:00

tax_rates

  • tax_rates_id# -- integer - not null, auto inc
  • tax_zone_id -- integer - not null, default 0
  • tax_class_id -- integer - not null, default 0
  • tax_priority -- int(5) - default 1
  • tax_rate -- decimal(7,4) - not null, default 0.0000
  • tax_description -- varchar(255) - not null
  • last_modified -- datetime
  • date_added -- datetime - not null, default 0000-00-00 00:00:00

whos_online

  • customer_id -- integer
  • full_name -- varchar(34) - not null
  • session_id -- varchar(128) - not null
  • ip_address -- varchar(15) - not null
  • time_entry -- varchar(14) - not null
  • time_last_click -- varchar(14) - not null
  • last_page_url -- varchar(34) - not null

zones

  • zone_id# -- integer - not null, autoinc
  • zone_country_id -- integer - not null, default 0
  • zone_code -- varchar(32) - not null
  • zone_name -- varchar(32) - not null

zones_to_geo_zones

  • association_id# - integer - not null, autoinc
  • zone_country_id - integer - not null
  • zone_id - integer
  • geo_zone_id - integer
  • last_modified - datetime
  • date_added - datetime - not null