schema.sql

售票网站系统

/* - schema.sql for hbpc_shop
**
**  Creates the schema for the hbpc_shop store
*/

/* drop existing tables: */
if exists (SELECT * FROM sysobjects WHERE id = object_id('hbpc_shop_promo_cross') and sysstat & 0xf = 3)
    DROP TABLE hbpc_shop_promo_cross 
GO
if exists (SELECT * FROM sysobjects WHERE id = object_id('hbpc_shop_promo_price') and sysstat & 0xf = 3)
    DROP TABLE hbpc_shop_promo_price 
GO
if exists (SELECT * FROM sysobjects WHERE id = object_id('hbpc_shop_dept_prod') and sysstat & 0xf = 3)
    DROP TABLE hbpc_shop_dept_prod 
GO
if exists (SELECT * FROM sysobjects WHERE id = object_id('hbpc_shop_dept') and sysstat & 0xf = 3)
    DROP TABLE hbpc_shop_dept 
GO
if exists (SELECT * FROM sysobjects WHERE id = object_id('hbpc_shop_single_val_attr') and sysstat & 0xf = 3)
    DROP TABLE hbpc_shop_single_val_attr 
GO
if exists (SELECT * FROM sysobjects WHERE id = object_id('hbpc_shop_multi_val_attr') and sysstat & 0xf = 3)
    DROP TABLE hbpc_shop_multi_val_attr 
GO
if exists (SELECT * FROM sysobjects WHERE id = object_id('hbpc_shop_variant') and sysstat & 0xf = 3)
    DROP TABLE hbpc_shop_variant 
GO
if exists (SELECT * FROM sysobjects WHERE id = object_id('hbpc_shop_product') and sysstat & 0xf = 3)
    DROP TABLE hbpc_shop_product 
GO
if exists (SELECT * FROM sysobjects WHERE id = object_id('hbpc_shop_receipt') and sysstat & 0xf = 3)
    DROP TABLE hbpc_shop_receipt 
GO
if exists (SELECT * FROM sysobjects WHERE id = object_id('hbpc_shop_receipt_item') and sysstat & 0xf = 3)
    DROP TABLE hbpc_shop_receipt_item 
GO
if exists (SELECT * FROM sysobjects WHERE id = object_id('hbpc_shop_basket') and sysstat & 0xf = 3)
    DROP TABLE hbpc_shop_basket 
GO
if exists (SELECT * FROM sysobjects WHERE id = object_id('hbpc_shop_shopper') and sysstat & 0xf = 3)
    DROP TABLE hbpc_shop_shopper 
GO

CREATE TABLE hbpc_shop_product(
    pf_id VARCHAR(100) NOT NULL,
    name VARCHAR(255) NULL,
    description VARCHAR(255) NULL,
    list_price INT NULL,
    image_file VARCHAR(255) NULL,
    image_width INT NULL,
    image_height INT NULL,
    sale_price INT NULL,
    sale_start DATETIME NULL,
    sale_end DATETIME NULL,
    tax_vat_included TINYINT NULL,
    tax_vat_rate REAL NULL,
    attr_label1 VARCHAR(100) NULL,
    attr_label2 VARCHAR(100) NULL,
    attr_label3 VARCHAR(100) NULL,
    attr_label4 VARCHAR(100) NULL,
    attr_label5 VARCHAR(100) NULL,
    PRIMARY KEY (pf_id)
) 
GO

CREATE TABLE hbpc_shop_dept (
    dept_id INT NOT NULL,
    dept_name VARCHAR(255) NULL,
    dept_description VARCHAR(255) NULL,
    PRIMARY KEY (dept_id)
) 
GO

CREATE TABLE hbpc_shop_dept_prod (
    dept_id INT NOT NULL, 
    pf_id VARCHAR(100) NOT NULL,
    PRIMARY KEY (dept_id, pf_id),
    FOREIGN KEY (dept_id) REFERENCES hbpc_shop_dept,
    FOREIGN KEY (pf_id) REFERENCES hbpc_shop_product
) 
GO

CREATE TABLE hbpc_shop_variant(
    sku VARCHAR(100) NOT NULL, 
    pf_id VARCHAR(100) NOT NULL, 
    attr_value1 VARCHAR(100) NULL, 
    attr_value2 VARCHAR(100) NULL, 
    attr_value3 VARCHAR(100) NULL, 
    attr_value4 VARCHAR(100) NULL, 
    attr_value5 VARCHAR(100) NULL, 
    PRIMARY KEY (sku),
    FOREIGN KEY (pf_id) REFERENCES hbpc_shop_product
) 
GO

