MicroCommunity/java110-db/db/CommunityService/create_table.sql

-- community 表 过程表

CREATE TABLE business_community(
    community_id VARCHAR(30) NOT NULL COMMENT '小区ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    `name` VARCHAR(100) NOT NULL COMMENT '小区名称',
    address VARCHAR(200) NOT NULL COMMENT '小区地址',
    city_code varchar(12) not null comment '根据定位获取城市编码',
    nearby_landmarks VARCHAR(200) COMMENT '地标，如王府井北60米',
    map_x VARCHAR(20) NOT NULL COMMENT '地区 x坐标',
    map_y VARCHAR(20) NOT NULL COMMENT '地区 y坐标',
    `month` INT NOT NULL COMMENT '月份',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    operate VARCHAR(3) NOT NULL COMMENT '数据状态，添加ADD，修改MOD 删除DEL'
)
PARTITION BY RANGE (`month`) (
    PARTITION business_community_1 VALUES LESS THAN (2),
    PARTITION business_community_2 VALUES LESS THAN (3),
    PARTITION business_community_3 VALUES LESS THAN (4),
    PARTITION business_community_4 VALUES LESS THAN (5),
    PARTITION business_community_5 VALUES LESS THAN (6),
    PARTITION business_community_6 VALUES LESS THAN (7),
    PARTITION business_community_7 VALUES LESS THAN (8),
    PARTITION business_community_8 VALUES LESS THAN (9),
    PARTITION business_community_9 VALUES LESS THAN (10),
    PARTITION business_community_10 VALUES LESS THAN (11),
    PARTITION business_community_11 VALUES LESS THAN (12),
    PARTITION business_community_12 VALUES LESS THAN (13)
);
CREATE INDEX idx_business_community_id ON business_community(community_id);
CREATE INDEX idx_business_community_b_id ON business_community(b_id);

create table business_community_attr(
    b_id VARCHAR(30) NOT NULL COMMENT '订单ID',
    attr_id VARCHAR(30) NOT NULL COMMENT '属性id',
    community_id VARCHAR(30) NOT NULL COMMENT '用户ID',
    spec_cd VARCHAR(12) NOT NULL COMMENT '规格id,参考spec表',
    value VARCHAR(50) NOT NULL COMMENT '属性值',
    `month` INT NOT NULL comment '月份',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    operate VARCHAR(3) NOT NULL COMMENT '数据状态，添加ADD，修改MOD 删除DEL'
)
partition BY RANGE (`month`) (
    partition business_community_attr_1 VALUES LESS THAN (2),
    partition business_community_attr_2 VALUES LESS THAN (3),
    partition business_community_attr_3 VALUES LESS THAN (4),
    partition business_community_attr_4 VALUES LESS THAN (5),
    partition business_community_attr_5 VALUES LESS THAN (6),
    partition business_community_attr_6 VALUES LESS THAN (7),
    partition business_community_attr_7 VALUES LESS THAN (8),
    partition business_community_attr_8 VALUES LESS THAN (9),
    partition business_community_attr_9 VALUES LESS THAN (10),
    partition business_community_attr_10 VALUES LESS THAN (11),
    partition business_community_attr_11 VALUES LESS THAN (12),
    partition business_community_attr_12 VALUES LESS THAN (13)
);
CREATE INDEX idx_business_attr_community_id ON business_community_attr(community_id);
CREATE INDEX idx_business_community_attr_b_id ON business_community_attr(b_id);

-- 小区照片
CREATE TABLE business_community_photo(
    community_photo_id VARCHAR(30) NOT NULL COMMENT '商户照片ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    community_id VARCHAR(30) NOT NULL COMMENT '小区ID',
    community_photo_type_cd VARCHAR(12) NOT NULL COMMENT '商户照片类型,T 门头照 I 内景照',
    photo VARCHAR(100) NOT NULL COMMENT '照片',
    `month` INT NOT NULL COMMENT '月份',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    operate VARCHAR(3) NOT NULL COMMENT '数据状态，添加ADD，修改MOD 删除DEL'
)
PARTITION BY RANGE (`month`) (
    PARTITION business_community_photo_1 VALUES LESS THAN (2),
    PARTITION business_community_photo_2 VALUES LESS THAN (3),
    PARTITION business_community_photo_3 VALUES LESS THAN (4),
    PARTITION business_community_photo_4 VALUES LESS THAN (5),
    PARTITION business_community_photo_5 VALUES LESS THAN (6),
    PARTITION business_community_photo_6 VALUES LESS THAN (7),
    PARTITION business_community_photo_7 VALUES LESS THAN (8),
    PARTITION business_community_photo_8 VALUES LESS THAN (9),
    PARTITION business_community_photo_9 VALUES LESS THAN (10),
    PARTITION business_community_photo_10 VALUES LESS THAN (11),
    PARTITION business_community_photo_11 VALUES LESS THAN (12),
    PARTITION business_community_photo_12 VALUES LESS THAN (13)
);
CREATE INDEX idx_business_photo_community_id ON business_community_photo(community_id);
CREATE INDEX idx_business_community_photo_b_id ON business_community_photo(b_id);

