Comment la structure et la requête d'un système de nomination fondé sur les créneaux horaires où il est possible de réserver chaque entité a un autre moment de la table de tous les jours?

Je suis le développement d'un avocat système de réservation, où une personne peut réserver un rendez-vous à un moment donné, un jour donné (la prochaine avocat disponible de jour).

Disons que c'est un ZocDoc pour les avocats. La même structure, avec des nominations fondées sur le temps: http://goo.gl/djUZb

Je suis de l'utilisation de MySQL et PHP.

---

Le schéma de la table:

CREATE TABLE `laywer_appointments` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `lawyer_id` INT unsigned,
  `day_of_week` tinyint(3) unsigned DEFAULT '1',
  `slot_date` date DEFAULT NULL,
  `slot_time` time DEFAULT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '0',
  `client_id` int(11) DEFAULT NULL, -- client_id = NULL means free slot
);

---

Point 1)

Chaque avocat a défaut de créneaux horaires en fonction du jour de la semaine (statut = 0 les moyens disponibles).
Lors de l'insertion par défaut slots, je n'ai pas de date, juste day_of_week. Exemple de données:

+-----------+-------------+-----------+-----------+
| lawyer_id | day_of_week | slot_time | status    |
+-----------+-------------+-----------+-----------+
| 1         | 1           | 08:00     | 0         |
| 1         | 1           | 08:30     | 0         |
| 1         | 1           | 09:00     | 0         |
| 1         | 1           | 10:30     | 0         |
| 1         | 4           | 14:30     | 0         |
| 1         | 4           | 16:40     | 0         |
| 2         | 1           | 10:20     | 0         |
| 2         | 1           | 14:00     | 0         |
| 2         | 3           | 15:50     | 0         |
+-----------+-------------+-----------+-----------+

Point 2)

Un avocat peut ajouter un intervalle de temps à un jour spécifique (même si ce jour est un jour différent de la semaine du son par défaut fentes) et peut également verrouiller (statut = -1) l'un des emplacements par défaut dans une journée spécifique (c'est à dire qu'il est sur une réunion ou qu'il est malade):

+-----------+-------------+-----------+-----------+-----------+
| lawyer_id | day_of_week | slot_time | slot_date | status    |
+-----------+-------------+-----------+-----------+-----------+
| 1         | 1           | 16:00     | 12/03/13  | 0         |
| 1         | 6           | 11:00     | 26/04/13  | 0         |
| 1         | 6           | 12:00     | 26/04/13  | 0         |
| 2         | 1           | 10:00     | 01/01/13  | -1        |
+-----------+-------------+-----------+-----------+-----------+

Point 3)

Puis nous avons rendez-vous réservé. Dans ce cas, nous remplir le slot_date et le client_id:

+-----------+-------------+-----------+-----------+-----------+
| lawyer_id | day_of_week | slot_time | slot_date | client_id |
+-----------+-------------+-----------+-----------+-----------+
| 1         | 1           | 10:30     | 12/03/13  | 10        |
+-----------+-------------+-----------+-----------+-----------+

Comme un exemple, avec le ci-dessus de réservation et en supposant qu'il est encore à 6:30 du même jour (12/03/13), la libre disposition des emplacements qui doivent être imprimés:

8:00 - default slot
8:30 - default slot
9:00 - default slot
16:00 - Specific slot inserted in point 2 for 12/03/13

---

Le problème:

J'ai du renvoyer la prochaine date disponible et le temps libre (ceux par défaut, celles qui le sont moins verrouillé et réservé à ceux). Je ne peux pas juste dire "temps de retour à partir de lundi, 10/10/13".

Dans une page de résultats de recherche, je vais la liste de tous les avocats et le délai de mise à disposition de table pour chaque. Ce qui signifie que chaque avocat aura un autre moment de la table chaque fois qu'une recherche est effectuée.

Je ne peux pas simplement dire "SÉLECTIONNEZ le temps DE [tas de jointures] OÙ date = aujourd'hui".

Je suis venu avec cette requête qui ignore les machines à sous qui sont verrouillés (statut = -1) ou réservé (client_id pas nulle), mais bien sûr, il ne retourne pas le temps libre le plus proche à jour avec les horaires disponibles (ou à partir d'aujourd'hui):

