PHP/MySQL约会/预订系统的最佳实践 [英] Best practice for PHP/MySQL Appointment/Booking system

问题描述

我需要一些人为我目前正在从事的美发师的PHP/MySQL约会系统争取最佳实践".希望我们可以一起清理一些事情，以避免以后再做该系统.我一直在寻找SO和Google的一些教程，最佳做法等.但是我还没有找到任何适合我需求的东西.

I need some people to battle a "best practice" for a PHP/MySQL appointment system for a hairdresser I'm currently working on. Hopefully, together we can clear some things up to avoid having to re-do the system afterwards. I've been looking at SO and Google for some tutorials, best practices etc. but I haven't found anything that fits the needs I have.

基本信息

每天有多个美发师，每个美发师都有自己的日程安排，其中包含与客户的约会.与美发师相连的表包含他/她一周中有空的时间.

There are multiple hairdressers available each day, each hairdresser has his/her own agenda containing his/her appointments with customers. Connected to a hairdresser is a table containing the times he/she is available during the week.

表:人

+----+------+-------+-----------+
| id | name | email | available |
+----+------+-------+-----------+
| 1  | John | i@a.c | Y         |
| 2  | Sara | c@i.a | N         |
+----+------+-------+-----------+

表:次(此表有一个主键，我在下面的时间表中将其遗漏了)

+-----------+-------------+-----------+-----------+
| people_id | day_of_week | start     | end       |
+-----------+-------------+-----------+-----------+
| 1         | 1           | 08:00     | 12:00     |
| 1         | 1           | 12:30     | 17:00     |
| 1         | 3           | 09:00     | 12:00     |
| 1         | 4           | 10:30     | 16:00     |
| 1         | 5           | 09:00     | 17:00     |
| 1         | 6           | 09:00     | 12:00     |
| 2         | 1           | 09:00     | 12:00     |
| 2         | 2           | 09:00     | 12:00     |
| 2         | 3           | 09:00     | 12:00     |
+-----------+-------------+-----------+-----------+

如您所见，人与时间之间存在一对多的关系(显然，因为一周中有7天).但是除此之外，还有一个选项可以在一天的时段之间添加休息时间(请参见people_id = 1，day_of_week = 1:08:00-12:00和12:30-17:00).

As you can see there is a one to many relationship between people and times (obviously, since there are 7 days in a week). But besides that there is also an option to add a break between the hours of a day (see people_id = 1, day_of_week = 1: 08:00-12:00 and 12:30-17:00).

此外，还有一个名为"hairdress_types"的表，该表包含一个人可以进行的各种约会(如染发，剪发，洗脸等).该表包含此约会花费的时间(分钟).

Furthermore there is a table called 'hairdress_types', this is a table containing the various types of appointments one can make (like coloring hair, cutting hair, washing, etc). This table contains the amount of time this appointment takes in minutes.

最后我有一张非常简单的表appointments:

At last I have a table appointments which is pretty simple:

id, people_id, types_id, sex, fullname, telephone, emailaddress, date_start, date_end

日期开始和日期结束将是MySQL中完整的DATETIME字段，这使得使用MySQL查询函数进行计算变得更加容易.

Date start and date end would be full DATETIME fields in MySQL making it easier to calculate using MySQL query functions.

