在 PHP/MySQL 中使用模板和覆盖安排搜索

Question

我正在寻找一些关于相当复杂的搜索算法的建议/帮助。任何有关技术等的文章将不胜感激。

背景

我正在构建一个应用程序，简而言之，它允许用户设置任何给定日期的"可用性"。用户首先设置一个正式发布模板，该模板允许他们说：

Monday - AM   
Tuesday - PM  
Wednesday - All Day  
Thursday - None  
Friday - All Day

因此，此用户通常在周一上午，周二下午等可用。

图式：

id  
user_id  
day_of_week  (1-7)(Monday to Sunday)
availability

然后，他们可以手动覆盖特定日期，例如：

2013-03-03 - am  
2013-03-04 - pm  
2013-03-05 - all_day

图式：

id
user_id
date
availability

这一切都运行良好 - 我正在生成一个日历，它结合了模板和覆盖，并允许用户修改其可用性等。

问题所在

我现在需要允许管理员用户搜索具有特定可用性的用户。因此，管理员用户将使用日历来选择所需的日期和可用性并点击搜索。

例如，为我查找可用的用户：

2013-03-03 - pm
2013-03-04 - pm
2013-03-05 - pm

搜索

过程必须使用模板化可用性和替代搜索可用用户，然后返回最佳结果。理想情况下，它将返回始终可用的用户，但在没有单个用户可以匹配日期的情况下，我需要提供可以匹配的用户组合。

我知道这是一个相当复杂的问题，我不是在寻找一个完整的答案，也许只是一些指导或潜在相关技术的链接等。

我尝试过什么

目前，我有一个半途而废的解决方案。我正在抓取所有可用的用户，循环浏览每个用户，并在该循环中循环遍历所有必需的日期，并在用户未满足所需日期时立即中断。这显然是非常不可扩展的，它也只返回"完美匹配"。

可能的解决方案

使用聚合表进行全文搜索

我想创建一个单独的表，其中包含以下架构：

user_id
body

body 字段将使用"用户"模板天数和覆盖进行填充，因此示例记录可能如下所示：

user_id: 2
body: monday_am tuesday_pm wednesday_pm thursday_am friday_allday 2013-03-03_all_day 2013-03-03_pm

然后，我将用户搜索查询转换为类似的格式。因此，如果用户正在寻找在 2013 年 3 月 19 日 - 全天和 2013 年 3 月 20 日 - PM 有空的人，我会将其转换为字符串。

首先，由于 3 月 19 日是星期二，我会将其转换为 tuesday_allday 日，与 20 日相同。因此，我最终会得到：

tuesday_allday wednesday_pm 2013-03-19_allday 2013-03-20_pm

然后，

我会对聚合表进行全文搜索，并返回一个"加权"结果集，然后我可以循环并进一步查询。

我

不确定这在实践中如何工作，所以这就是为什么我问是否有人有任何指向我可以使用的技术或相关文章的链接。

Answer 1

我相信

