기능(Feature) #366
closed기능(Feature) #287: Hana Coupon
[Hana Coupon] Design database for Hana Coupon
Added by Joseph Vo 10 months ago. Updated 9 months ago.
100%
Description
Request :
Design database for Hana Coupon
Updated by Dan Hoang 10 months ago
- % Done changed from 0 to 90
BS_COUPON_BUDGET
COUPON_BUDGET_SEQ: BIGINT IDENTITY
SALES_COUNTRY_CODE: VARCHAR NOT NULL (INDEX)
BUDGET_NAME_EN: VARCHAR NOT NULL
BUDGET_NAME_KO: VARCHAR NOT NULL
BUDGET_DESC: VARCHAR NULL
IN_CHARGE_DEPT_CODE: VARCHAR NOT NULL
SUPPORT_SALES_COMP_CODE_LIST: VARCHAR NULL COMMENT 'Issue for list specific Sellers'
ITEM_CATEGORY_CODE: varchar(10) DEFAULT NULL COMMENT 'Reserved Item Category Code: BasicBS003'
COUPON_SHARED_TRADER_COMP_CODE1: INT NULL
COUPON_SHARE_RATE_OR_AMOUNT1: VARCHAR NULL
COUPON_SHARE_APPLIED_VALUE2: DECIMAL NULL
COUPON_SHARED_TRADER_COMP_CODE2: INT NULL
COUPON_SHARE_RATE_OR_AMOUNT2: VARCHAR NULL
COUPON_SHARE_APPLIED_VALUE1: DECIMAL NULL
TOTAL_BUDGET_AMOUNT: DECIMAL NOT NULL
COUPON_RATE_OR_AMOUNT: VARCHAR NOT NULL
COUPON_APPLIED_VALUE: DECIMAL NOT NULL
MIN_ORDER_AMOUNT DECIMAL NOT NULL COMMENT 'Minimum order value to apply'
DRAFT_CODE: VARCHAR NULL COMMENT 'Prefix of coupon code'
USE_YN: VARCHAR NOT NULL DEFAULT 'N'
ISSUE_FROM_DATETIME: DATE NOT NULL (INDEX)
ISSUE_TO_DATETIME: DATE NOT NULL (INDEX)
USE_FROM_DATE: DATE NOT NULL
USE_TO_DATE: DATE NOT NULL
MAX_COUPON_COUNT: SMALLINT NOT NULL
MAX_USAGE_PER_COUPON: SMALLINT NOT NULL DEFAULT 1
FIRST_INSERT_UNO: BIGINT NOT NULL
FIRST_INSERT_DATETIME: DATETIME NOT NULL
LAST_UPDATE_UNO: BIGINT NOT NULL
LAST_UPDATE_DATETIME: DATETIME NOT NULL
BS_COUPON_ITEM
COUPON_ITEM_SEQ: BIGINT IDENTITY
COUPON_BUDGET_SEQ: BIGINT NOT NULL (INDEX)
COUPON_ITEM_CODE VARCHAR NOT NULL UNIQUE (INDEX) COMMENT 'Combine Value of DRAFT_CODE and random characters'
COUPON_NAME_EN: VARCHAR NOT NULL
COUPON_NAME_KO: VARCHAR NOT NULL
COUPON_NAME_JA: VARCHAR NULL
COUPON_NAME_VI: VARCHAR NULL
COUPON_NAME_ZH: VARCHAR NULL
COUPON_DESC: VARCHAR NULL
ITEM_DETAIL_CATEGORY_CODE_LIST: VARCHAR NULL COMMENT 'Reserved Item Detail of Category Code()'
MAX_ISSUE_ABLE_COUNT: SMALLINT NOT NULL DEFAULT 1 COMMENT 'Value is the same with value of MAX_USAGE_PER_COUPON in BS_COUPON_BUDGET table'
LEFT_COUPON_COUNT: SMALLINT NOT NULL DEFAULT 1 COMMENT 'Initial Value is the same with value of MAX_ISSUE_ABLE_COUNT'
FIRST_INSERT_UNO: BIGINT NOT NULL
FIRST_INSERT_DATETIME: DATETIME NOT NULL
LAST_UPDATE_UNO: BIGINT NOT NULL
LAST_UPDATE_DATETIME: DATETIME NOT NUL
PM_USED_COUPON
USED_COUPON_SEQ: BIGINT IDENTITY
USER_NO: BIGINT NULL (INDEX)
AFFILIATE_USER_NO: VARCHAR NULL (INDEX)
SALES_COMP_CODE: INT NOT NULL (INDEX)
BOOKING_CODE: VARCHAR NOT NULL (INDEX)
BOOKING_ITEM_CODE: VARCHAR NOT NULL (INDEX)
COUPON_BUDGET_SEQ: BIGINT NOT NULL
COUPON_ITEM_SEQ: BIGINT NOT NULL
COUPON_SHARED_TRADER_COMP_CODE1: INT NULL
COUPON_SHARE_RATE_OR_AMOUNT1: VARCHAR NULL
COUPON_SHARE_APPLIED_VALUE2: DECIMAL NULL
COUPON_SHARED_TRADER_COMP_CODE2: INT NULL
COUPON_SHARE_RATE_OR_AMOUNT2: VARCHAR NULL
COUPON_SHARE_APPLIED_VALUE1: DECIMAL NULL
COUPON_RATE_OR_AMOUNT: VARCHAR NOT NULL
COUPON_APPLIED_VALUE: DECIMAL NOT NULL
FIRST_INSERT_UNO: BIGINT NOT NULL
FIRST_INSERT_DATETIME: DATETIME NOT NULL
LAST_UPDATE_UNO: BIGINT NOT NULL
LAST_UPDATE_DATETIME: DATETIME NOT NUL
Updated by Dan Hoang 10 months ago
CREATE TABLE OMH_SUITE.BS_COUPON_BUDGET (
COUPON_BUDGET_SEQ bigint NOT NULL AUTO_INCREMENT,
SALES_COUNTRY_CODE varchar(9) NOT NULL,
BUDGET_NAME_EN varchar(100) NOT NULL,
BUDGET_NAME_KO varchar(100) NOT NULL,
BUDGET_DESC varchar(1000) DEFAULT NULL,
IN_CHARGE_DEPT_CODE varchar(10) NOT NULL,
SUPPORT_SALES_COMP_CODE_LIST varchar(1000) DEFAULT NULL COMMENT 'Issue for list specific Sellers',
ITEM_CATEGORY_CODE varchar(10) DEFAULT NULL COMMENT 'Reserved Item Category Code: BasicBS003',
COUPON_SHARED_TRADER_COMP_CODE1 int DEFAULT NULL,
COUPON_SHARE_RATE_OR_AMOUNT1 varchar(1) DEFAULT NULL,
COUPON_SHARE_APPLIED_VALUE2 decimal(15, 2) DEFAULT NULL,
COUPON_SHARED_TRADER_COMP_CODE2 int DEFAULT NULL,
COUPON_SHARE_RATE_OR_AMOUNT2 varchar(1) DEFAULT NULL,
COUPON_SHARE_APPLIED_VALUE1 decimal(15, 2) DEFAULT NULL,
TOTAL_BUDGET_AMOUNT decimal(15, 2) NOT NULL,
COUPON_RATE_OR_AMOUNT varchar(1) NOT NULL,
COUPON_APPLIED_VALUE decimal(15, 2) NOT NULL,
MIN_ORDER_AMOUNT decimal(15, 2) NOT NULL COMMENT 'Minimum order value to apply',
DRAFT_CODE varchar(20) DEFAULT NULL COMMENT 'Prefix of coupon code',
USE_YN varchar(1) NOT NULL DEFAULT 'N',
ISSUE_FROM_DATETIME date NOT NULL,
ISSUE_TO_DATETIME date NOT NULL,
USE_FROM_DATE date NOT NULL,
USE_TO_DATE date NOT NULL,
MAX_COUPON_COUNT smallint NOT NULL,
MAX_USAGE_PER_COUPON smallint NOT NULL DEFAULT 1,
FIRST_INSERT_UNO bigint NOT NULL,
FIRST_INSERT_DATETIME datetime NOT NULL,
LAST_UPDATE_UNO bigint NOT NULL,
LAST_UPDATE_DATETIME datetime NOT NULL,
PRIMARY KEY (COUPON_BUDGET_SEQ)
)
ENGINE = INNODB,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_unicode_ci;
ALTER TABLE OMH_SUITE.BS_COUPON_BUDGET
ADD INDEX IDX_COUPON_BUDGET_ISSUE_FROM_DATETIME (ISSUE_FROM_DATETIME);
ALTER TABLE OMH_SUITE.BS_COUPON_BUDGET
ADD INDEX IDX_COUPON_BUDGET_ISSUE_TO_DATETIME (ISSUE_TO_DATETIME);
ALTER TABLE OMH_SUITE.BS_COUPON_BUDGET
ADD INDEX IDX_COUPON_BUDGET_SALES_COUNTRY_CODE (SALES_COUNTRY_CODE);
ALTER TABLE OMH_SUITE.BS_COUPON_BUDGET
ADD INDEX IDX_COUPON_BUDGET_USE_FROM_DATE (USE_FROM_DATE);
ALTER TABLE OMH_SUITE.BS_COUPON_BUDGET
ADD INDEX IDX_COUPON_BUDGET_USE_TO_DATE (USE_TO_DATE);
CREATE TABLE OMH_SUITE.BS_COUPON_ITEM (
COUPON_ITEM_SEQ bigint NOT NULL AUTO_INCREMENT,
COUPON_BUDGET_SEQ bigint NOT NULL,
COUPON_ITEM_CODE varchar(20) NOT NULL COMMENT 'Combine Value of DRAFT_CODE and random characters',
COUPON_NAME_EN varchar(100) NOT NULL,
COUPON_NAME_KO varchar(100) NOT NULL,
COUPON_NAME_JA varchar(100) DEFAULT NULL,
COUPON_NAME_VI varchar(100) DEFAULT NULL,
COUPON_NAME_ZH varchar(100) DEFAULT NULL,
COUPON_DESC varchar(1000) DEFAULT NULL,
ITEM_DETAIL_CATEGORY_CODE_LIST varchar(100) DEFAULT NULL COMMENT 'Reserved Item Detail of Category Code()',
MAX_ISSUE_ABLE_COUNT smallint NOT NULL DEFAULT 1 COMMENT 'Value is the same with value of MAX_USAGE_PER_COUPON in BS_COUPON_BUDGET table',
LEFT_COUPON_COUNT smallint NOT NULL DEFAULT 1 COMMENT 'Initial Value is the same with value of MAX_ISSUE_ABLE_COUNT',
FIRST_INSERT_UNO bigint NOT NULL,
FIRST_INSERT_DATETIME datetime NOT NULL,
LAST_UPDATE_UNO bigint NOT NULL,
LAST_UPDATE_DATETIME datetime NOT NULL,
PRIMARY KEY (COUPON_ITEM_SEQ)
)
ENGINE = INNODB,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_unicode_ci;
ALTER TABLE OMH_SUITE.BS_COUPON_ITEM
ADD INDEX IDX_COUPON_BUDGET_SEQ (COUPON_BUDGET_SEQ);
ALTER TABLE OMH_SUITE.BS_COUPON_ITEM
ADD UNIQUE INDEX UQ_COUPON_ITEM_CODE (COUPON_ITEM_CODE);
CREATE TABLE OMH_SUITE.PM_USED_COUPON (
USED_COUPON_SEQ bigint NOT NULL AUTO_INCREMENT,
USER_NO bigint DEFAULT NULL,
AFFILIATE_USER_NO varchar(500) DEFAULT NULL,
SALES_COMP_CODE int NOT NULL,
BOOKING_CODE varchar(20) NOT NULL,
BOOKING_ITEM_CODE varchar(20) NOT NULL,
COUPON_ITEM_SEQ bigint NOT NULL,
COUPON_SHARED_TRADER_COMP_CODE1 int DEFAULT NULL,
COUPON_SHARE_RATE_OR_AMOUNT1 varchar(1) DEFAULT NULL,
COUPON_SHARE_APPLIED_VALUE2 decimal(15, 2) DEFAULT NULL,
COUPON_SHARED_TRADER_COMP_CODE2 int DEFAULT NULL,
COUPON_SHARE_RATE_OR_AMOUNT2 varchar(1) DEFAULT NULL,
COUPON_SHARE_APPLIED_VALUE1 decimal(15, 2) DEFAULT NULL,
COUPON_RATE_OR_AMOUNT varchar(1) NOT NULL,
COUPON_APPLIED_VALUE decimal(15, 2) NOT NULL,
FIRST_INSERT_UNO bigint NOT NULL,
FIRST_INSERT_DATETIME datetime NOT NULL,
LAST_UPDATE_UNO bigint NOT NULL,
LAST_UPDATE_DATETIME datetime NOT NULL,
PRIMARY KEY (USED_COUPON_SEQ)
)
ENGINE = INNODB,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_unicode_ci;
ALTER TABLE OMH_SUITE.PM_USED_COUPON
ADD INDEX IDX_AFFILIATE_USER_NO (AFFILIATE_USER_NO);
ALTER TABLE OMH_SUITE.PM_USED_COUPON
ADD INDEX IDX_BOOKING_CODE (BOOKING_CODE);
ALTER TABLE OMH_SUITE.PM_USED_COUPON
ADD INDEX IDX_BOOKING_ITEM_CODE (BOOKING_ITEM_CODE);
ALTER TABLE OMH_SUITE.PM_USED_COUPON
ADD INDEX IDX_COUPON_ITEM_SEQ (COUPON_ITEM_SEQ);
ALTER TABLE OMH_SUITE.PM_USED_COUPON
ADD INDEX IDX_SALES_COMP_CODE (SALES_COMP_CODE);
ALTER TABLE OMH_SUITE.PM_USED_COUPON
ADD INDEX IDX_USER_NO (USER_NO);
Updated by ziniy Kang 9 months ago
- Status changed from 완료(Done) to 완료성공(Resolve)
- Target version set to v1.0.0