osCommerce tables

The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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_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