php - 计划的最佳数据库架构

到目前为止，我有一张排班表，看起来像这样

id | shift_id | start      | finish
1  | 1        | 2017-11-20 | 2017-11-24
2  | 2        | 2017-11-20 | 2017-11-24
3  | 1        | 2017-11-27 | 2017-12-01
4  | 2        | 2017-11-27 | 2017-12-01
5  | 1        | 2017-12-04 | 2017-12-08
6  | 6        | 2017-12-04 | 2017-12-08

我想按单个日期或使用日期范围来搜索数据库，并且一直在使用以下方法：

// Query shift data
$sql = "SELECT * FROM " . SCHEDULE_TABLE . "
        INNER JOIN " . SHIFTS_TABLE . " ON " . SCHEDULE_TABLE . ".shift_id = " . SHIFTS_TABLE . ".shift_id
        WHERE :startDate >= start AND start < :endDate";
$stmt = $pdo->prepare($sql);
$stmt->execute(['startDate' => $startDate, 'endDate' => $endDate]);

因此，如果我将$ startdate设置为“ 2017-11-21”，将$ endDate设置为“ 2017-11-21”，则我希望查询返回ID为1和2的结果（确实如此）

但是，如果我搜索的范围超出给定的一周，例如，如果我将$ startDate设置为“ 2017-11-22”，将endDate设置为“ 2017-12-06”，则只会得到1周的结果，我希望查询能够返回所有6行，但仅返回前2行。

我知道这可能与我的查询有关，因为我只搜索开始列，而从未使用完成列。

最终，当查询返回时，我会将日期范围内的每个班次放入一个数组中，因此如果我搜索“ 2017-11-20”-“ 2017-11-24”，我将得到一个数组：-

        {
        date: 2017-11-20
        shift: 1
        date: 2017-11-20
        shift: 2
        date: 2017-11-21
        shift: 1
        date: 2017-11-21
        shift: 2
etc...
}

我应该寻找解决我的SQL查询还是应该重新组织表，以便我每天存储而不是5天存储？

最佳答案

这看起来像是重叠范围问题。在WHERE子句中尝试以下逻辑：

WHERE :startDate < finish AND :endDate > start

这是以下查询的演示，该查询显示针对您提到的日期范围返回了所有六个记录。

SELECT *
FROM yourTable
WHERE '2017-11-22' < finish AND '2017-12-06' > start;

Demo

关于php - 计划的最佳数据库架构，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/47706490/