SELECT p.day_of_week, p.slot_date, p.slot_time
FROM laywer_appointments p
WHERE p.client_id IS NULL AND p.status = 0
     AND p.slot_time NOT IN (
              SELECT s.slot_time FROM laywer_appointments s
              WHERE (s.slot_date IS NOT NULL AND s.client_id IS NOT NULL 
              OR s.status = -1) AND s.day_of_week = p.day_of_week
     )
GROUP BY p.day_of_week, p.slot_date, p.slot_time
ORDER BY p.day_of_week ASC, p.slot_time ASC;

Un autre problème: si, aujourd'hui, est day_of_week = 5, mais la prochaine day_of_week pour un avocat est de 2, comment pouvez-j'ai une requête qui?

Comment retourner le plus proche et disponible day_of_week et totalisent seulement des temps de retour à partir de ce jour, tous les jours?

Une solution possible

Une chose que je suis venu avec était de créer 3 tables au lieu d'une:

• default_slots: 3 colonnes: lawyer_id, day_of_week, le temps
• fentes: laywer_id, day_of_week, l'heure, la date, le statut de
• rendez-vous: toutes les infos au sujet d'un rendez-vous

Alors, je vais stocker TOUTES gratuit des plages horaires pour chaque jour de la date effective jusqu'à un an dans les fentes de table pour chaque avocat. (pris le temps de fentes de default_slots).

+-----------+-------------+-----------+-----------+-----------+
| lawyer_id | day_of_week | slot_time | slot_date | status    |
+-----------+-------------+-----------+-----------+-----------+
| 1         | 1           | 16:00     | 12/03/13  | 0         |
| 1         | 1           | 16:00     | 12/03/13  | 0         |
| 1         | 2           | 08:00     | 13/03/13  | 0         |
| 1         | 2           | 09:00     | 13/03/13  | 0         |
... next week
| 1         | 1           | 16:00     | 19/03/13  | 0         |
| 1         | 1           | 16:00     | 19/03/13  | 0         |
| 1         | 2           | 08:00     | 20/03/13  | 0         |
| 1         | 2           | 09:00     | 20/03/13  | 0         |
... up to an year
| 1         | 1           | 16:00     | 20/03/14  | 0         |
| 1         | 1           | 16:00     | 20/03/14  | 0         |
| 1         | 2           | 08:00     | 21/03/14  | 0         |
| 1         | 2           | 09:00     | 21/03/14  | 0         |
+-----------+-------------+-----------+-----------+-----------+

Je vais aussi avoir certaines tâches cron qui exécute toutes les semaines qui ajoute une autre semaine de slot libre enregistrements dans la table slots et également supprimer les anciens enregistrements pour réduire la taille de la table et les données non utilisées.

Un avocat sera également en mesure de verrouiller un temps directement dans les fentes, aussi bien ajouter des moments précis (point 2).

Pour l'inscription, il sera d'obtenir des créneaux horaires pour une date égale ou supérieure à celle d'aujourd'hui avec de temps libre, car à chaque moment de chaque jour aura une ligne.

Implications sur cette solution:
1) le premier Jour, nous aurons 2500 avocats (2e mois environ 6000). En supposant que 8 slots/jour X 20 jours de travail/mois X 12 mois = 1920 fente enregistrements par avocat.

2500 laywers x 1920 dossiers = 4,8 millions d'enregistrements sur une journée. (~12M, le deuxième mois)

Ces documents seront mis à JOUR, INSERTED et DELETED tout le temps. Les fentes de table a certains indices, donc je ne peux pas imaginer les opérations d'écriture réalisés en permanence sur une table avec 12M+ enregistrements et de certains indices. Indices des cours de mise à jour à chaque seconde ne voit pas intelligent pour moi.

Je ne peux vraiment pas venir avec une solution évolutive et. Ma solution avec une table seulement pourrait fonctionner, mais je ne peux pas penser à un moyen de l'interrogation que tout.
Et le dénormalisée fentes de table va être énorme, tout en ayant besoin constant des opérations d'écriture.

Des conseils?