时间表关系数据库设计

Question

为了项目特定的目的，我需要在现有时间表数据库应用程序的基础上构建一些查询。

的时间表数据库中删除数据。

USER_ID | ENTRY_DATE | BILLED_HRS

然后将其映射为一个项目组织结构图，我所拥有的数据是：

USER_ID | PROJECT_ROLE | START_DATE | FINISH_DATE

考虑到START_DATE和FINISH_DATE是将HRS映射到PROJECT_ROLE的唯一方法，加入这两者的最佳方法是什么。

可能只有5-10个USER_IDs会有多个角色，所以可能有一个快速跟踪的方法。我无法修改底层时间表应用程序表，因此这个连接需要足够快才能正常运行。

谢谢

Answer 1

您要查找的基本查询如下：

SELECT ts.*, po.project_role
FROM timesheet ts
LEFT JOIN project_organization po ON
   po.user_id = ts.user_id AND
   ts.entry_date BETWEEN po.start_date AND po.finish_date

我将留一个左联接，以确保即使与任何project_role不匹配，也不会松散时间表数据。不过要小心..。如果项目组织表的行与相同的start_date和finish_date重叠，您将得到重复的时间表数据。与外部连接相比，这是不可避免的。

至于这个查询的效率，它很大程度上取决于您拥有的索引。

作为个人建议..。我建议定期提取数据(也许每晚)，并在另一个数据库中复制一份，以供报告之用。通过这种方式，您可以检查它(显示不匹配project_role或匹配多个project_role的数据的警告)并清除它(直接在时间表数据表上写入project_role)。当然，如果要在实时数据上运行查询，则不能这样做。但是我也认为时间表数据每天只输入一次，所以它可能是最好的解决方案。