275 lines
8.6 KiB
SQL
275 lines
8.6 KiB
SQL
-- ============================================================
|
|
-- 餐食模块 数据库表结构
|
|
-- ============================================================
|
|
|
|
drop table if exists b_meal_plan_rating;
|
|
drop table if exists b_meal_dish_tag;
|
|
drop table if exists b_meal_dish_ingredient;
|
|
drop table if exists b_meal_dish_step;
|
|
drop table if exists b_meal_plan_log;
|
|
drop table if exists b_meal_plan_item;
|
|
drop table if exists b_meal_plan;
|
|
drop table if exists b_meal_dish;
|
|
drop table if exists b_meal_category;
|
|
drop table if exists b_meal_tag;
|
|
drop table if exists b_meal_slot;
|
|
|
|
-- ============================================================
|
|
-- 菜品
|
|
-- ============================================================
|
|
create table b_meal_dish (
|
|
id bigint primary key,
|
|
family_id bigint not null,
|
|
name varchar(64) not null,
|
|
image_file_key varchar(512) not null,
|
|
category_id bigint,
|
|
ingredient_summary varchar(512) default '',
|
|
cooking_time_minutes int default 0,
|
|
difficulty smallint default 0,
|
|
remark varchar(512) default '',
|
|
status smallint not null default 1,
|
|
sort_number int not null default 0,
|
|
create_time timestamptz,
|
|
update_time timestamptz,
|
|
create_by bigint,
|
|
update_by bigint
|
|
);
|
|
|
|
-- ============================================================
|
|
-- 菜品分类
|
|
-- ============================================================
|
|
create table b_meal_category (
|
|
id bigint primary key,
|
|
family_id bigint not null,
|
|
name varchar(32) not null,
|
|
sort_number int not null default 0,
|
|
status smallint not null default 1,
|
|
create_time timestamptz,
|
|
update_time timestamptz,
|
|
create_by bigint,
|
|
update_by bigint
|
|
);
|
|
|
|
-- ============================================================
|
|
-- 标签（父子结构）
|
|
-- parent_id 为空表示父标签，指向父标签表示子标签
|
|
-- ============================================================
|
|
create table b_meal_tag (
|
|
id bigint primary key,
|
|
family_id bigint not null,
|
|
parent_id bigint,
|
|
name varchar(32) not null,
|
|
color varchar(7) default '',
|
|
sort_number int not null default 0,
|
|
status smallint not null default 1,
|
|
create_time timestamptz,
|
|
update_time timestamptz,
|
|
create_by bigint,
|
|
update_by bigint
|
|
);
|
|
|
|
-- ============================================================
|
|
-- 菜品食材明细
|
|
-- ============================================================
|
|
create table b_meal_dish_ingredient (
|
|
id bigint primary key,
|
|
family_id bigint not null,
|
|
dish_id bigint not null,
|
|
name varchar(64) not null,
|
|
quantity numeric(10, 2),
|
|
unit varchar(16) default '',
|
|
spec varchar(64) default '',
|
|
remark varchar(128) default '',
|
|
sort_number int not null default 0,
|
|
create_time timestamptz,
|
|
update_time timestamptz,
|
|
create_by bigint,
|
|
update_by bigint
|
|
);
|
|
|
|
-- ============================================================
|
|
-- 菜品步骤
|
|
-- ============================================================
|
|
create table b_meal_dish_step (
|
|
id bigint primary key,
|
|
family_id bigint not null,
|
|
dish_id bigint not null,
|
|
step_number int not null,
|
|
description varchar(1024) default '',
|
|
image_file_key varchar(512) default '',
|
|
sort_number int not null default 0,
|
|
create_time timestamptz,
|
|
update_time timestamptz,
|
|
create_by bigint,
|
|
update_by bigint
|
|
);
|
|
|
|
-- ============================================================
|
|
-- 菜品标签关联
|
|
-- ============================================================
|
|
create table b_meal_dish_tag (
|
|
id bigint primary key,
|
|
family_id bigint not null,
|
|
dish_id bigint not null,
|
|
tag_id bigint not null,
|
|
create_time timestamptz,
|
|
update_time timestamptz,
|
|
create_by bigint,
|
|
update_by bigint
|
|
);
|
|
|
|
-- ============================================================
|
|
-- 餐段
|
|
-- ============================================================
|
|
create table b_meal_slot (
|
|
id bigint primary key,
|
|
family_id bigint not null,
|
|
name varchar(32) not null,
|
|
sort_number int not null default 0,
|
|
status smallint not null default 1,
|
|
create_time timestamptz,
|
|
update_time timestamptz,
|
|
create_by bigint,
|
|
update_by bigint
|
|
);
|
|
|
|
-- ============================================================
|
|
-- 餐食安排
|
|
-- 同一个家庭、同一天、同一个餐段只有一条记录
|
|
-- status: 0=planning（进行中，可修改）, 1=completed（已完成，不可修改）
|
|
-- ============================================================
|
|
create table b_meal_plan (
|
|
id bigint primary key,
|
|
family_id bigint not null,
|
|
plan_date date not null,
|
|
meal_slot_id bigint not null,
|
|
status smallint not null default 0,
|
|
remark varchar(512) default '',
|
|
cook_by bigint,
|
|
create_time timestamptz,
|
|
update_time timestamptz,
|
|
create_by bigint,
|
|
update_by bigint
|
|
);
|
|
|
|
-- ============================================================
|
|
-- 餐食安排明细
|
|
-- ============================================================
|
|
create table b_meal_plan_item (
|
|
id bigint primary key,
|
|
family_id bigint not null,
|
|
plan_id bigint not null,
|
|
dish_id bigint not null,
|
|
quantity int not null default 1,
|
|
remark varchar(256) default '',
|
|
sort_number int not null default 0,
|
|
create_time timestamptz,
|
|
update_time timestamptz,
|
|
create_by bigint,
|
|
update_by bigint
|
|
);
|
|
|
|
-- ============================================================
|
|
-- 餐食活动日志
|
|
-- type: 1=安排餐食, 2=加菜, 3=移除菜, 4=备注, 5=设置做饭人
|
|
-- dish_name: 操作时的菜品名称快照，用于时间轴展示
|
|
-- ============================================================
|
|
create table b_meal_plan_log (
|
|
id bigint primary key,
|
|
family_id bigint not null,
|
|
plan_id bigint not null,
|
|
type smallint not null,
|
|
content varchar(1024) default '',
|
|
ref_id bigint,
|
|
dish_name varchar(128) default null,
|
|
create_time timestamptz,
|
|
update_time timestamptz,
|
|
create_by bigint,
|
|
update_by bigint
|
|
);
|
|
|
|
-- ============================================================
|
|
-- 餐食安排评分
|
|
-- rating: 1-5 星
|
|
-- 同一家庭、同一安排、同一评分人只有一条记录（通过索引保证唯一）
|
|
-- ============================================================
|
|
create table b_meal_plan_rating (
|
|
id bigint primary key,
|
|
family_id bigint not null,
|
|
plan_id bigint not null,
|
|
rater_id bigint not null,
|
|
rating smallint not null,
|
|
content varchar(256) default '',
|
|
create_time timestamptz,
|
|
update_time timestamptz,
|
|
create_by bigint,
|
|
update_by bigint
|
|
);
|
|
|
|
-- ============================================================
|
|
-- 索引
|
|
-- ============================================================
|
|
|
|
-- 菜品：按家庭、状态、分类查询
|
|
create index idx_b_meal_dish_family_status_category
|
|
on b_meal_dish (family_id, status, category_id, sort_number, id);
|
|
|
|
-- 菜品分类：按家庭、状态查询
|
|
create index idx_b_meal_category_family_status
|
|
on b_meal_category (family_id, status, sort_number, id);
|
|
|
|
-- 标签：按家庭、状态、父标签查询
|
|
create index idx_b_meal_tag_family_status_parent
|
|
on b_meal_tag (family_id, status, parent_id, sort_number, id);
|
|
|
|
-- 食材：按菜品查询
|
|
create index idx_b_meal_dish_ingredient_dish
|
|
on b_meal_dish_ingredient (family_id, dish_id, sort_number, id);
|
|
|
|
-- 步骤：按菜品查询
|
|
create index idx_b_meal_dish_step_dish
|
|
on b_meal_dish_step (family_id, dish_id, sort_number, step_number, id);
|
|
|
|
-- 菜品标签关联：按菜品和按标签查询
|
|
create index idx_b_meal_dish_tag_dish
|
|
on b_meal_dish_tag (family_id, dish_id, tag_id);
|
|
|
|
create index idx_b_meal_dish_tag_tag
|
|
on b_meal_dish_tag (family_id, tag_id, dish_id);
|
|
|
|
-- 餐段：按家庭、状态查询
|
|
create index idx_b_meal_slot_family_status
|
|
on b_meal_slot (family_id, status, sort_number, id);
|
|
|
|
-- 餐食安排：按家庭、日期、餐段唯一查询
|
|
create index idx_b_meal_plan_family_date_slot
|
|
on b_meal_plan (family_id, plan_date, meal_slot_id);
|
|
|
|
-- 餐食安排：按家庭、日期范围查询（历史查看）
|
|
create index idx_b_meal_plan_family_date
|
|
on b_meal_plan (family_id, plan_date, id);
|
|
|
|
-- 餐食安排明细：按安排查询
|
|
create index idx_b_meal_plan_item_plan
|
|
on b_meal_plan_item (family_id, plan_id, sort_number, id);
|
|
|
|
-- 餐食安排明细：按菜品反查
|
|
create index idx_b_meal_plan_item_dish
|
|
on b_meal_plan_item (family_id, dish_id, plan_id);
|
|
|
|
-- 活动日志：按安排和时间轴查询
|
|
create index idx_b_meal_plan_log_plan
|
|
on b_meal_plan_log (family_id, plan_id, create_time, id);
|
|
|
|
-- 活动日志：按安排和类型筛选
|
|
create index idx_b_meal_plan_log_plan_type
|
|
on b_meal_plan_log (family_id, plan_id, type);
|
|
|
|
-- 评分：按安排查询
|
|
create index idx_b_meal_plan_rating_plan
|
|
on b_meal_plan_rating (family_id, plan_id, id);
|
|
|
|
-- 评分：同一家庭、同一安排、同一评分人唯一
|
|
create unique index idx_b_meal_plan_rating_plan_rater
|
|
on b_meal_plan_rating (family_id, plan_id, rater_id);
|