createschemamysql.sql

基于struts框架的web开发应用实例

CREATE DATABASE STOREFRONT;

USE STOREFRONT;

CREATE TABLE STOREFRONT_ADDRESS_BOOK (
  PROFILE_ID       NUMERIC (30)   NOT NULL,
  USER_ID          NUMERIC (30),
  PROFILE_PRIMARY  CHAR (5),
  KEY XIF463STOREFRONT_ADDRESS_BOOK (USER_ID),
  PRIMARY KEY ( PROFILE_ID )
);


CREATE TABLE STOREFRONT_ORDER (
  ORDER_ID               NUMERIC (30)   NOT NULL,
  ORIGINATOR_PROFILE_ID  NUMERIC (30),
  BILLING_PROFILE_ID     NUMERIC (30),
  SHIPPING_PROFILE_ID    NUMERIC (30),
  STATUS_ID              NUMERIC (30)   NOT NULL,
  PLACEMENT_DATE         VARCHAR (50)  NOT NULL,
  TAX_PRICE              NUMERIC (12,2) NOT NULL,
  SHIPPING_PRICE         NUMERIC (12,2) NOT NULL,
  SUBTOTAL               NUMERIC (12,2) NOT NULL,
  TOTAL                  NUMERIC (12,2) NOT NULL,
  PRIMARY KEY ( ORDER_ID )
);


CREATE TABLE STOREFRONT_ORDER_ITEM (
  ORDER_PRODUCT_ID  NUMERIC (30)   NOT NULL,
  PRODUCT_ID        NUMERIC (30)   NOT NULL,
  ORDER_ID          NUMERIC (30)   NOT NULL,
  QUANTITY          NUMERIC (30)   NOT NULL,
  PRODUCT_PRICE     NUMERIC (12,2) NOT NULL,
  DESCRIPTION       VARCHAR (255)  NOT NULL,
  LINE_NUMBER       INTEGER,
  KEY XIF460STOREFRONT_ORDER_ITEM (PRODUCT_ID),
  PRIMARY KEY ( ORDER_PRODUCT_ID )
);


CREATE TABLE STOREFRONT_PROFILE (
  PROFILE_ID         NUMERIC (30)   NOT NULL,
  TITLE              VARCHAR (30),
  L_NAME             VARCHAR (30),
  F_NAME             VARCHAR (30),
  MIDDLE_NAME        VARCHAR (30),
  COMPANY            VARCHAR (80),
  PHONE_1            VARCHAR (30),
  ADDRESS_1          VARCHAR (50)  NOT NULL,
  ADDRESS_2          VARCHAR (50),
  CITY               VARCHAR (30)  NOT NULL,
  STATE_CODE         VARCHAR (30)  NOT NULL,
  COUNTRY            VARCHAR (30)  NOT NULL,
  ZIP                VARCHAR (30)  NOT NULL,
  EMAIL_1            VARCHAR (255),
  LANGUAGE_PREF      VARCHAR (10),
  PRIMARY KEY ( PROFILE_ID )
);


CREATE TABLE STOREFRONT_SEQUENCE_GENERATOR (
  SEQUENCE_NAME  VARCHAR (30)  NOT NULL,
  CURRENT_VALUE  NUMERIC (30)   NOT NULL,
  PRIMARY KEY ( SEQUENCE_NAME )
);


CREATE TABLE STOREFRONT_USER (
  USER_ID   NUMERIC (30)   NOT NULL,
  LOGIN_ID  VARCHAR (30)  NOT NULL,
  PASSWORD  VARCHAR (15)  NOT NULL,
  KEY XAK1STOREFRONT_USER (LOGIN_ID),
  PRIMARY KEY ( USER_ID )
);


CREATE TABLE STOREFRONT_ARTIST (
  ARTIST_ID    NUMERIC (30)   NOT NULL,
  ARTIST_NAME  VARCHAR (255)  NOT NULL,
  KEY XIE1STOREFRONT_ARTIST (ARTIST_NAME),
  PRIMARY KEY ( ARTIST_ID )
);


CREATE TABLE STOREFRONT_GENRE (
  GENRE_ID  NUMERIC (30)   NOT NULL,
  NAME      VARCHAR (255)  NOT NULL,
  PRIMARY KEY ( GENRE_ID )
);

CREATE TABLE STOREFRONT_ALBUM (
  PRODUCT_ID       NUMERIC (30)   NOT NULL,
  GENRE_ID         NUMERIC (30)   NOT NULL,
  ARTIST_ID        NUMERIC (30)   NOT NULL,
  NAME             VARCHAR (255)  NOT NULL,
  LARGE_IMAGE_URL  VARCHAR (255),
  SMALL_IMAGE_URL  VARCHAR (255),
  LABEL            VARCHAR (255)  NOT NULL,
  KEY XIE1STOREFRONT_ALBUM (NAME, PRODUCT_ID),
  KEY XIF265STOREFRONT_ALBUM (ARTIST_ID),
  KEY XIF270STOREFRONT_ALBUM (GENRE_ID),
  PRIMARY KEY ( PRODUCT_ID )
);


CREATE TABLE STOREFRONT_PRODUCT (
  PRODUCT_ID                    NUMERIC (30)   NOT NULL,
  SKU                           VARCHAR (30)  NOT NULL,
  DESCRIPTION                   VARCHAR (255),
  MSRP_PRICE                    NUMERIC (12,2),
  DERIVED_PRICE                 NUMERIC (12,2),
  DERIVED_SALES_PRICE           NUMERIC (12,2),
  NAME                          VARCHAR (255),
  PRIMARY KEY ( PRODUCT_ID )
);