CREATE TABLE hbpc_shop_multi_val_attr (
    pf_id VARCHAR(100) NOT NULL, 
    mva_name    VARCHAR(100) NOT NULL,
    mva_value   VARCHAR(100) NOT NULL,
    mva_index   INT NULL,
    PRIMARY KEY (pf_id, mva_name, mva_value),
    FOREIGN KEY (pf_id) REFERENCES hbpc_shop_product
) 
GO
 
CREATE TABLE hbpc_shop_single_val_attr (
    pf_id VARCHAR(100) NOT NULL, 
    sva_name    VARCHAR(100) NOT NULL,
    sva_value   VARCHAR(100) NOT NULL,
    PRIMARY KEY (pf_id, sva_name, sva_value),
    FOREIGN KEY (pf_id) REFERENCES hbpc_shop_product
 ) 
GO

CREATE TABLE hbpc_shop_promo_price(
    promo_name          VARCHAR(100) NOT NULL,
    promo_type          INT NOT NULL,
    promo_description   VARCHAR(255) NULL,
    promo_rank          INT NULL,
    active              INT NULL,
    date_start          DATETIME NULL,
    date_end            DATETIME NULL,
    shopper_all         INT NULL ,
    shopper_column      VARCHAR(64) NULL,
    shopper_op          VARCHAR(2) NULL,
    shopper_value       VARCHAR(64) NULL,
    cond_all            INT NULL,
    cond_column         VARCHAR(64) NULL,
    cond_op             VARCHAR(2) NULL,
    cond_value          VARCHAR(64) NULL,
    cond_basis          CHAR(1) NULL,
    cond_min            INT NULL, 
    award_all           INT NULL,
    award_column        VARCHAR(64) NULL,
    award_op            VARCHAR(2) NULL,
    award_value         VARCHAR(64) NULL,
    award_max           INT NULL,
    disjoint_cond_award INT null,
    disc_type           CHAR(1) NULL,
    disc_value          REAL NULL,
    PRIMARY KEY (promo_name)
)
GO

CREATE TABLE hbpc_shop_promo_cross(
    pf_id VARCHAR(100) NOT NULL,
    rel_pf_id VARCHAR(100) NOT NULL,
    PRIMARY KEY (pf_id, rel_pf_id),
    FOREIGN KEY (pf_id) REFERENCES hbpc_shop_product,
    FOREIGN KEY (rel_pf_id) REFERENCES hbpc_shop_product(pf_id)
) 
GO

CREATE TABLE hbpc_shop_basket(
    shopper_id        CHAR(32) NOT NULL,
    date_changed      DATETIME NOT NULL,
    marshalled_basket IMAGE NOT NULL,
    PRIMARY KEY (shopper_id)
) 
GO

CREATE TABLE hbpc_shop_shopper(
    shopper_id   CHAR(32) NOT NULL,
    date_created DATETIME NOT NULL,
    name         VARCHAR(100) NULL,
    password     VARCHAR(100) NULL,
    address1     VARCHAR(100) NULL,
    address2     VARCHAR(100) NULL,
    address3     VARCHAR(100) NULL,
    address4     VARCHAR(100) NULL,
    country      VARCHAR(100) NULL,  
    phone        VARCHAR(100) NULL,  
    email        VARCHAR(100) UNIQUE NOT NULL,
    PRIMARY KEY (shopper_id)
) 
GO 

CREATE TABLE hbpc_shop_receipt (
    order_id     CHAR(26) NOT NULL,
    shopper_id   CHAR(32)  NOT NULL,
    total        INT NULL,
    status       INT NULL,
    date_entered DATETIME NULL,
    date_changed DATETIME NULL,
    marshalled_receipt IMAGE NULL,
    PRIMARY KEY (order_id)
) 
GO

CREATE TABLE hbpc_shop_receipt_item(
    order_id       CHAR(26) NOT NULL,
    row_id         INT NOT NULL,
    sku            VARCHAR(100) NULL,
    quantity       INT NOT NULL,
    adjusted_price INT NOT NULL,
    PRIMARY KEY (order_id, row_id)
) 
GO