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.

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