因此，按照我的设置方式，前端用户会在触发ajax/jquery功能的字段中选择一个日期，该功能会找到在所选日期可用的所有美发师.然后，用户指定发型师的身份(这不是强制性的:用户还可以选择任何"发型师选项)以及他/她想要进行的约会的类型.

So, the way I set things up, a front-end user would select a date in a field triggering an ajax/jquery function that finds all hairdressers available at the choosen date. The user then specificies a hairdresser (this is not mandatory: a user can also choose to select 'Any' hairdresser option) and the type of appointment he/she wants to make.

提交第一份表格后，可以使用以下信息:

After submitting the first form, the following information can be used:

1. 日期(日，月和年)
2. people_id(对于"Any"，可以为0，如果选择了美发师，则为ID)
3. hairdress_type(与约会所花费的分钟数有关)

使用此信息，我将从选定的理发师中选择可用的日期或，我将循环所有可用的理发师及其可用的日期.

Using this information I would either select the available dates from the selected hairdresser OR I would loop al available hairdressers and their available dates.

这是我的思想崩溃的地方！因为什么是检查可用日期的最佳方法.我认为最好的方法是:

This is where my minds gets a mental breakdown! Because what is the best way to check the available dates. The way I thought would be the best was:

1. 查询给定日期的理发师时间(一次1个)
2. 使用查询1的结果的开始日期+约会类型花费的分钟数来查询约会表(因此:SELECT * FROM appointments WHERE ((date_start >= $start AND date_start <= ($start+$time)) OR (date_end > $start AND date_end <= ($start+$time)) AND people_id = 1)
3. 未找到任何结果，我认为该位置是免费的，并作为选项提供给用户

我面临的更大的问题是要点2:我对这个查询的想法真的发疯了，这是我需要查找匹配特定时间范围的约会的完整查询吗?

The biggers problem I'm facing is point 2: My mind is really going crazy on this query, is this the complete query I need to find appointments matching a specific timespan?

感谢您阅读&一起思考！ :-)

Thanks for reading & thinking along! :-)

John-星期一-12:00-17:00. 预约: -12:00-12:30 -14:30-15:30

John - Monday - 12:00 - 17:00. Appointments: - 12:00 - 12:30 - 14:30 - 15:30

用户想要约2个小时的约会，在上面的示例中，我将检查:

A user wants to have an appointment which takes 2 hours, in the exampel above I would check:

1. 在12:00和14:00之间有约会吗?是的，..前往下一个地点
2. 在14:00和16:00之间有约会吗?是的，..前往下一个地点
3. 在16:00和18:00之间有约会吗?错误，在17:00之后不可用

因此..使用10/15分钟的时间块"可能是一个更好的选择.进行检查:

Thus.. it might be a better option to use "timeblocks" of 10/15 minutes. Making the checks:

1. 12:00-14:00
2. 12:10-14:10
3. 12:20-14:20等.

这将在12:30到14:30之间找到可用的地点.

This would find the available spot between 12:30 and 14:30.

我一直在写一些纸上的东西(一张桌子，上面有约会和可能使用的空白点).我想出了以下几点.如果发生以下情况，则无法进行约会:

I've been working out some stuff on paper (a table with appointments and possible empty spots to use). And I came up with the following. An appointment can not be made in case:

1. 与start_date在$ start和$ end之间的约会
2. 在$ start和$ end之间有一个end_date的约会
3. 有一个约会，开始日期< $ start和end_date> $ end

将上面的内容与people_id一起查询到约会表中将导致没有行(=自由点)或一个/多个行，在这种情况下将获取该点.

Querying the above to the appointment table together with people_id would result in either no rows (= free spot) or one/multiple row(s) in which case the spot is taken.

我猜找到空位的最好方法是在数据库中查询X分钟的块，起始间隔为10分钟.这种解决方案的坏处是我每小时每小时要查询6个查询，每个美发师大约要查询48个查询...关于如何减少查询量的任何想法?

I guess the best way to find open spots is to query the database for blocks of X minutes with a start interval of 10 minutes. The bad side of this solution is that I would neet 6 queries for every hour, which would be about 48 queries for every hairdresser... Any ideas on how to reduce that amount of queries?

推荐答案

最后，我使用了一个为数据库中的开始日期和结束日期生成时间戳的系统.在检查时，我在开始时增加了1秒，在结束时减去了1秒，以避免约会重叠.

In the end I went for a system that generated timestamps for the start and end dates in the database. While checking I added one second to the start and subtracted one second from the end to avoid any overlapping time for appointments.

我最终要做什么

很明显，我不确定这是否是最佳实践，但是它确实对我有用.用户首先选择自己的性别和当天的偏好.这会发送AJAX请求，以获取可用人员和各种约会类型(例如，染发，剪发等).

Obviously I'm not sure this is the best practice, but it did work for me. The user starts by selecting their sex and a preference for the day. This sends an AJAX request to get the available personel and various kinds of appointment types (f.e. coloring hair, cutting hair, etc).

选择了所有设置(性别，日期，人员和类型)后，我将通过一些简单的验证开始:检查日期，检查date("N")是否不是7(星期日).如果一切正常，那么开始更重要的事情:

