代码拉取完成，页面将自动刷新
create table public.customer
(
id character varying(36) primary key not null default (gen_random_uuid())::text,
name character varying(255), -- 姓名
id_card character varying(255), -- 身份证
id_card_face text, -- 身份证A面
id_card_national text, -- 身份证B面
face text, -- 人脸
phone character varying(255), -- 手机号
session_key character varying(50), -- 小程序SESSION_KEY
appid character varying(50), -- 微信支付应用ID
openid character varying(50), -- 用户在微信支付APPID下的唯一标识
alipay_appid character varying(50), -- 支付宝应用ID
alipay_openid character varying(50), -- 支付宝用户在APPID下的唯一标识
username character varying(50), -- 登录账户
password character varying(255), -- 密码
insert_datetime timestamp(0) without time zone default now(), -- 创建时间
update_datetime timestamp(0) without time zone default now() -- 更新时间
);
create unique index uk_customer_username on customer using btree (username);
comment on table public.customer is '营业员';
comment on column public.customer.name is '姓名';
comment on column public.customer.id_card is '身份证';
comment on column public.customer.id_card_face is '身份证A面';
comment on column public.customer.id_card_national is '身份证B面';
comment on column public.customer.face is '人脸';
comment on column public.customer.phone is '手机号';
comment on column public.customer.session_key is '小程序SESSION_KEY';
comment on column public.customer.appid is '微信支付应用ID';
comment on column public.customer.openid is '用户在微信支付APPID下的唯一标识';
comment on column public.customer.alipay_appid is '支付宝应用ID';
comment on column public.customer.alipay_openid is '支付宝用户在APPID下的唯一标识';
comment on column public.customer.username is '登录账户';
comment on column public.customer.password is '密码';
comment on column public.customer.insert_datetime is '创建时间';
comment on column public.customer.update_datetime is '更新时间';
create table public.merchant
(
id character varying(36) primary key not null default (gen_random_uuid())::text,
category character varying(20) not null, -- 分类
shortname character varying(20) not null, -- 简称
password character varying(255) not null, -- 主体密码
trademark text, -- LOGO
open_time time without time zone not null, -- 开门时间
close_time time without time zone not null, -- 关闭时间
description character varying(255), -- 主体介绍
pictures text[], -- 图集
videos text[], -- 视频ID
status boolean default false, -- 是否营业
customer_id character varying(36) not null, -- 营业员ID
username character varying(20) not null, -- 联系人
phone character varying(11) not null, -- 联系方式
location point not null, -- 坐标
postcode character(6) not null, -- 邮编
address_value text[] not null, -- 省市区
address_code text[] not null, -- 省市区编号
address_detail character varying(50) not null, -- 详细地址
address_name character varying(50) not null, -- 地址名称
sub_mch_id character varying(50), -- 特约主体编号
seller_id character varying(50), -- 卖家支付宝用户ID
insert_datetime timestamp(0) without time zone default now(), -- 创建时间
update_datetime timestamp(0) without time zone default now() -- 更新时间
);
comment on table public.merchant is '主体';
comment on column public.merchant.category is '分类';
comment on column public.merchant.shortname is '简称';
comment on column public.merchant.password is '主体密码';
comment on column public.merchant.trademark is 'LOGO';
comment on column public.merchant.open_time is '开门时间';
comment on column public.merchant.close_time is '关闭时间';
comment on column public.merchant.description is '主体介绍';
comment on column public.merchant.pictures is '图集';
comment on column public.merchant.videos is '视频ID';
comment on column public.merchant.status is '是否营业';
comment on column public.merchant.customer_id is '营业员ID';
comment on column public.merchant.username is '联系人';
comment on column public.merchant.phone is '联系方式';
comment on column public.merchant.location is '坐标';
comment on column public.merchant.postcode is '邮编';
comment on column public.merchant.address_value is '省市区';
comment on column public.merchant.address_code is '省市区编号';
comment on column public.merchant.address_detail is '详细地址';
comment on column public.merchant.address_name is '地址名称';
comment on column public.merchant.sub_mch_id is '特约主体编号';
comment on column public.merchant.seller_id is '卖家支付宝用户ID';
comment on column public.merchant.insert_datetime is '创建时间';
comment on column public.merchant.update_datetime is '更新时间';
create table public.product
(
id character varying(36) primary key not null default (gen_random_uuid())::text,
price_unit character varying(15) not null, -- 单价单位
name character varying(90) not null, -- 商品名称
merchant_id character varying(36) not null, -- 商户ID
status boolean not null default false, -- 是否上架
price numeric not null default 0.00, -- 单价
stock integer not null default 0, -- 库存
max integer not null default 1, -- 最大购买量
min integer not null default 1, -- 最小购买量
vid character varying(50), -- 视频ID
description character varying(255), -- 商品描述
label character varying(50), -- 商品标签
order_label character varying(50), -- 标签排序
pictures text[], -- 商品图集
insert_datetime timestamp(0) without time zone default now(), -- 创建时间
update_datetime timestamp(0) without time zone default now(), -- 更新时间
start_datetime timestamp(0) without time zone default now(),
end_datetime timestamp(0) without time zone default now(),
foreign key (merchant_id) references public.merchant (id)
match simple on update no action on delete cascade
);
create index i_product_merchant_id on product using btree (merchant_id);
comment on table public.product is '商品';
comment on column public.product.price_unit is '单价单位';
comment on column public.product.name is '商品名称';
comment on column public.product.merchant_id is '商户ID';
comment on column public.product.status is '是否上架';
comment on column public.product.price is '单价';
comment on column public.product.stock is '库存';
comment on column public.product.max is '最大购买量';
comment on column public.product.min is '最小购买量';
comment on column public.product.vid is '视频ID';
comment on column public.product.description is '商品描述';
comment on column public.product.label is '商品标签';
comment on column public.product.order_label is '标签排序';
comment on column public.product.pictures is '商品图集';
comment on column public.product.insert_datetime is '创建时间';
comment on column public.product.update_datetime is '更新时间';
create table public.address
(
id character varying(36) primary key not null default (gen_random_uuid())::text,
customer_id character varying(36) not null, -- 消费者ID
location point not null, -- 坐标
type character varying(50) not null, -- 类型
name character varying(50) not null, -- 地址名称
detail character varying(50) not null, -- 地址详情
postcode character(6) not null, -- 邮编
code text[] not null, -- 省市区编号
value text[] not null, -- 省市区
insert_date date, -- 创建时间
update_date date, -- 更新时间
foreign key (customer_id) references public.customer (id)
match simple on update no action on delete cascade
);
comment on table public.address is '收货地址';
comment on column public.address.customer_id is '消费者ID';
comment on column public.address.location is '坐标';
comment on column public.address.type is '类型';
comment on column public.address.name is '地址名称';
comment on column public.address.detail is '地址详情';
comment on column public.address.postcode is '邮编';
comment on column public.address.code is '省市区编号';
comment on column public.address.value is '省市区';
comment on column public.address.insert_date is '创建时间';
comment on column public.address.update_date is '更新时间';
create table public.advice
(
id character varying(36) primary key not null default (gen_random_uuid())::text, -- 主键
merchant_id character varying(36) not null, -- 主体ID
customer_id character varying(36) not null, -- 用户ID
name character varying(50) not null, -- 姓名
phone character(11) not null, -- 手机号
title character varying(255), -- 标题
description character varying(255), -- 描述
description_pictures text[], -- 描述图片
remark character varying(255), -- 备注
status character varying(50) not null, -- 状态（WAIT待处理、CLOSED已关闭、ERROR未解决、SUCCESS已解决）
insert_datetime timestamp(0) without time zone not null, -- 创建时间
update_datetime timestamp(0) without time zone not null -- 更新时间
);
comment on table public.advice is '意见箱';
comment on column public.advice.id is '主键';
comment on column public.advice.merchant_id is '主体ID';
comment on column public.advice.customer_id is '用户ID';
comment on column public.advice.name is '姓名';
comment on column public.advice.phone is '手机号';
comment on column public.advice.title is '标题';
comment on column public.advice.description is '描述';
comment on column public.advice.description_pictures is '描述图片';
comment on column public.advice.remark is '备注';
comment on column public.advice.status is '状态（WAIT待处理、CLOSED已关闭、ERROR未解决、SUCCESS已解决）';
comment on column public.advice.insert_datetime is '创建时间';
comment on column public.advice.update_datetime is '更新时间';
create table public.application
(
id character varying(36) primary key not null default (gen_random_uuid())::text,
context_path character varying(20) not null, -- 服务名称
name character varying(50) not null, -- 名称
type character varying(10) not null, -- 类型
value character varying(255) not null, -- 路径
path character varying(255) not null, -- 服务名称+路径
insert_datetime timestamp(3) without time zone, -- 创建时间
update_datetime timestamp(3) without time zone -- 更新时间
);
create unique index ui_context_path_name on application using btree (context_path, name);
comment on table public.application is '服务资源';
comment on column public.application.context_path is '服务名称';
comment on column public.application.name is '名称';
comment on column public.application.type is '类型';
comment on column public.application.value is '路径';
comment on column public.application.path is '服务名称+路径';
comment on column public.application.insert_datetime is '创建时间';
comment on column public.application.update_datetime is '更新时间';
create table public.cart
(
id character varying(36) primary key not null default (gen_random_uuid())::text,
customer_id character varying(255) not null, -- 消费者ID
product_id character varying(36) not null, -- 商品ID
merchant_id character varying(36) not null, -- 商户ID
total numeric not null, -- 小计
checked boolean not null default false, -- 是否选中
num integer not null default 1, -- 购买数量
insert_date date, -- 创建时间
update_date date, -- 更新时间
foreign key (merchant_id) references public.merchant (id)
match simple on update no action on delete cascade,
foreign key (product_id) references public.product (id)
match simple on update no action on delete cascade
);
create unique index ui_cart_merchant_id_customer_id_product_id on cart using btree (merchant_id, customer_id, product_id);
comment on table public.cart is '购物车';
comment on column public.cart.customer_id is '消费者ID';
comment on column public.cart.product_id is '商品ID';
comment on column public.cart.merchant_id is '商户ID';
comment on column public.cart.total is '小计';
comment on column public.cart.checked is '是否选中';
comment on column public.cart.num is '购买数量';
comment on column public.cart.insert_date is '创建时间';
comment on column public.cart.update_date is '更新时间';
create table public.door
(
id character varying(36) primary key not null default (gen_random_uuid())::text,
merchant_id character varying(36), -- 主体ID
imei character varying(50) not null, -- 设备编码
title character varying(50) not null, -- 标题
ip character varying(39) not null, -- IP
remarks character varying(255), -- 备注
status character varying(50) not null, -- 状态
update_datetime timestamp(0) without time zone not null default now() -- 更新时间
);
create unique index uk_door_imei on door using btree (imei);
comment on table public.door is '门口';
comment on column public.door.merchant_id is '主体ID';
comment on column public.door.imei is '设备编码';
comment on column public.door.title is '标题';
comment on column public.door.ip is 'IP';
comment on column public.door.remarks is '备注';
comment on column public.door.status is '状态';
comment on column public.door.update_datetime is '更新时间';
create table public.door_customer
(
id character varying(36) primary key not null default (gen_random_uuid())::text,
door_id character varying(36) not null, -- 门口ID
customer_id character varying(36) not null, -- 用户ID
merchant_id character varying(36) not null, -- 主体ID
foreign key (customer_id) references public.customer (id)
match simple on update no action on delete cascade,
foreign key (door_id) references public.door (id)
match simple on update no action on delete cascade
);
comment on table public.door_customer is '门口与用户关联';
comment on column public.door_customer.door_id is '门口ID';
comment on column public.door_customer.customer_id is '用户ID';
comment on column public.door_customer.merchant_id is '主体ID';
create table public.house
(
id character varying(36) primary key not null default (gen_random_uuid())::text,
merchant_id character varying(36) not null, -- 主体ID
floor_number character varying(50), -- 楼号
unit character varying(50), -- 单元
room_number character varying(50), -- 房号
type character varying(50) not null, -- 类型:住宅、商铺、公寓、办公室
area numeric(20, 2) not null, -- 房屋面积：平方米
people_number smallint not null default 0, -- 人数
park_space_count smallint not null default 0, -- 车位数量
remarks character varying(255), -- 备注
floor integer -- 楼层
);
create unique index house_merchant_id_floor_number_unit_room_number_uindex on house using btree (merchant_id, floor_number, unit, room_number);
comment on table public.house is '房屋';
comment on column public.house.merchant_id is '主体ID';
comment on column public.house.floor_number is '楼号';
comment on column public.house.unit is '单元';
comment on column public.house.room_number is '房号';
comment on column public.house.type is '类型:住宅、商铺、公寓、办公室';
comment on column public.house.area is '房屋面积：平方米';
comment on column public.house.people_number is '人数';
comment on column public.house.park_space_count is '车位数量';
comment on column public.house.remarks is '备注';
comment on column public.house.floor is '楼层';
create table public.house_customer
(
id character varying(36) primary key not null default (gen_random_uuid())::text,
relation character varying(50) not null, -- 房屋与用户之间的关系：房东、租户
house_id character varying(36) not null, -- 房屋ID
customer_id character varying(36) not null, -- 用户ID
merchant_id character varying(36) not null, -- 主体ID
foreign key (customer_id) references public.customer (id)
match simple on update no action on delete cascade,
foreign key (house_id) references public.house (id)
match simple on update no action on delete no action
);
create unique index house_customer_hid_cid_uindex on house_customer using btree (house_id, customer_id);
comment on table public.house_customer is '营业员与房屋关联';
comment on column public.house_customer.relation is '房屋与用户之间的关系：房东、租户';
comment on column public.house_customer.house_id is '房屋ID';
comment on column public.house_customer.customer_id is '用户ID';
comment on column public.house_customer.merchant_id is '主体ID';
create table public.house_identity
(
id character varying(36) primary key not null default (gen_random_uuid())::text,
customer_id character varying(36) not null, -- 用户ID
name character varying(50) not null, -- 姓名
id_card character(18) not null, -- 身份证
id_card_face text, -- 身份证-人脸
id_card_national text, -- 身份证-国徽
face text, -- 人脸
phone character(11) not null, -- 手机号
house_id character varying(36) not null, -- 房屋ID
floor_number character varying(50), -- 楼号
unit character varying(50), -- 单元
room_number character varying(50), -- 房号
type character varying(50) not null, -- 类型:住宅、商铺、公寓、办公室
relation character varying(50) not null, -- 房屋与用户之间的关系：房东、租户
status character varying(50) not null, -- 状态
insert_datetime timestamp(0) without time zone not null default now(), -- 创建时间
update_datetime timestamp(0) without time zone not null default now(), -- 更新时间
merchant_id character varying(36) not null -- 主体ID
);
comment on table public.house_identity is '实名认证';
comment on column public.house_identity.customer_id is '用户ID';
comment on column public.house_identity.name is '姓名';
comment on column public.house_identity.id_card is '身份证';
comment on column public.house_identity.id_card_face is '身份证-人脸';
comment on column public.house_identity.id_card_national is '身份证-国徽';
comment on column public.house_identity.face is '人脸';
comment on column public.house_identity.phone is '手机号';
comment on column public.house_identity.house_id is '房屋ID';
comment on column public.house_identity.floor_number is '楼号';
comment on column public.house_identity.unit is '单元';
comment on column public.house_identity.room_number is '房号';
comment on column public.house_identity.type is '类型:住宅、商铺、公寓、办公室';
comment on column public.house_identity.relation is '房屋与用户之间的关系：房东、租户';
comment on column public.house_identity.status is '状态';
comment on column public.house_identity.insert_datetime is '创建时间';
comment on column public.house_identity.update_datetime is '更新时间';
comment on column public.house_identity.merchant_id is '主体ID';
create table public.house_renting
(
id character varying(36) primary key not null,
merchant_id character varying(36) not null, -- 主体ID
location point not null, -- 位置
customer_id character varying(36), -- 账号ID
floor_number character varying(50) not null, -- 楼号
unit character varying(50) not null, -- 单元
room_number character varying(50) not null, -- 房号
mode character varying(50) not null, -- 方式:整租、合租、公寓、出售
type character varying(128) not null, -- 户型
price numeric(20, 2) not null, -- 价格
name character varying(128) not null, -- 姓名
phone character varying(11) not null, -- 电话
title character varying(128) not null, -- 标题
description character varying(255) not null, -- 描述
description_pictures text[] not null, -- 图片
desired_position point, -- 期望坐标
desired_name character varying(255), -- 期望地名
desired_address character varying(255), -- 期望地址
insert_datetime timestamp(0) without time zone not null default now()
);
comment on table public.house_renting is '房屋租售';
comment on column public.house_renting.merchant_id is '主体ID';
comment on column public.house_renting.location is '位置';
comment on column public.house_renting.customer_id is '账号ID';
comment on column public.house_renting.floor_number is '楼号';
comment on column public.house_renting.unit is '单元';
comment on column public.house_renting.room_number is '房号';
comment on column public.house_renting.mode is '方式:整租、合租、公寓、出售';
comment on column public.house_renting.type is '户型';
comment on column public.house_renting.price is '价格';
comment on column public.house_renting.name is '姓名';
comment on column public.house_renting.phone is '电话';
comment on column public.house_renting.title is '标题';
comment on column public.house_renting.description is '描述';
comment on column public.house_renting.description_pictures is '图片';
comment on column public.house_renting.desired_position is '期望坐标';
comment on column public.house_renting.desired_name is '期望地名';
comment on column public.house_renting.desired_address is '期望地址';
create table public.house_vacant
(
id character varying(36) primary key not null default (gen_random_uuid())::text, -- 主键ID
merchant_id character varying(36) not null, -- 主体ID
customer_id character varying(36) not null, -- 用户ID
house_id character varying(36) not null, -- 房屋ID
initial_water integer, -- 初始水表读数
initial_water_image character varying(255), -- 初始水表图片路径
initial_electricity character varying(255), -- 初始电表读数
initial_electricity_image character varying(255), -- 初始电表图片路径
final_water integer, -- 结束水表读数
final_water_image character varying(255), -- 结束水表图片路径
final_electricity character varying(255), -- 结束电表读数
final_electricity_image character varying(255), -- 结束电表图片路径
start_date date not null, -- 开始日期
end_date date not null, -- 结束日期
remarks text, -- 备注
floor_number character varying(50), -- 楼号
unit character varying(40), -- 单元
room_number character varying(40) -- 室
);
create index i_vh_hd_sd_ed on house_vacant using btree (house_id, start_date, end_date);
comment on table public.house_vacant is '物业空置房记录表';
comment on column public.house_vacant.id is '主键ID';
comment on column public.house_vacant.merchant_id is '主体ID';
comment on column public.house_vacant.customer_id is '用户ID';
comment on column public.house_vacant.house_id is '房屋ID';
comment on column public.house_vacant.initial_water is '初始水表读数';
comment on column public.house_vacant.initial_water_image is '初始水表图片路径';
comment on column public.house_vacant.initial_electricity is '初始电表读数';
comment on column public.house_vacant.initial_electricity_image is '初始电表图片路径';
comment on column public.house_vacant.final_water is '结束水表读数';
comment on column public.house_vacant.final_water_image is '结束水表图片路径';
comment on column public.house_vacant.final_electricity is '结束电表读数';
comment on column public.house_vacant.final_electricity_image is '结束电表图片路径';
comment on column public.house_vacant.start_date is '开始日期';
comment on column public.house_vacant.end_date is '结束日期';
comment on column public.house_vacant.remarks is '备注';
comment on column public.house_vacant.floor_number is '楼号';
comment on column public.house_vacant.unit is '单元';
comment on column public.house_vacant.room_number is '室';
create table public.merchant_customer
(
id character varying(36) primary key not null default (gen_random_uuid())::text,
customer_id character varying(36) not null, -- 营业员ID
merchant_id character varying(36) not null, -- 主体ID
foreign key (customer_id) references public.customer (id)
match simple on update no action on delete cascade
);
create unique index merchant_customer_vid_cid_uindex on merchant_customer using btree (merchant_id, customer_id);
comment on table public.merchant_customer is '营业员与主体关联';
comment on column public.merchant_customer.customer_id is '营业员ID';
comment on column public.merchant_customer.merchant_id is '主体ID';
create table public.merchant_customer_application
(
id character varying(36) primary key not null default (gen_random_uuid())::text,
customer_id character varying(36) not null, -- 营业员ID
application_id character varying(36) not null, -- 服务资源ID
merchant_id character varying(36) not null, -- 主体ID
foreign key (application_id) references public.application (id)
match simple on update no action on delete cascade,
foreign key (customer_id) references public.customer (id)
match simple on update no action on delete cascade
);
comment on table public.merchant_customer_application is '主体营业员与服务资源关联';
comment on column public.merchant_customer_application.customer_id is '营业员ID';
comment on column public.merchant_customer_application.application_id is '服务资源ID';
comment on column public.merchant_customer_application.merchant_id is '主体ID';
create table public.notice
(
id character varying(36) primary key not null default (gen_random_uuid())::text,
merchant_id character varying(36) not null, -- 主体ID
title character varying(255), -- 标题
description text, -- 描述
pictures text[], -- 图集
videos text[], -- 视频ID
notice_datetime timestamp(0) without time zone, -- 通知时间
insert_datetime timestamp(0) without time zone not null default now(), -- 创建时间
update_datetime timestamp(0) without time zone not null default now() -- 更新时间
);
comment on table public.notice is '公告通知';
comment on column public.notice.merchant_id is '主体ID';
comment on column public.notice.title is '标题';
comment on column public.notice.description is '描述';
comment on column public.notice.pictures is '图集';
comment on column public.notice.videos is '视频ID';
comment on column public.notice.notice_datetime is '通知时间';
comment on column public.notice.insert_datetime is '创建时间';
comment on column public.notice.update_datetime is '更新时间';
create table public.order_heat
(
id character varying(36) primary key not null default (gen_random_uuid())::text,
merchant_id character varying(36) not null, -- 主体ID
house_id character varying(36) not null,
floor_number character varying(50), -- 楼号
unit character varying(50), -- 单元
room_number character varying(50), -- 房号
area numeric(20, 2) not null, -- 面积
heat_total numeric(20, 2) not null, -- 采暖费
type character varying(50) not null, -- 类型:住宅、商铺、公寓、办公室
remarks character varying(255), -- 备注
code character varying(32), -- 编号
start_date date not null, -- 开始日期
end_date date not null, -- 结束日期
status character varying(50) not null, -- 状态:待支付、已支付
pay_datetime timestamp(0) without time zone, -- 支付时间
notice_datetime timestamp(0) without time zone, -- 通知时间
update_datetime timestamp(0) without time zone not null default now() -- 更新日期
);
create unique index order_heat_hid_sd_ed_uindex on order_heat using btree (house_id, start_date, end_date);
comment on table public.order_heat is '采暖费';
comment on column public.order_heat.merchant_id is '主体ID';
comment on column public.order_heat.floor_number is '楼号';
comment on column public.order_heat.unit is '单元';
comment on column public.order_heat.room_number is '房号';
comment on column public.order_heat.area is '面积';
comment on column public.order_heat.heat_total is '采暖费';
comment on column public.order_heat.type is '类型:住宅、商铺、公寓、办公室';
comment on column public.order_heat.remarks is '备注';
comment on column public.order_heat.code is '编号';
comment on column public.order_heat.start_date is '开始日期';
comment on column public.order_heat.end_date is '结束日期';
comment on column public.order_heat.status is '状态:待支付、已支付';
comment on column public.order_heat.pay_datetime is '支付时间';
comment on column public.order_heat.notice_datetime is '通知时间';
comment on column public.order_heat.update_datetime is '更新日期';
create table public.order_house
(
id character varying(36) primary key not null default (gen_random_uuid())::text,
merchant_id character varying(36) not null, -- 主体ID
house_id character varying(36) not null,
floor_number character varying(50), -- 楼号
unit character varying(50), -- 单元
room_number character varying(50), -- 房号
area numeric(20, 2) not null, -- 面积
rubbish_price numeric(20, 2) not null, -- 垃圾费:元/月
property_total numeric(20, 2) not null,
park_space_count smallint not null, -- 车位数量
park_space_total numeric(20, 2) not null, -- 车位管理费
total numeric(20, 2) not null, -- 合计:元
type character varying(50) not null, -- 类型:住宅、商铺、公寓、办公室
remarks character varying(255), -- 备注
code character varying(32), -- 编号
start_date date not null, -- 开始日期
end_date date not null, -- 结束日期
month_count bigint not null, -- 月数
status character varying(50) not null, -- 状态:待支付、已支付
pay_datetime timestamp(0) without time zone, -- 支付时间
notice_datetime timestamp(0) without time zone, -- 通知时间
update_datetime timestamp(0) without time zone not null default now() -- 更新日期
);
create unique index order_house_hid_sd_ed_uindex on order_house using btree (house_id, start_date, end_date);
comment on table public.order_house is '物业费';
comment on column public.order_house.merchant_id is '主体ID';
comment on column public.order_house.floor_number is '楼号';
comment on column public.order_house.unit is '单元';
comment on column public.order_house.room_number is '房号';
comment on column public.order_house.area is '面积';
comment on column public.order_house.rubbish_price is '垃圾费:元/月';
comment on column public.order_house.park_space_count is '车位数量';
comment on column public.order_house.park_space_total is '车位管理费';
comment on column public.order_house.total is '合计:元';
comment on column public.order_house.type is '类型:住宅、商铺、公寓、办公室';
comment on column public.order_house.remarks is '备注';
comment on column public.order_house.code is '编号';
comment on column public.order_house.start_date is '开始日期';
comment on column public.order_house.end_date is '结束日期';
comment on column public.order_house.month_count is '月数';
comment on column public.order_house.status is '状态:待支付、已支付';
comment on column public.order_house.pay_datetime is '支付时间';
comment on column public.order_house.notice_datetime is '通知时间';
comment on column public.order_house.update_datetime is '更新日期';
create table public.order_product
(
id character varying(36) primary key not null default (gen_random_uuid())::text,
customer_id character varying(36) not null, -- 消费者ID
username character varying(20), -- 消费者联系人
phone character(11), -- 联系方式
user_address_name character varying(255), -- 收货地址名称
user_address character varying(100), -- 收货地址描述
user_address_detail character varying(255), -- 收货地址详情
user_address_point point, -- 收货地址坐标
way character varying(50) not null, -- 配送方式
details json, -- 商品列表
merchant_id character varying(36) not null, -- 商户ID
store_address_detail character varying(50) not null, -- 商户地址详情
store_address_name character varying(50) not null, -- 商户地址名称
store_address_value text[] not null, -- 商户省市区
store_address_code text[] not null, -- 商户省市区编号
store_phone character(11) not null, -- 商户联系方式
store_location point not null, -- 商户坐标
store_postcode character(6) not null, -- 商户邮编
store_shortname character varying(20) not null, -- 商户简称
store_username character varying(10) not null, -- 商户联系人
total numeric not null, -- 订单总金额
status character varying(50) not null default 'WAIT_PAY', -- 订单状态
insert_datetime timestamp(0) without time zone -- 创建时间
);
create index i_score_insert_date_merchant_id on order_product using btree (insert_datetime, merchant_id);
create index i_score_insert_date_customer_id on order_product using btree (insert_datetime, customer_id);
comment on table public.order_product is '订单';
comment on column public.order_product.customer_id is '消费者ID';
comment on column public.order_product.username is '消费者联系人';
comment on column public.order_product.phone is '联系方式';
comment on column public.order_product.user_address_name is '收货地址名称';
comment on column public.order_product.user_address is '收货地址描述';
comment on column public.order_product.user_address_detail is '收货地址详情';
comment on column public.order_product.user_address_point is '收货地址坐标';
comment on column public.order_product.way is '配送方式';
comment on column public.order_product.details is '商品列表';
comment on column public.order_product.merchant_id is '商户ID';
comment on column public.order_product.store_address_detail is '商户地址详情';
comment on column public.order_product.store_address_name is '商户地址名称';
comment on column public.order_product.store_address_value is '商户省市区';
comment on column public.order_product.store_address_code is '商户省市区编号';
comment on column public.order_product.store_phone is '商户联系方式';
comment on column public.order_product.store_location is '商户坐标';
comment on column public.order_product.store_postcode is '商户邮编';
comment on column public.order_product.store_shortname is '商户简称';
comment on column public.order_product.store_username is '商户联系人';
comment on column public.order_product.total is '订单总金额';
comment on column public.order_product.status is '订单状态';
comment on column public.order_product.insert_datetime is '创建时间';
create table public.order_water
(
id character varying(36) primary key not null default (gen_random_uuid())::text,
merchant_id character varying(36) not null, -- 主体ID
house_id character varying(36) not null,
floor_number character varying(50), -- 楼号
unit character varying(50), -- 单元
room_number character varying(50), -- 房号
water_initial numeric(20, 5) not null, -- 水表初始值
water_current numeric(20, 5) not null, -- 水表当前值
water_count numeric(20, 5) not null, -- 用水量
water_loss numeric(20, 5) not null, -- 损耗
water_ratio numeric(20, 5) not null,
water_total numeric(20, 2) not null, -- 水费
type character varying(50) not null, -- 类型:住宅、商铺、公寓、办公室
remarks character varying(255), -- 备注
code character varying(32), -- 编号
start_date date not null, -- 开始日期
end_date date not null, -- 结束日期
status character varying(50) not null, -- 状态:待支付、已支付
pay_datetime timestamp(0) without time zone, -- 支付时间
notice_datetime timestamp(0) without time zone, -- 通知时间
update_datetime timestamp(0) without time zone not null default now() -- 更新日期
);
create unique index order_water_hid_sd_ed_uindex on order_water using btree (house_id, start_date, end_date);
comment on table public.order_water is '水费';
comment on column public.order_water.merchant_id is '主体ID';
comment on column public.order_water.floor_number is '楼号';
comment on column public.order_water.unit is '单元';
comment on column public.order_water.room_number is '房号';
comment on column public.order_water.water_initial is '水表初始值';
comment on column public.order_water.water_current is '水表当前值';
comment on column public.order_water.water_count is '用水量';
comment on column public.order_water.water_loss is '损耗';
comment on column public.order_water.water_total is '水费';
comment on column public.order_water.type is '类型:住宅、商铺、公寓、办公室';
comment on column public.order_water.remarks is '备注';
comment on column public.order_water.code is '编号';
comment on column public.order_water.start_date is '开始日期';
comment on column public.order_water.end_date is '结束日期';
comment on column public.order_water.status is '状态:待支付、已支付';
comment on column public.order_water.pay_datetime is '支付时间';
comment on column public.order_water.notice_datetime is '通知时间';
comment on column public.order_water.update_datetime is '更新日期';
create table public.park_region
(
id character varying(36) primary key not null default (gen_random_uuid())::text, -- 主键
name character varying(255), -- 名称
formula text, -- 计算公式
price numeric(20, 2) not null, -- 单价，单位：元
free_time integer not null, -- 减免时长，单位：分钟
merchant_id character varying(36) not null, -- 主体ID
insert_datetime timestamp(0) without time zone not null default now(), -- 创建时间
update_datetime timestamp(0) without time zone not null default now() -- 更新日期
);
comment on table public.park_region is '停车区域';
comment on column public.park_region.id is '主键';
comment on column public.park_region.name is '名称';
comment on column public.park_region.formula is '计算公式';
comment on column public.park_region.price is '单价，单位：元';
comment on column public.park_region.free_time is '减免时长，单位：分钟';
comment on column public.park_region.merchant_id is '主体ID';
comment on column public.park_region.insert_datetime is '创建时间';
comment on column public.park_region.update_datetime is '更新日期';
create table public.order_park
(
id character varying(36) primary key not null default (gen_random_uuid())::text, -- 主键
car_number character varying(255), -- 车牌号
car_number_picture character varying(255), -- 车牌图片
in_datetime timestamp(0) without time zone not null, -- 驶入时间
in_picture character varying(255), -- 驶入图片
out_datetime timestamp(0) without time zone, -- 驶离时间
out_picture character varying(255), -- 驶离图片
money_meet numeric(20, 2) not null, -- 应缴金额
money_reduction numeric(20, 2) not null, -- 减免金额
money_payment numeric(20, 2) not null, -- 实缴金额
park_status character varying(50) not null, -- 停车状态
in_device_number character varying(255), -- 驶入设备编号
out_device_number character varying(255), -- 驶离设备编号
merchant_id character varying(36) not null, -- 主体ID
park_region_id character varying(36) not null, -- 区域ID
remarks character varying(255), -- 备注
notice_datetime timestamp(0) without time zone, -- 通知时间
update_datetime timestamp(0) without time zone, -- 更新日期
foreign key (park_region_id) references public.park_region (id)
match simple on update no action on delete no action
);
comment on table public.order_park is '停车费';
comment on column public.order_park.id is '主键';
comment on column public.order_park.car_number is '车牌号';
comment on column public.order_park.car_number_picture is '车牌图片';
comment on column public.order_park.in_datetime is '驶入时间';
comment on column public.order_park.in_picture is '驶入图片';
comment on column public.order_park.out_datetime is '驶离时间';
comment on column public.order_park.out_picture is '驶离图片';
comment on column public.order_park.money_meet is '应缴金额';
comment on column public.order_park.money_reduction is '减免金额';
comment on column public.order_park.money_payment is '实缴金额';
comment on column public.order_park.park_status is '停车状态';
comment on column public.order_park.in_device_number is '驶入设备编号';
comment on column public.order_park.out_device_number is '驶离设备编号';
comment on column public.order_park.merchant_id is '主体ID';
comment on column public.order_park.park_region_id is '区域ID';
comment on column public.order_park.remarks is '备注';
comment on column public.order_park.notice_datetime is '通知时间';
comment on column public.order_park.update_datetime is '更新日期';
create table public.access
(
id character varying(36) primary key not null default (gen_random_uuid())::text,
merchant_id character varying(36), -- 主体ID
park_region_id character varying(36), -- 区域ID
imei character varying(50) not null, -- 设备编码
title character varying(50) not null, -- 标题
ip character varying(39) not null, -- IP
remarks character varying(255), -- 备注
status character varying(50) not null, -- 状态
update_datetime timestamp(0) without time zone not null default now(), -- 更新时间
type character varying(128), -- 类型
foreign key (park_region_id) references public.park_region (id)
match simple on update no action on delete no action
);
create unique index uk_access_imei on access using btree (imei);
comment on table public.access is '道闸';
comment on column public.access.merchant_id is '主体ID';
comment on column public.access.park_region_id is '区域ID';
comment on column public.access.imei is '设备编码';
comment on column public.access.title is '标题';
comment on column public.access.ip is 'IP';
comment on column public.access.remarks is '备注';
comment on column public.access.status is '状态';
comment on column public.access.update_datetime is '更新时间';
comment on column public.access.type is '类型';
create table public.park_capture
(
id varchar(36) default (gen_random_uuid())::text not null
primary key,
code varchar(255),
code_image varchar(255),
code_reliability numeric(20, 2),
color varchar(255),
color_reliability numeric(20, 2),
shooting_angle numeric(20, 2),
merchant_id varchar(255),
device_number varchar(255),
data_type varchar(50),
capture_image varchar(255),
capture_time timestamp(0),
upload_time timestamp(0),
receive_time timestamp(0),
save_time timestamp(0) default now()
);
create table public.parkSpace
(
id character varying(36) primary key not null default (gen_random_uuid())::text,
merchant_id character varying(36) not null, -- 主体ID
house_id character varying(36), -- 房屋ID
region character varying(50) not null, -- 区域
code character varying(50) not null, -- 编号
car_code character varying(50), -- 车牌号
state character varying(50) not null, -- 占用状态：有车、无车
status character varying(50) not null, -- 使用状态：出售、出租、闲置
remarks character varying(255), -- 备注
foreign key (house_id) references public.house (id)
match simple on update no action on delete no action
);
comment on table public.parkSpace is '车位';
comment on column public.parkSpace.merchant_id is '主体ID';
comment on column public.parkSpace.house_id is '房屋ID';
comment on column public.parkSpace.region is '区域';
comment on column public.parkSpace.code is '编号';
comment on column public.parkSpace.car_code is '车牌号';
comment on column public.parkSpace.state is '占用状态：有车、无车';
comment on column public.parkSpace.status is '使用状态：出售、出租、闲置';
comment on column public.parkSpace.remarks is '备注';
create table public.pay_order
(
id character varying(36) primary key not null default (gen_random_uuid())::text,
merchant_id character varying(36) not null, -- 主体ID
sub_mch_id character varying(36) not null, -- 商户号
merchant_shortname character varying(255), -- 主体简称
customer_id character varying(36) not null, -- 用户ID
order_ids text[], -- 详情ID
details json, -- 详情
refunds json, -- 退款记录
description character varying(255) not null, -- 描述
total numeric(20, 2) not null, -- 支付金额
remarks character varying(255), -- 备注
status character varying(50) not null, -- 支付状态
pay_datetime timestamp(0) without time zone, -- 支付时间
prepay_id character varying(255), -- 预支付编号
out_trade_no character varying(255) not null, -- 订单编号
insert_datetime timestamp(0) without time zone not null default now(), -- 创建时间
update_datetime timestamp(0) without time zone not null default now() -- 更新日期
);
create unique index pay_order_out_trade_no_key on pay_order using btree (out_trade_no);
comment on table public.pay_order is '支付记录';
comment on column public.pay_order.merchant_id is '主体ID';
comment on column public.pay_order.sub_mch_id is '商户号';
comment on column public.pay_order.merchant_shortname is '主体简称';
comment on column public.pay_order.customer_id is '用户ID';
comment on column public.pay_order.order_ids is '详情ID';
comment on column public.pay_order.details is '详情';
comment on column public.pay_order.refunds is '退款记录';
comment on column public.pay_order.description is '描述';
comment on column public.pay_order.total is '支付金额';
comment on column public.pay_order.remarks is '备注';
comment on column public.pay_order.status is '支付状态';
comment on column public.pay_order.pay_datetime is '支付时间';
comment on column public.pay_order.prepay_id is '预支付编号';
comment on column public.pay_order.out_trade_no is '订单编号';
comment on column public.pay_order.insert_datetime is '创建时间';
comment on column public.pay_order.update_datetime is '更新日期';
create table public.repair
(
id character varying(36) primary key not null default (gen_random_uuid())::text,
customer_id character varying(36) not null, -- 用户ID
name character varying(50) not null, -- 姓名
phone character varying(11) not null, -- 手机号
address character varying(255) not null, -- 详细地址
title character varying(255), -- 申报标题
description character varying(255), -- 申报描述
description_pictures text[], -- 申报图集
status character varying(50) not null, -- 状态(待分配、待处理、待评价、已结束)
label character varying(128), -- 标签
repairer_customer_id character varying(36), -- 用户ID
repairer_name character varying(50), -- 姓名
repairer_phone character varying(11), -- 手机号
merchant_id character varying(36) not null, -- 主体ID
merchant_shortname character varying(36) not null, -- 主体简称
insert_datetime timestamp(0) without time zone not null default now(), -- 创建时间
update_datetime timestamp(0) without time zone not null default now() -- 更新时间
);
comment on table public.repair is '报事报修';
comment on column public.repair.customer_id is '用户ID';
comment on column public.repair.name is '姓名';
comment on column public.repair.phone is '手机号';
comment on column public.repair.address is '详细地址';
comment on column public.repair.title is '申报标题';
comment on column public.repair.description is '申报描述';
comment on column public.repair.description_pictures is '申报图集';
comment on column public.repair.status is '状态(待分配、待处理、待评价、已结束)';
comment on column public.repair.label is '标签';
comment on column public.repair.repairer_customer_id is '用户ID';
comment on column public.repair.repairer_name is '姓名';
comment on column public.repair.repairer_phone is '手机号';
comment on column public.repair.merchant_id is '主体ID';
comment on column public.repair.merchant_shortname is '主体简称';
comment on column public.repair.insert_datetime is '创建时间';
comment on column public.repair.update_datetime is '更新时间';
create table public.visitor
(
id character varying(36) primary key not null default (gen_random_uuid())::text, -- 主键ID
merchant_id character varying(36) not null, -- 主体ID
shortname character varying(255) not null, -- 主体简称
customer_id character varying(36) not null, -- 用户ID
name character varying(36) not null, -- 姓名
id_card character varying(36) not null, -- 身份证
phone character varying(36) not null, -- 手机号
car_code character varying(50), -- 车牌号
house_id character varying(36) not null, -- 房屋ID
visit_datetime timestamp(0) without time zone not null, -- 访客时间
leave_datetime timestamp(0) without time zone not null, -- 离开时间
application_time timestamp(0) without time zone not null default now(), -- 申请时间
remarks text, -- 备注
floor_number character varying(50), -- 楼号
unit character varying(40), -- 单元
room_number character varying(40) -- 室
);
comment on table public.visitor is '访客';
comment on column public.visitor.id is '主键ID';
comment on column public.visitor.merchant_id is '主体ID';
comment on column public.visitor.shortname is '主体简称';
comment on column public.visitor.customer_id is '用户ID';
comment on column public.visitor.name is '姓名';
comment on column public.visitor.id_card is '身份证';
comment on column public.visitor.phone is '手机号';
comment on column public.visitor.car_code is '车牌号';
comment on column public.visitor.house_id is '房屋ID';
comment on column public.visitor.visit_datetime is '访客时间';
comment on column public.visitor.leave_datetime is '离开时间';
comment on column public.visitor.application_time is '申请时间';
comment on column public.visitor.remarks is '备注';
comment on column public.visitor.floor_number is '楼号';
comment on column public.visitor.unit is '单元';
comment on column public.visitor.room_number is '室';
create table public.vote
(
id character varying(36) primary key not null default (gen_random_uuid())::text,
merchant_id character varying(36) not null, -- 主体ID
title character varying(255), -- 标题
description character varying(255), -- 描述
pictures text[], -- 图集
videos text[], -- 视频ID
options text[], -- 选项
result character varying(255) not null default '', -- 结果
notice_datetime timestamp(0) without time zone, -- 通知时间
insert_datetime timestamp(0) without time zone not null default now(), -- 创建时间
update_datetime timestamp(0) without time zone not null default now() -- 更新时间
);
comment on table public.vote is '投票';
comment on column public.vote.merchant_id is '主体ID';
comment on column public.vote.title is '标题';
comment on column public.vote.description is '描述';
comment on column public.vote.pictures is '图集';
comment on column public.vote.videos is '视频ID';
comment on column public.vote.options is '选项';
comment on column public.vote.result is '结果';
comment on column public.vote.notice_datetime is '通知时间';
comment on column public.vote.insert_datetime is '创建时间';
comment on column public.vote.update_datetime is '更新时间';
create table public.voter
(
id character varying(36) primary key not null default (gen_random_uuid())::text,
vote_id character varying(36) not null, -- 投票ID
house_id character varying(36) not null, -- 房屋ID
merchant_id character varying(36) not null, -- 主体ID
customer_id character varying(36) not null, -- 用户ID
option character varying(255), -- 选项
remark character varying(255), -- 备注
insert_datetime timestamp(0) without time zone not null default now(), -- 创建时间
foreign key (vote_id) references public.vote (id)
match simple on update no action on delete cascade
);
create unique index voter_vid_hid_uindex on voter using btree (vote_id, house_id);
comment on table public.voter is '投票者';
comment on column public.voter.vote_id is '投票ID';
comment on column public.voter.house_id is '房屋ID';
comment on column public.voter.merchant_id is '主体ID';
comment on column public.voter.customer_id is '用户ID';
comment on column public.voter.option is '选项';
comment on column public.voter.remark is '备注';
comment on column public.voter.insert_datetime is '创建时间';
此处可能存在不合适展示的内容，页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容，可点击提交进行申诉，我们将尽快为您处理。
马建仓 AI 助手