| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132 |
- -- 人员信息表
- CREATE TABLE person_info (
- id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
- realname VARCHAR(50) NOT NULL COMMENT '姓名',
- id_card VARCHAR(18) UNIQUE COMMENT '身份证号',
- ethnic VARCHAR(30) COMMENT '民族',
- age TINYINT COMMENT '年龄',
- gender TINYINT NOT NULL COMMENT '性别: 1-男, 2-女, 0-未知',
- birth_date DATE COMMENT '出生日期',
- population_category TINYINT COMMENT '人口类别：0-本村户籍 ，1-非本村户籍',
- phone VARCHAR(20) COMMENT '电话',
- population_tags VARCHAR(1024) COMMENT '人口标签(标签 json 列表)',
- occupation_id VARCHAR(50) COMMENT '职业ID',
- village_id INT COMMENT '所属村庄ID',
- grid_id INT COMMENT '所属网格ID',
- education_level VARCHAR(20) COMMENT '文化程度',
- marital_status TINYINT COMMENT '婚姻状况: 1-未婚, 2-已婚, 3-丧偶, 4-离婚',
- political_status VARCHAR(20) COMMENT '政治面貌',
- religion VARCHAR(30) COMMENT '宗教信仰',
- photo_url VARCHAR(255) COMMENT '照片URL',
- household_id INT COMMENT '所属户籍ID',
- household_relation VARCHAR(30) COMMENT '户籍关系',
- current_address VARCHAR(200) COMMENT '现居地',
- special_identity_code VARCHAR(20) COMMENT '特殊身份编号',
- special_identity VARCHAR(100) COMMENT '特殊身份',
- create_time datetime null comment '创建时间',
- create_by varchar(64) null comment '创建人',
- update_time datetime null comment '修改时间',
- update_by varchar(64) null comment '修改人',
- del_flag char default '0' null comment '删除标志',
- INDEX idx_realname (realname),
- INDEX idx_ethnic (ethnic),
- INDEX idx_id_card (id_card),
- INDEX idx_grid (grid_id),
- INDEX idx_household (household_id),
- INDEX idx_special_identity (special_identity)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='人员信息表';
- -- 户籍信息表
- CREATE TABLE household_info (
- id INT PRIMARY KEY AUTO_INCREMENT COMMENT '户籍ID',
- household_code VARCHAR(50) UNIQUE NOT NULL COMMENT '户籍编号',
- household_head VARCHAR(50) NOT NULL COMMENT '户主姓名',
- household_head_id_card VARCHAR(18) COMMENT '户主身份证号',
- household_type TINYINT NOT NULL COMMENT '户籍类型: 1-农业户口, 2-非农业户口',
- village_id INT COMMENT '所属行政村ID',
- household_address VARCHAR(200) COMMENT '户籍地址',
- belonging_area VARCHAR(200) COMMENT '归属地区',
- house_code VARCHAR(50) COMMENT '房屋编号',
- create_time datetime null comment '创建时间',
- update_time datetime null comment '修改时间',
- del_flag char default '0' null comment '删除标志'
- INDEX idx_household_number (household_code),
- INDEX idx_household_head (household_head),
- INDEX idx_house_number (house_number)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='户籍信息表';
- -- 房屋信息表
- CREATE TABLE house_info (
- id INT PRIMARY KEY AUTO_INCREMENT COMMENT '房屋ID',
- house_code VARCHAR(20) UNIQUE NOT NULL COMMENT '房屋编号(如FW0000580689)',
- village_id INT COMMENT '所属行政村ID',
- village_name VARCHAR(100) COMMENT '所属行政村名称',
- doorplate_number VARCHAR(50) COMMENT '门牌号',
- house_address VARCHAR(200) COMMENT '房屋详细地址',
- house_owner_id INT COMMENT '房主人员ID',
- total_points INT DEFAULT 0 COMMENT '总积分',
- remaining_points INT DEFAULT 0 COMMENT '剩余积分',
- house_type TINYINT COMMENT '房屋性质:1-自建房,2-商铺,3-公租房',
- house_category TINYINT COMMENT '房屋类别:1-修缮,2-原址重建,3-集中安置',
- building_area DECIMAL(10,2) COMMENT '宅基地面积(㎡)',
- grid_id INT COMMENT '所属网格ID',
- photo_url VARCHAR(256) COMMENT '照片URL',
- house_tags VARCHAR(1024) COMMENT '家庭标签(JSON数组)',
- qr_code_url VARCHAR(256) COMMENT '门牌二维码图片URL',
- create_time datetime null comment '创建时间',
- update_time datetime null comment '修改时间',
- del_flag char default '0' null comment '删除标志',
- -- 索引
- INDEX idx_house_code (house_code),
- INDEX idx_village_grid (village_id, grid_id),
- INDEX idx_doorplate (doorplate_number),
- INDEX idx_owner (house_owner_id),
- INDEX idx_house_type (house_type),
- INDEX idx_house_category (house_category),
- INDEX idx_address (house_address)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='房屋信息表';
- -- 网格信息表
- CREATE TABLE grids (
- grid_id INT PRIMARY KEY AUTO_INCREMENT,
- grid_code VARCHAR(20) UNIQUE NOT NULL COMMENT '网格编码',
- grid_name VARCHAR(100) NOT NULL COMMENT '网格名称',
- grid_level TINYINT NOT NULL COMMENT '网格级别(1-一级,2-二级,3-三级...)',
- parent_id INT COMMENT '上级网格ID',
- full_path VARCHAR(255) COMMENT '完整路径(格式:1,12,123)',
- village_id INT COMMENT '所属行政村ID',
- manager_id INT COMMENT '网格管理员ID',
- manager_name VARCHAR(50) COMMENT '网格管理员姓名',
- manager_phone VARCHAR(20) COMMENT '管理员电话',
- area_size DECIMAL(10,2) COMMENT '面积(平方公里)',
- household_count INT DEFAULT 0 COMMENT '户数',
- population_count INT DEFAULT 0 COMMENT '人口数',
- description VARCHAR(200) COMMENT '描述',
- status TINYINT DEFAULT 1 COMMENT '状态:1-启用,0-停用',
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
- updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- FOREIGN KEY (parent_id) REFERENCES grids(grid_id),
- FOREIGN KEY (village_id) REFERENCES villages(village_id),
- INDEX idx_level (grid_level),
- INDEX idx_parent (parent_id),
- INDEX idx_village (village_id),
- INDEX idx_path (full_path(20))
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='网格信息表';
- -- 项目表
- CREATE TABLE projects (
- id INT PRIMARY KEY AUTO_INCREMENT COMMENT '项目ID',
- project_name VARCHAR(100) NOT NULL COMMENT '项目名称',
- project_description VARCHAR(256) COMMENT '项目内容',
- investment_amount DECIMAL(15,2) COMMENT '投资金额(元)',
- start_date DATE COMMENT '项目开始日期',
- estimated_completion_date DATE COMMENT '预计建成日期',
- actual_completion_date DATE COMMENT '实际建成日期',
- progress TINYINT DEFAULT 0 COMMENT '项目进度(0-建设中 1-已建成)',
- create_time datetime null comment '创建时间',
- update_time datetime null comment '修改时间',
- del_flag char default '0' null comment '删除标志',
- INDEX idx_progress (progress),
- INDEX idx_name (project_name),
- INDEX idx_dates (start_date, estimated_completion_date)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='项目表';
|
