URR8SDin

1CREATE TABLE IF NOT EXISTS `review` (
2  `review_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Review id',
3  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Review create date',
4  `entity_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity id',
5  `entity_pk_value` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Product id',
6  `status_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Status code',
7  PRIMARY KEY (`review_id`),
8  KEY `REVIEW_ENTITY_ID` (`entity_id`),
9  KEY `REVIEW_STATUS_ID` (`status_id`),
10  KEY `REVIEW_ENTITY_PK_VALUE` (`entity_pk_value`),
11  CONSTRAINT `REVIEW_ENTITY_ID_REVIEW_ENTITY_ENTITY_ID` FOREIGN KEY (`entity_id`) REFERENCES `review_entity` (`entity_id`) ON DELETE CASCADE,
12  CONSTRAINT `REVIEW_STATUS_ID_REVIEW_STATUS_STATUS_ID` FOREIGN KEY (`status_id`) REFERENCES `review_status` (`status_id`) ON DELETE NO ACTION
13) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Review base information';
14
15CREATE TABLE IF NOT EXISTS `review_detail` (
16  `detail_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Review detail id',
17  `review_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'Review id',
18  `store_id` smallint(5) unsigned DEFAULT '0' COMMENT 'Store id',
19  `title` varchar(255) NOT NULL COMMENT 'Title',
20  `detail` text NOT NULL COMMENT 'Detail description',
21  `nickname` varchar(128) NOT NULL COMMENT 'User nickname',
22  `customer_id` int(10) unsigned DEFAULT NULL COMMENT 'Customer Id',
23  PRIMARY KEY (`detail_id`),
24  KEY `REVIEW_DETAIL_REVIEW_ID` (`review_id`),
25  KEY `REVIEW_DETAIL_STORE_ID` (`store_id`),
26  KEY `REVIEW_DETAIL_CUSTOMER_ID` (`customer_id`),
27  CONSTRAINT `REVIEW_DETAIL_CUSTOMER_ID_CUSTOMER_ENTITY_ENTITY_ID` FOREIGN KEY (`customer_id`) REFERENCES `customer_entity` (`entity_id`) ON DELETE SET NULL,
28  CONSTRAINT `REVIEW_DETAIL_REVIEW_ID_REVIEW_REVIEW_ID` FOREIGN KEY (`review_id`) REFERENCES `review` (`review_id`) ON DELETE CASCADE,
29  CONSTRAINT `REVIEW_DETAIL_STORE_ID_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE SET NULL
30) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Review detail information';
31
32CREATE TABLE IF NOT EXISTS `review_entity` (
33  `entity_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Review entity id',
34  `entity_code` varchar(32) NOT NULL COMMENT 'Review entity code',
35  PRIMARY KEY (`entity_id`)
36) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='Review entities';
37
38CREATE TABLE IF NOT EXISTS `review_entity_summary` (
39  `primary_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Summary review entity id',
40  `entity_pk_value` bigint(20) NOT NULL DEFAULT '0' COMMENT 'Product id',
41  `entity_type` smallint(6) NOT NULL DEFAULT '0' COMMENT 'Entity type id',
42  `reviews_count` smallint(6) NOT NULL DEFAULT '0' COMMENT 'Qty of reviews',
43  `rating_summary` smallint(6) NOT NULL DEFAULT '0' COMMENT 'Summarized rating',
44  `store_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store id',
45  PRIMARY KEY (`primary_id`),
46  KEY `REVIEW_ENTITY_SUMMARY_STORE_ID` (`store_id`),
47  CONSTRAINT `REVIEW_ENTITY_SUMMARY_STORE_ID_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE CASCADE
48) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Review aggregates';
49
50CREATE TABLE IF NOT EXISTS `review_status` (
51  `status_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Status id',
52  `status_code` varchar(32) NOT NULL COMMENT 'Status code',
53  PRIMARY KEY (`status_id`)
54) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='Review statuses';
55
56CREATE TABLE IF NOT EXISTS `review_store` (
57  `review_id` bigint(20) unsigned NOT NULL COMMENT 'Review Id',
58  `store_id` smallint(5) unsigned NOT NULL COMMENT 'Store Id',
59  PRIMARY KEY (`review_id`,`store_id`),
60  KEY `REVIEW_STORE_STORE_ID` (`store_id`),
61  CONSTRAINT `REVIEW_STORE_REVIEW_ID_REVIEW_REVIEW_ID` FOREIGN KEY (`review_id`) REFERENCES `review` (`review_id`) ON DELETE CASCADE,
62  CONSTRAINT `REVIEW_STORE_STORE_ID_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE CASCADE
63) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Review Store';