When all settings have been choosen (sex, date, personel and type) I start by some simple validations: checking the date, checking if the date("N") is not 7 (sunday). If everything is okay, the more important stuff is started:

1)从数据库中获取约会类型，包括该类型花费的总时间(30分钟，45分钟等) 2)提取可用人员(当天的完整列表，或者如果选择一个，则仅显示一个人)及其可用时间

1) The appointment type is fetched from the database including the total amount of time this type takes (30 minutes, 45 minutes, etc) 2) The available personal is fetched (a complete list of people on that day or just a single person if one is chosen) including their available times

人员(或一个人)然后从他们自己的开始时间开始循环播放.此时，我有一组包含以下内容的数据:

The personel (or one person) is then looped, starting with their own starting time. At this point I have a set of data containing:

$duration (of the appointment type)
$startTime (starting time of the person selected in the loop)
$endTime (= $startTime + $duration)
$personStart (= starting time of the person)
$personEnd (= end time of the person)

让我们看一下这个演示数据:

Let's take this demo data:

$duration = 30 min
$startTime = 9.00h
$endTime = 9.30h
$personStart = 9.00h
$personEnd = 12.00h

我在这里做的是:

while( $endTime < $personEnd )
{
    // Check the spot for availability
    $startTime = $endTime;
    $endTime = $startTime + $duration;
}

很明显，在这种情况下，它已经简化了.因为当我检查可用性时，现货不是免费的.我将$ startTime设置为等于找到的最新约会，然后从循环中的那里开始.

Obviously, it's al simplified in this case. Because when I check for availability, and the spot is not free. I set the $startTime to be equal to the latest appointment found and start from there in the loop.

示例:

I check for a free spot at 9.00 but the spot is not free because there's an appointment from 9.00 till 10.00, then 10.00 is returned and $startTime is set to 10.00h instead of 9.30h. This is done to keep the number of queries to a minimum since there can be quiet a lot.

检查可用性功能

// Check Availability
public static function checkAvailability($start, $end, $ape_id)
{
  // add one second to the start to avoid results showing up on the full hour
  $start += 1;
  // remove one second from the end to avoid results showing up on the full hour
  $end -= 1;

  // $start and $end are timestamps
  $getAppointments = PRegistry::getObject('db')->query("SELECT * FROM appointments WHERE
    ((
        app_start BETWEEN '".date("Y-m-d H:i:s", $start)."' AND '".date("Y-m-d H:i:s", $end)."' 
          OR
        app_end BETWEEN '".date("Y-m-d H:i:s", $start)."' AND '".date("Y-m-d H:i:s", $end)."'
      ) 
    OR
      (
    app_start < '".date("Y-m-d H:i:s", $start)."' AND app_end > '".date("Y-m-d H:i:s", $end)."'
     ))
    AND
     ape_id = ".PRegistry::getObject('db')->escape($ape_id));

    if(PRegistry::getObject('db')->num_rows($getAppointments) == 0) {
      return true;
    } else {
      $end = 0;
      foreach(PRegistry::getObject('db')->fetch_array(MYSQLI_ASSOC, $getAppointments, false) as $app) {
        if($app['app_end'] > $end) {
          $end = $app['app_end'];
            }
    }
    return $end;
     } 
}

由于我将约会存储为自:10.00，直到:11.00"，所以我必须确保检查从11:00:01到11:59:59的地点，否则，在11:00的约会将显示在结果.

Since I'm storing appointments as "From:10.00 Till:11.00" I have to make sure to check spots from 11:00:01 till 11:59:59, because otherwise the appointment at 11:00 will show in the results.

在函数的末尾，如果找到约会，我将循环结果并返回最新的末尾.这是我在上面提到的循环中的下一个起点.

At the end of the function, in case an appointment is found, I loop the results and return the latest end. This is the next start in the loop I mentioned above.

希望这对任何人都有帮助.就像信息一样:ape_id是与其链接的约会人员"的ID.

Hopefully this can be of any help to anyone. Just as info: ape_id is the ID of the "Appointment Person" it is linked with.

这篇关于PHP/MySQL约会/预订系统的最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！