嘿，这个需求我之前帮朋友梳理过，其实核心就是用一对多关联来搞定公司和营业时间的关系，同时字段设计要兼顾灵活性和查询效率。下面给你拆解下具体的表结构和字段设计：

1. 核心表结构 #

公司表 (companies) #

用来存储公司的基础信息，字段尽量覆盖后续可能的查询维度：

• company_id：主键（自增整数或UUID都可以），唯一标识每家公司
• company_name：公司全称（必填，查询时最常用的字段）
• address：公司详细地址（可选，如果需要基于区域查询的话）
• contact_phone：联系电话（可选）
• created_at：记录创建时间，updated_at：记录更新时间（审计用，方便追踪数据变更）

营业时间表 (business_hours) #

这是实现灵活营业时间的核心，用来存储单家公司不同日期/时段的营业规则：

• hour_id：主键
• company_id：外键，关联companies.company_id，建立1家公司对应N条营业时间的关联关系
• day_type：枚举类型（推荐），用来区分规则类型，比如：
  • weekday：周一至周五
  • weekend：周六至周日
  • holiday：法定节假日
  • custom_date：特定自定义日期（比如店庆、临时调整日）
• target_date：日期类型，仅当day_type为custom_date时必填，存储具体日期（如2024-12-25），其他类型可为NULL
• open_time：时间类型（如09:00:00），记录开门时间，is_closed为FALSE时必填
• close_time：时间类型（如18:00:00），记录关门时间，is_closed为FALSE时必填
• is_closed：布尔值，标记当天是否停业（比如节假日直接关门，就不用填开闭时间了）
• note：文本类型，备注信息（如“圣诞临时停业”“周末仅上午营业”）

2. 关联关系与索引优化 #

• 关联逻辑：通过company_id外键，将companies和business_hours绑定，完美支持一家公司对应多条营业时间记录的需求
• 索引建议：给business_hours加联合索引(company_id, day_type, target_date)，能大幅提升“某公司某天营业时间”“哪些公司今天营业到6点”这类查询的速度

3. 常见查询场景示例 #

针对你关心的“哪些公司...”类查询，给几个实用的SQL示例：

查询周一至周五营业到18:00的公司 #

SELECT DISTINCT c.company_name, c.address
FROM companies c
JOIN business_hours bh ON c.company_id = bh.company_id
WHERE bh.day_type = 'weekday' 
  AND bh.close_time = '18:00:00'
  AND bh.is_closed = FALSE;

查询今天（比如2024-10-05，周六）正常营业的公司 #

SELECT c.company_name, bh.open_time, bh.close_time
FROM companies c
JOIN business_hours bh ON c.company_id = bh.company_id
WHERE (
    -- 优先匹配自定义日期规则
    (bh.day_type = 'custom_date' AND bh.target_date = '2024-10-05' AND bh.is_closed = FALSE)
    OR
    -- 无自定义规则则匹配周末规则
    (bh.day_type = 'weekend' AND bh.target_date IS NULL AND bh.is_closed = FALSE)
)
ORDER BY c.company_name;

查询元旦（2025-01-01）停业的公司 #

SELECT c.company_name
FROM companies c
JOIN business_hours bh ON c.company_id = bh.company_id
WHERE (
    -- 匹配自定义元旦停业规则
    (bh.day_type = 'custom_date' AND bh.target_date = '2025-01-01' AND bh.is_closed = TRUE)
    OR
    -- 匹配通用节假日停业规则
    (bh.day_type = 'holiday' AND bh.is_closed = TRUE)
);

4. 额外优化建议 #

• 如果业务中涉及大量法定节假日，可以单独新增holidays表存储所有假日日期，查询时关联该表自动判断某天是否为假日，避免在business_hours中重复添加节假日规则
• 可以给每家公司设置默认营业时间（比如周一至周五9:00-18:00），特殊日期再添加单独记录覆盖默认，减少冗余数据

内容的提问来源于stack exchange，提问作者brubdeds brindeds