-- 人员信息表 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='项目表';