phpshop.sql

php做的网上商店系统。简单易懂

CREATE TABLE ISshipping (
  ship_method_id int(11) DEFAULT '0' NOT NULL auto_increment,
  ship_carrier_id varchar(12),
  ship_carrier_code varchar(11),
  ship_carrier_name varchar(32),
  ship_method_code varchar(11) DEFAULT '' NOT NULL,
  ship_method_name varchar(32),
  ship_publish char(1),
  ship_handling_charge decimal(4,2),
  ship_handling_type char(1),
  ship_lead_time int(3),
  ship_heavy char(1),
  ship_msg_header text,
  ship_msg_footer text,
  cdate int(11),
  mdate int(11),
  vendor_id int(11) DEFAULT '0' NOT NULL,
  PRIMARY KEY (ship_method_id)
);



CREATE TABLE auth_user_md5 (
  user_id varchar(32) DEFAULT '' NOT NULL,
  username varchar(32) DEFAULT '' NOT NULL,
  password varchar(32) DEFAULT '' NOT NULL,
  perms varchar(255),
  PRIMARY KEY (user_id),
  UNIQUE k_username (username)
);


CREATE TABLE auth_user_vendor (
  user_id varchar(32),
  vendor_id int(11)
);


CREATE TABLE category (
  category_id varchar(32) DEFAULT '' NOT NULL,
  vendor_id int(11) DEFAULT '0' NOT NULL,
  category_name varchar(128) DEFAULT '' NOT NULL,
  category_description text,
  category_thumb_image varchar(255),
  category_full_image varchar(255),
  category_publish char(1),
  cdate int(11),
  mdate int(11),
  category_flypage varchar(255),
  PRIMARY KEY (category_id)
);


CREATE TABLE category_xref (
  category_parent_id varchar(32),
  category_child_id varchar(32),
  category_list int(11)
);


CREATE TABLE country (
  country_id int(11) DEFAULT '0' NOT NULL,
  country_name varchar(64),
  country_3_code char(3),
  country_2_code char(2),
  PRIMARY KEY (country_id)
);


CREATE TABLE currency (
  currency_id int(11) DEFAULT '0' NOT NULL auto_increment,
  currency_name varchar(64),
  currency_code char(3),
  PRIMARY KEY (currency_id)
);


CREATE TABLE function (
  function_id int(11) DEFAULT '0' NOT NULL auto_increment,
  module_id int(11),
  function_name varchar(32),
  function_class varchar(32),
  function_method varchar(32),
  function_description text,
  function_perms varchar(255),
  PRIMARY KEY (function_id)
);


CREATE TABLE language (
  language_id int(11) DEFAULT '0' NOT NULL auto_increment,
  language_name varchar(64),
  language_code char(3),
  PRIMARY KEY (language_id)
);

CREATE TABLE module (
  module_id int(11) DEFAULT '0' NOT NULL auto_increment,
  module_name varchar(255),
  module_description text,
  module_perms varchar(255),
  module_header varchar(255),
  module_footer varchar(255),
  module_publish char(1),
  list_order int(11),
  language_code_1 varchar(4),
  language_code_2 varchar(4),
  language_code_3 varchar(4),
  language_code_4 varchar(4),
  language_code_5 varchar(4),
  language_file_1 varchar(255),
  language_file_2 varchar(255),
  language_file_3 varchar(255),
  language_file_4 varchar(255),
  language_file_5 varchar(255),
  module_label_1 varchar(255),
  module_label_2 varchar(255),
  module_label_3 varchar(255),
  module_label_4 varchar(255),
  module_label_5 varchar(255),
  PRIMARY KEY (module_id)
);


CREATE TABLE order_item (
  order_item_id int(11) DEFAULT '0' NOT NULL auto_increment,
  order_id int(11),
  user_info_id int(11),
  vendor_id int(11),
  product_id int(11),
  product_quantity int(11),
  product_item_price decimal(10,2),
  order_item_currency varchar(16),
  order_status char(1),
  cdate int(11),
  mdate int(11),
  PRIMARY KEY (order_item_id)
);


CREATE TABLE order_payment (
  order_id int(11) DEFAULT '0' NOT NULL,
  payment_method_id int(11),
  order_payment_number blob,
  order_payment_expire int(11),
  order_payment_name varchar(255),
  order_payment_log text
);


CREATE TABLE order_status (
  order_status_id int(11) DEFAULT '0' NOT NULL auto_increment,
  order_status_code char(1) DEFAULT '' NOT NULL,
  order_status_name varchar(64),
  list_order int(11),
  vendor_id int(11),
  PRIMARY KEY (order_status_id)
);


CREATE TABLE orders (
  order_id int(11) DEFAULT '0' NOT NULL auto_increment,
  user_id varchar(32) DEFAULT '' NOT NULL,
  vendor_id int(11) DEFAULT '0' NOT NULL,
  order_number varchar(32),
  user_info_id int(11),
  order_subtotal decimal(10,2),
  order_tax decimal(10,2),
  order_shipping decimal(10,2),
  order_shipping_tax decimal(10,2),
  order_currency varchar(16),
  order_status char(1),
  cdate int(11),
  mdate int(11),
  ship_method_id int(11),
  PRIMARY KEY (order_id)
);


CREATE TABLE payment_method (
  payment_method_id int(11) DEFAULT '0' NOT NULL auto_increment,
  vendor_id int(11),
  payment_method_name varchar(255),
  shopper_group_id int(11),
  payment_method_discount decimal(10,2),
  list_order int(11),
  payment_method_code varchar(8),
  enable_processor char(1),
  PRIMARY KEY (payment_method_id)
);