-- 商户成员
create table business_community_member(
    community_member_id varchar(30) not null comment 'ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    community_id VARCHAR(30) NOT NULL COMMENT '小区ID',
    member_id varchar(50) not null  comment '成员ID',
    member_type_cd varchar(12) not null comment '成员类型见 community_member_type表',
    `month` INT NOT NULL comment '月份',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    operate VARCHAR(3) NOT NULL COMMENT '数据状态，添加ADD，修改MOD 删除DEL'
)
partition BY RANGE (`month`) (
    partition business_community_member_1 VALUES LESS THAN (2),
    partition business_community_member_2 VALUES LESS THAN (3),
    partition business_community_member_3 VALUES LESS THAN (4),
    partition business_community_member_4 VALUES LESS THAN (5),
    partition business_community_member_5 VALUES LESS THAN (6),
    partition business_community_member_6 VALUES LESS THAN (7),
    partition business_community_member_7 VALUES LESS THAN (8),
    partition business_community_member_8 VALUES LESS THAN (9),
    partition business_community_member_9 VALUES LESS THAN (10),
    partition business_community_member_10 VALUES LESS THAN (11),
    partition business_community_member_11 VALUES LESS THAN (12),
    partition business_community_member_12 VALUES LESS THAN (13)
);
CREATE INDEX idx_business_member_community_id ON business_community_member(community_id);
CREATE INDEX idx_business_community_member_b_id ON business_community_member(b_id);



CREATE TABLE s_community(
    community_id VARCHAR(30) NOT NULL COMMENT '小区ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    `name` VARCHAR(100) NOT NULL COMMENT '小区名称',
    address VARCHAR(200) NOT NULL COMMENT '小区地址',
    nearby_landmarks VARCHAR(200) COMMENT '地标，如王府井北60米',
    city_code varchar(12) not null comment '根据定位获取城市编码',
    map_x VARCHAR(20) NOT NULL COMMENT '地区 x坐标',
    map_y VARCHAR(20) NOT NULL COMMENT '地区 y坐标',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，S 保存，0, 在用 1失效',
    UNIQUE KEY (community_id)
);
CREATE INDEX idx_community_b_id ON s_community(b_id);
CREATE UNIQUE INDEX idx_community_id ON s_community(community_id);

CREATE TABLE s_community_attr(
    b_id VARCHAR(30) NOT NULL COMMENT '订单ID',
    attr_id VARCHAR(30) NOT NULL COMMENT '属性id',
    community_id VARCHAR(30) NOT NULL COMMENT '用户ID',
    spec_cd VARCHAR(12) NOT NULL COMMENT '规格id,参考spec表',
    VALUE VARCHAR(50) NOT NULL COMMENT '属性值',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，S 保存，0, 在用 1失效',
    UNIQUE KEY (attr_id)
);
CREATE INDEX idx_community_attr_b_id ON s_community_attr(b_id);
CREATE INDEX idx_attr_community_id ON s_community_attr(community_id);

-- 小区照片
CREATE TABLE s_community_photo(
    community_photo_id VARCHAR(30) NOT NULL COMMENT '商户照片ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    community_id VARCHAR(30) NOT NULL COMMENT '小区ID',
    community_photo_type_cd VARCHAR(12) NOT NULL COMMENT '商户照片类型,T 门头照 I 内景照',
    photo VARCHAR(100) NOT NULL COMMENT '照片',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态，详细参考c_status表，S 保存，0, 在用 1失效',
    UNIQUE KEY (community_photo_id)
);
CREATE INDEX idx_community_photo_b_id ON s_community_photo(b_id);
CREATE INDEX idx_community_photo_community_id ON s_community_photo(community_id);
CREATE INDEX idx_community_photo_community_photo_id ON s_community_photo(community_photo_id);


-- 商户成员
create table s_community_member(
    community_member_id varchar(30) not null comment 'ID',
    b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
    community_id VARCHAR(30) NOT NULL COMMENT '小区ID',
    member_id varchar(50) not null  comment '成员ID',
    member_type_cd varchar(12) not null comment '成员类型见 community_member_type表',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    status_cd VARCHAR(2) NOT NULL default '0' COMMENT '数据状态，详细参考c_status表，S 保存，0, 在用 1失效',
    unique KEY (community_member_id)
);
CREATE INDEX idx_s_community_member_id ON s_community_member(community_id);
CREATE INDEX idx_s_community_member_b_id ON s_community_member(b_id);

create table  community_member_type(
    id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
    member_type_cd VARCHAR(12) NOT NULL UNIQUE COMMENT '编码',
    `name` VARCHAR(50) NOT NULL COMMENT '名称',
    description VARCHAR(200) COMMENT '描述',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
);