这个问题可以通过定义更完善的数据库模式来解决。通过使用更详细的数据库模式，您将能够在任何给定的时间范围内(不仅仅是am&pm(找到任何可用用户(如果您愿意的话(。它还允许您保留模板数据，同时不会用模板信息污染可用性数据(相反，您可以从模板表中进行选择，以编程方式填写给定日期的可用性，然后用户可以对其进行修改(。

我花了一些时间来绘制这个问题，并提出了一个架构结构，我相信它解决了您指定的问题，并允许您以最少的模式更改来扩展应用程序。(为了便于阅读，我在此建议答案的末尾添加了 SQL(

我还包含一个示例 select 语句，该语句允许您使用任意数量的参数提取可用性数据。为了清楚起见，SELECT 位于架构的 SQL 上方 @ 我的解释性文本的末尾。请不要被选择吓倒，乍一看可能看起来很复杂@，但实际上是整个架构的映射(保存模板表(。(顺便说一句，我并不是这么说，因为我怀疑你能理解它，我相信你可以，但我知道很多程序员忽略更复杂的数据库结构，对自己不利，因为它看起来过于复杂，但分析时实际上没有他们在程序中必须做的杂技那么复杂以获得类似的结果......关系数据库基于数学的一个分支，该分支是好的@准确，一致，并且(相对(简洁地关联数据(。

一般用途：(有关更多详细信息，请阅读 SQL CREATE TABLE 语句中的注释(- 填充星期几表。- 使用要跟踪的一些时间范围填充 TimeFrame 表(AM 时间帧的开始时间可能为 00：00：00，结束时间为 11：59：59，而 PM 的开始时间可能为 12：00：00，结束时间为 23：59：59(-添加用户- 添加要跟踪的日期(请参阅SQL中的注释，了解避免膨胀的想法以及此表的优点(- 填充每个用户的模板表- 为每个用户生成默认可用性列表(及其关联的可用时间数据(- 向用户公开默认可用性，以便他们可以覆盖默认值注意：您还可以为参与添加一个可选表，使其与可用性相反(或者也许有一个更好的抽象来包含这两个概念......免責聲明：我没有花额外的时间来完全填充我的本地数据库并验证所有内容，因此可能存在一些我在图表中没有看到的弱点/错误......(对不起，我在这方面花费的时间比预期的要长得多，并且必须在一个逾期的项目上完成工作(。虽然我已经相当广泛地使用数据库结构和其他人创建的数据库12 +年，但我确信我并非没有缺点，我希望StackOverflow上的其他人能够完成我可能包含的错误。

对于没有包含更多示例数据，我深表歉意。如果我在不久的将来有时间，我会提供一些(想想将乔治，弗雷德和哈里添加到用户表中，在日期表中添加一些日期，然后使用可用性，可用时间和时间框架表详细说明乔治和弗雷德在上学期间与哈利相比有多忙(。

SELECT 语句(注意：我强烈建议将其制作成视图...通过这种方式，您可以选择所需的任何列并在 WHERE 子句中添加所需的任何参数/条件，而不必每次都写出连接......所以该视图将不包括 WHERE 条款...只是为了说清楚(：

SELECT *
FROM Users Us
JOIN Availabilities Av
ON Us.User_ID=Av.User_ID
JOIN Dates Da
ON Av.Date_ID=Da.Date_ID
JOIN AvailableTimes Avt
ON Av.Av_ID=Avt.Av_ID
WHERE Da.Date='2014-01-03' -- whatever date 
-- alternately: WHERE Da.DayOWeek_ID=3 -- which would be Wednesday
-- WHERE Da.Date BETWEEN() -- whatever date range...
-- etc...

以每周天数为单位的推荐数据(实际上是查找表(：

INSERT INTO DaysOfWeek(DayOWeek_ID,Name,Description)
VALUES (1,'Sunday', 'First Day of the Week'),(1,'Monday', 'Second Day of the Week')...(7,'Saturday', 'Last Day of the Week'),(8,'AllWeek','The entire week'),(9,'Weekdays', 'Monday through Friday'),(10,'Weekends','Saturday & Sunday')

示例模板数据：

INSERT INTO Templates(Time_ID,User_ID,DayOWeek_ID)
VALUES (1,1,9)-- this would show the first user is available for the first time frame every weekday as their default... 
,(1,2,2) -- this would show the first user available on Tuesdays for the second time frame

以下是建议的架构结构：

CREATE  TABLE `test`.`Users` (

User_ID INT 不为空AUTO_INCREMENT， UserName 瓦尔查尔(45( 空 ， 主键 ( User_ID ( (;

CREATE  TABLE `test`.`Templates` (
  `Template_ID` INT NOT NULL AUTO_INCREMENT ,
  `Time_ID` INT NULL ,
  `User_ID` INT NULL ,
  `DayOWeek_ID` INT NULL ,
  PRIMARY KEY (`Template_ID`) )
`COMMENT = 'This table holds the template data for general expected availability of a user/agent/person (so the person would use this to set their general availability)'`;
CREATE  TABLE `test`.`Availabilities` (
  `Av_ID` INT NOT NULL AUTO_INCREMENT ,
  `User_ID` INT NULL ,
  `Date_ID` INT NULL ,
  PRIMARY KEY (`Av_ID`) )
COMMENT = 'This table holds a users actual availability for a particular date.nIf the use is not available for a date then this table has no entry for that user for that date.n(btw, this suggests the possiblity of an alternate table that could utilize all other structures except the templates called Engagements which would record when a user is actually busy... in order to use this table & the other table together would need to always join to AvailableTimes as a date would actually be in both tables but associated with different time frames).';
CREATE  TABLE `test`.`Dates` (
  `Date_ID` INT NOT NULL AUTO_INCREMENT ,
  `DayOWeek_ID` INT NULL ,
  `Date` DATE NULL ,
  PRIMARY KEY (`Date_ID`) )
COMMENT = 'This table is utilized to hold actual dates whith which users/agents can be associated.nThe important thing to note here is: this may end up holding every day of every year... this suggests a need to archive this data (and everything associated with it for performance reasons as this database is utilized).nOne more important detail... this is more efficient than associating actual dates directly with each user/agent with an availability on that date... this way the date is only recorded once, the other approach records this date with the user for each availability.';
 CREATE  TABLE `test`.`AvailableTimes` (
  `AvTime_ID` INT NOT NULL AUTO_INCREMENT ,
  `Av_ID` INT NULL ,
  `Time_ID` INT NULL ,
  PRIMARY KEY (`AvTime_ID`) )
COMMENT = 'This table records the time frames that a user is available on a particular date.nThis allows the time frames to be flexible without affecting the structure of the DB.n(e.g. if you only keep track of AM & PM at the beginning of the use of the DB but later decide to keep track on an hourly basis you simply add the hourly time frames & start populating them, no changes to the DB schema need to be made)';
CREATE  TABLE `test`.`TimeFrames` (
  `Time_ID` INT NOT NULL AUTO_INCREMENT ,
  `StartTime` TIME NOT NULL ,
  `EndTime` TIME NOT NULL ,
  `Name` VARCHAR(45) NOT NULL ,
  `Desc` VARCHAR(128) NULL ,
  PRIMARY KEY (`Time_ID`) ,
  UNIQUE INDEX `Name_UNIQUE` (`Name` ASC) )
COMMENT = 'Utilize this table to record the times that are being tracked.nThis allows the flexibility of having multiple time frames on the same day.nIt also provides the flexibility to change the time frames being tracked without changing the DB structure.';
CREATE  TABLE `test`.`DaysOfWeek` (
  `DaysOWeek_ID` INT NOT NULL AUTO_INCREMENT ,
  `Name` VARCHAR(45) NOT NULL ,
  `Description` VARCHAR(128) NULL ,
  PRIMARY KEY (`DaysOWeek_ID`) ,
  UNIQUE INDEX `Name_UNIQUE` (`Name` ASC) )
COMMENT = 'This table is a lookup table to hold the days of the week.nI personally would recommend adding a row for:nWeekends, All Week, & WeekDays nThis will often be used in conjunction with the templates and will allow less entries in that table to be made with those 3 entries in this table.';

Answer 2

好的，我会这样做：

• 在用户表中，为星期日、星期一创建字段...星期六。
• 对这些字段中的值使用 pm 、am 或 both。
• 您还应该为数据库中的每个字段编制索引，以便更快地进行查询。
• 然后为用户/日期/子午线字段创建一个单独的表(子午线表示 am 或 pm(。同样，子午线字段的值将是 pm 、 am 或 both 。
• 你需要对 php 的日期函数做一些研究，以提取星期几的数字，并可能使用它的 switch 语句。
• 使用请求的日期并提取星期几，并在用户表中查询其星期几的可用性。
然后使用请求的日期/子午线本身，并在新的用户/日期/子午
• 线表中查询用户的个人可用性日期/子午线。

我认为这里不会有太多的算法，除非在日期请求中提取一周中的几天。如果您正在做一个日期范围，那么你可以从算法中受益，但如果它只是一堆樱桃挑选的日期，那么你将不得不一个接一个地做。让我知道，也许我会给你扔一个算法。