CREATE TABLE product (
  product_id int(11) DEFAULT '0' NOT NULL auto_increment,
  vendor_id int(11) DEFAULT '0' NOT NULL,
  product_parent_id int(11),
  product_sku varchar(64) DEFAULT '' NOT NULL,
  product_s_desc varchar(255),
  product_desc text,
  product_thumb_image varchar(255),
  product_full_image varchar(255),
  product_publish char(1),
  product_weight decimal(10,4),
  product_weight_uom varchar(32) DEFAULT 'pounds.',
  product_length decimal(10,4),
  product_width decimal(10,4),
  product_height decimal(10,4),
  product_lwh_uom varchar(32) DEFAULT 'inches',
  product_url varchar(255),
  product_in_stock int(11),
  product_available_date int(11),
  product_special char(1),
  product_discount_id int(11),
  ship_code_id int(11),
  cdate int(11),
  mdate int(11),
  product_name varchar(64),
  PRIMARY KEY (product_id)
);


CREATE TABLE product_attribute (
  product_id int(11) DEFAULT '0' NOT NULL,
  attribute_name char(255) DEFAULT '' NOT NULL,
  attribute_value char(255)
);


CREATE TABLE product_attribute_sku (
  product_id int(11) DEFAULT '0' NOT NULL,
  attribute_name char(255) DEFAULT '' NOT NULL,
  attribute_list int(11)
);


CREATE TABLE product_category_xref (
  category_id varchar(32),
  product_id int(11) DEFAULT '0' NOT NULL,
  product_list int(11)
);

CREATE TABLE product_price (
  product_price_id int(11) DEFAULT '0' NOT NULL auto_increment,
  product_id int(11) DEFAULT '0' NOT NULL,
  product_price decimal(10,2),
  product_currency char(16),
  product_price_vdate int(11),
  product_price_edate int(11),
  cdate int(11),
  mdate int(11),
  shopper_group_id int(11),
  PRIMARY KEY (product_price_id)
);


CREATE TABLE sessions (
  sess_id varchar(255) DEFAULT '' NOT NULL,
  mdate int(11) DEFAULT '0' NOT NULL,
  session_data text,
  PRIMARY KEY (sess_id),
  KEY mdate (mdate)
);


CREATE TABLE shopper_group (
  shopper_group_id int(11) DEFAULT '0' NOT NULL auto_increment,
  vendor_id int(11),
  shopper_group_name varchar(32),
  shopper_group_desc text,
  PRIMARY KEY (shopper_group_id)
);


CREATE TABLE shopper_vendor_xref (
  user_id varchar(32),
  vendor_id int(11),
  shopper_group_id int(11),
  customer_number varchar(32)
);

CREATE TABLE tax_rate (
  tax_rate_id int(11) DEFAULT '0' NOT NULL auto_increment,
  vendor_id int(11),
  tax_state varchar(64),
  tax_country varchar(64),
  mdate int(11),
  tax_rate decimal(10,4),
  PRIMARY KEY (tax_rate_id)
);



CREATE TABLE user_info (
  user_info_id int(11) DEFAULT '0' NOT NULL auto_increment,
  user_id varchar(32) DEFAULT '' NOT NULL,
  address_type char(2),
  address_type_name varchar(32),
  company varchar(64),
  title varchar(32),
  last_name varchar(32),
  first_name varchar(32),
  middle_name varchar(32),
  phone_1 varchar(32),
  phone_2 varchar(32),
  fax varchar(32),
  address_1 varchar(64) DEFAULT '' NOT NULL,
  address_2 varchar(64),
  city varchar(32) DEFAULT '' NOT NULL,
  state varchar(32) DEFAULT '' NOT NULL,
  country varchar(32) DEFAULT 'US' NOT NULL,
  zip varchar(32) DEFAULT '' NOT NULL,
  user_email varchar(255),
  extra_field_1 varchar(255),
  extra_field_2 varchar(255),
  extra_field_3 varchar(255),
  extra_field_4 char(1),
  extra_field_5 char(1),
  cdate int(11),
  mdate int(11),
  PRIMARY KEY (user_info_id)
);

CREATE TABLE vendor (
  vendor_id int(11) DEFAULT '0' NOT NULL auto_increment,
  vendor_name varchar(64),
  contact_last_name varchar(32) DEFAULT '' NOT NULL,
  contact_first_name varchar(32) DEFAULT '' NOT NULL,
  contact_middle_name varchar(32),
  contact_title varchar(32),
  contact_phone_1 varchar(32) DEFAULT '' NOT NULL,
  contact_phone_2 varchar(32),
  contact_fax varchar(32),
  contact_email varchar(255),
  vendor_phone varchar(32),
  vendor_address_1 varchar(64) DEFAULT '' NOT NULL,
  vendor_address_2 varchar(64),
  vendor_city varchar(32) DEFAULT '' NOT NULL,
  vendor_state varchar(32) DEFAULT '' NOT NULL,
  vendor_country varchar(32) DEFAULT 'US' NOT NULL,
  vendor_zip varchar(32) DEFAULT '' NOT NULL,
  vendor_store_name varchar(128) DEFAULT '' NOT NULL,
  vendor_store_desc text,
  vendor_category_id int(11),
  vendor_thumb_image varchar(255),
  vendor_full_image varchar(255),
  vendor_currency varchar(16),
  cdate int(11),
  mdate int(11),
  vendor_image_path varchar(255),
  PRIMARY KEY (vendor_id)
);

CREATE TABLE vendor_category (
  vendor_category_id int(11) DEFAULT '0' NOT NULL auto_increment,
  vendor_category_name varchar(64),
  vendor_category_desc text,
  PRIMARY KEY (vendor_category_id)
);