cytx_api/sql/hospital.sql

-- 医院列表
CREATE TABLE `hospital` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL COMMENT '医院名称',
  `province_code` varchar(10) DEFAULT '' COMMENT '省份编码',
  `city_code` varchar(10) DEFAULT '' COMMENT '城市编码',
  `district_code` varchar(10) DEFAULT '' COMMENT '区县编码',
  `province_name` varchar(50) DEFAULT '' COMMENT '省份名称',
  `city_name` varchar(50) DEFAULT '' COMMENT '城市名称',
  `district_name` varchar(50) DEFAULT '' COMMENT '区县名称',
  `sort` int(11) DEFAULT 0 COMMENT '排序(越大越前)',
  `is_show` tinyint(1) DEFAULT 1 COMMENT '是否展示: 1是 0否',
  `is_deleted` tinyint(1) DEFAULT 0 COMMENT '软删除: 0正常 1已删除',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_region` (`province_code`, `city_code`, `district_code`)
) COMMENT='医院列表';

-- patient 表新增就诊医院省市区编码
ALTER TABLE `patient`
  ADD COLUMN `hospital_province_code` varchar(10) DEFAULT '' COMMENT '就诊医院省份编码' AFTER `hospital`,
  ADD COLUMN `hospital_city_code` varchar(10) DEFAULT '' COMMENT '就诊医院城市编码' AFTER `hospital_province_code`,
  ADD COLUMN `hospital_district_code` varchar(10) DEFAULT '' COMMENT '就诊医院区县编码' AFTER `hospital_city_code`;