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

背景

简而言之，我正在构建一个应用程序，该应用程序允许用户在任何给定的一天内设置其“可用性”。用户首先设置一个通用可用性模板，该模板允许他们说:

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

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

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

最佳答案

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

我花了一些时间来图解这个问题，并提出了一个架构结构，我相信它可以解决您指定的问题，并允许您以最少的架构更改来发展您的应用程序。
(为使阅读更容易，我在建议的答案末尾添加了SQL)

我还提供了一个示例select语句示例，该语句允许您使用任意数量的参数提取可用性数据。
为了清楚起见，在我的解释性文本的末尾，SELECT位于模式上方的SQL的上方。
请不要被选择所吓倒，它乍看起来可能很复杂，但实际上它是对整个架构的映射(保存模板表)。
(顺便说一句，我并不是说，因为我怀疑您是否可以理解它，我敢肯定，但是我知道很多程序员都忽略了更复杂的数据库结构，这对他们自己不利，因为它看起来过于复杂，但是当进行分析时，实际上它们不如他们在程序中进行杂技操作才能获得相似的结果...关系数据库基于a branch of mathematics，它是@准确，一致且(相对)简洁地关联数据)。

一般用途:
(有关更多详细信息，请阅读SQL CREATE TABLE语句中的注释)
-填充DaysOfWeek表。
-使用要跟踪的一些时间帧填充TimeFrames表(AM时间帧的StartTime可以为00:00:00，结束时间为11:59:59，而PM的StartTime可以为12:00:00＆EndTime之23:59:59)
-添加用户
-添加要跟踪的日期(有关避免过大膨胀的想法以及此表的优点，请参阅SQL中的注释)
-为每个用户填充模板表
-为每个用户生成默认可用性列表(及其关联的AvailableTimes数据)
-向用户公开默认功能，以便他们可以覆盖默认设置
注意:您还可以为“参与度”添加一个可选的表，以使其与“可用性”相反(或者可能会有更好的抽象，包括两个概念……)
免责声明:我没有花额外的时间来完全填充我的本地数据库并验证所有内容，因此可能存在一些我在图表中没有看到的弱点/错误...(对不起，我花的时间比预期的要长得多，并且必须完成逾期未完成的项目的工作)。
尽管我在数据库结构和与其他人已经创建12年以上的数据库中进行了相当广泛的工作，但我确信我并非没有错，但我希望StackOverflow上的其他人能够解决我可能包括的错误。

对于不包含更多示例数据，我深表歉意。
如果我在不久的将来有时间，我会提供一些信息((例如，将George，Fred和Harry添加到users表中，在Dates表中添加一些日期，然后详细说明George＆Fred在上学期间与Harry相比他们有多忙)可用性，AvailableTimes和TimeFrames表)。

SELECT语句(注意:我强烈建议将其变成view ...这样，您可以选择所需的任何列，并在WHERE子句中添加所需的任何参数/条件，而不必编写每个连接时间...所以该 View 将不包括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` (

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.';

关于php - 计划在PHP/MySQL中使用模板和替代内容进行搜索，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/15483547/