osCommerce tables

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