Shopizer网站数据库设计

Database schema

The database schema information decribed in this section is divided into 5 sections

1. Merchant information
2. Product catalogue
3. Order information
4. Tax information
5. References tables

Merchant information

Shopizer supports multiple store fronts having different inventory. A store owner is defined as a merchant and its identifier is a merchant id (MerchantId). Most store related entity tables in the system contains a MerchantId column. A merchant identifier (merchantId) is created from MERCHANT_ID table every time a new store is created. A new MERCHANT_USER_INFO entity is created also for each new MerchantId created. MERCHANT_STORE entity table contains store related information for a given MerchantId. There can be many merchant user per MerchantId and one MerchantStore per MerchantId. Each MerchantUser can be given differnect access control role for a given MerchantStore. MERCHANT_REGISTRATION entity contains global registration of a merchant to the system such as registration codes and payment information if supported. MERCHANT_REGISTRATION_DEF (not used for now) defines payment fees per resistration code. MERCHANT_USER_ROLE_DEF is a reference table containing roles that can be assign to each MerchantUser. The table MERCHANT_USER_ROLE contains all roles assigned to a given MerchantUser. The administration name (login name) is the primary key for role assignation. An administration name needs to be unique in the system.

CENTRAL_GROUP entity contains Shopizer's main menu global function and CENTRAL_FUNCTION contains drop down menu items for a given menu group. The association group function is made in CENTRAL_REG_ASSOCIATION and a foreign key to MERCHANT_REGISTRATION_DEF_CODE let the system display or not menu items according to the Merchant registration code. By default the system is configured to desplay all groups and all functions. MERCHANT_CONFIGURATION table contains all cofigurations for Shopizer's subcomponents such as payment gatway information, shipping carrier, integration API credentials, tax settings etc…

Product catalogue

The following section describes the product catalogue schema tables which applies to the following concept:

A Product belongs to a Category. A Product can have one to many Prices. A Price can have 0 to one Special. Products can have Proterties defined as Product Options.

PRODUCT table contains product entity core attributes (name, price, image …) and the table PRODUCT_DESCRIPTION contains product description for a given language. REVIEWS and REVIEWS tables contains reviews description submited by the system registered customers / users. PRODUCT_TYPE table drives catalogue and shopping cart behaviors such as specific function for digital product types.

PRODUCTS_OPTIONS are additional information that can be added to a product core attributes for instance color, size… PRODUCT_OPTION table contains the selection types available radio, select, text and checkbox. PRODUCT_ATTRIBUTES table defines the PRODUCT_OPTION for a given PRODUCT, it contains related information such as a specific price increase or a weight increase. A PRODUCT_ATTRIBUTE can be read only which mean that this attribute is only displayed for information, by example this can be used to display product specifications. When read only is set to false, the attribute will be a selectable option when adding the item to the shopping cart.

A PRODUCT can have one to many prices. The PRODUCT price core attribute defines the base one time product price. A SPECIAL can be assigned to a product which will ajust base price. Other prices can be defined in PRODUCTS_PRICE, for instance an item can have an installation price and an activation price. All those prices can also have a special defined in PRODUCTS_PRICE_SPECIALS.

PRODUCT_RELATIONSHIP table contains an association between a product and another product. The relation contains an association type which can be used to display featured items, cross sell and up sell functionalities.

Install MySQL schema

You will need to create a database

instructions are in <SHOPIZER>\schema\readme.txt

Here they are :

1- Log in MySQL as root

mysql>CREATE DATABASE SALESMANAGER;
mysql>GRANT USAGE, SELECT ON *.* TO <YOUR USERNAME>@localhost IDENTIFIED BY '<YOUR PASSWORD>' with grant option;
mysql>grant all privileges on SALESMANAGER.* to <YOUR USERNAME>@localhost;
mysql>grant all privileges on SALESMANAGER.* to <YOUR USERNAME>@'%';
mysql>GRANT FILE ON *.* TO <YOUR USERNAME>@localhost;
mysql>GRANT FILE ON *.* TO <YOUR USERNAME>@'%';
mysql>FLUSH PRIVILEGES; 

edit <SHOPIZER>/sm-core/conf/properties/system.properties

#MySQL
database.driver=com.mysql.jdbc.Driver
database.jdbcUrl=jdbc:mysql://localhost:3306/SALESMANAGER
database.user=<YOUR USER NAME>
database.password=<YOUR PASSWORD>
hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

edit <SHOPIZER>\schema\build.properties

uncomment MySQL section and comment HSQLDB

#MySQL
db_port=3306
db_username=<YOUR USER NAME>
db_password=<YOUR PASSWORD>
salesmanager_db_name=SALESMANAGER

2) run <SHOPIZER>\schema\shopizer-build-mysql.bat

3) Change <SHOPIZER>/sm-core/conf/properties/systems.properties to use MySQL (make sure the other database configuration sections are commented)

#MySQL
database.driver=com.mysql.jdbc.Driver
database.jdbcUrl=jdbc:mysql://<HOST>:3306/SALESMANAGER
database.user=<YOUR USER>
database.password=<YOUR PASSORD>
hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

4) Shopizer will need a MySQL driver to connect to the database. You must download MySQL JDBC driver that matches your database version.

5) Rebuild sm-central and sm-shop web applications

Install Oracle schema

This procedure has been tested and works perfectly with Oracle APEX (Oracle Express / Oracle XE). The tests were done using version 10g. The procedure is described also in the source distribution in <SHOPIZER>\schema\readme.txt

1) Start Oracle XE administration web page and log in using SYS or SYSTEM account. Click on the administration arrow, click on database user arrow and then on create user. Username: salesmanager <or your own but will require changes in the properties file> Password: <PASSWORD>

2) The next step will be to build the schema and insert system data. The scripts for the schema creation task is located in shopizer-<SHOPIZER>\schema\sql\shopizer_oracle.sqland the all scripts in <SHOPIZER>\schema\sql\data for loading system data. Or you can execute <SHOPIZER>\schema\sql\shopizer_oracle.sql which includes every script to be run. It is recommended to uses the ANT tasks for doing this, if you have Ant installed, just set the database information in <SHOPIZER>\schema\build.properties. Uncomment Oracle connection database section and make sure the other database sectins are commented

#Oracle
db_port=1521
db_username=salesmanager or <YOUR USERNAME>
db_password=<YOUR PASSWORD>
salesmanager_db_name=XE

Then execute <SHOPIZER>\schema\shopizer-build-oracle.bat

3) Change <SHOPIZER>/sm-core/conf/properties/systems.properties to use Oracle (make sure the other database configuration sections are commented)

#Oracle
database.driver=oracle.jdbc.driver.OracleDriver
database.jdbcUrl=jdbc:oracle:thin:@<YOUR HOST>:1521:XE
database.user=salesmanager or <YOUR USERNAME>
database.password=<YOUR PASSWORD>
hibernate.dialect=org.hibernate.dialect.OracleDialect

4) Shopizer will need an Oracle thin driver to connect to the database. Your Oracle installation contains a jdbc driver you can use

ORACLE_HOME/jdbc/lib contains ojdbc14.jar that needs to be copied to <SHOPIZER>\sm-core\lib\misc

5) Rebuild sm-central and sm-shop web applications