title: Which approach is better in terms of database performance - one table group by operations or multiple tables serializable transaction?
body: <p>I'm developing a reservation system based on an SQL database (no limitations on the specific DB). </p>
  
  <p><strong>Schema:</strong><br>
  <a href="https://i.stack.imgur.com/f8P7Y.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/f8P7Y.png" alt="enter image description here"></a>  </p>
  
  <p><strong>Workflow:</strong><br>
  <a href="https://i.stack.imgur.com/Yaea7.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Yaea7.png" alt="enter image description here"></a>  </p>
  
  <p>Now I'm using a special 'actual_reservations' view based on a cusom group-by with filtration logic to identify actually placed reservations like:</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>CREATE VIEW `reserv-io`.`actual_reserved_resources` AS
    SELECT
      `r`.`id`,
      `a`.`resource_id`,
      `a`.`type_id`,
      `a`.`status_id`,
      `a`.`reservation_start`,
      `a`.`reservation_end`
    FROM `reserv-io`.`actions` `a`
      JOIN (SELECT
              `r`.`id`      `id`,
              MAX(`a`.`id`) `action_id`
            FROM `reserv-io`.`reservations` `r`
              JOIN `reserv-io`.`actions` `a` ON `r`.`id` = `a`.`reservation_id`
            WHERE (`r`.`id` NOT IN
                   (SELECT `r`.`id` `id`
                    FROM `reserv-io`.`reservations` `r`
                      JOIN `reserv-io`.`actions` `a` ON `r`.`id` = `a`.`reservation_id`
                    WHERE (`a`.`status_id` IN
                           (SELECT `id`
                            FROM `reserv-io`.`reservation_statuses`
                            WHERE `name` = 'Canceled')))
                   AND `a`.`status_id` IN
                       (SELECT `id`
                        FROM `reserv-io`.`reservation_statuses`
                        WHERE `name` = 'Approved' OR `name` = 'Accepted'))
            GROUP BY `r`.`id`) `b`
        ON `a`.`id` = `b`.`action_id`
      JOIN `reserv-io`.`reservations` `r` ON `r`.`id` = `a`.`reservation_id`;</code></pre>
  </div>
  </div>
  </p>
  
  <p>And to determine whether there are any overlapping with other reservations with procedure like this:</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>CREATE PROCEDURE HAS_OVERLAPPING_RESERVATION_WITH_TYPE_SELF_CHECK(
    IN  RESERVATION      BIGINT,
    IN  RESOURCE         INT,
    IN  RESERVATION_TYPE INT,
    IN  STARTS_AT        DATETIME,
    IN  ENDS_AT          DATETIME,
    OUT RESULT           BIT)
    BEGIN
      SELECT CASE WHEN EXISTS(
          SELECT *
          FROM actual_reserved_resources r
          WHERE r.resource_id = RESOURCE
                AND r.type_id = RESERVATION_TYPE
                AND r.reservation_start &lt; ENDS_AT
                AND r.reservation_end &gt; STARTS_AT
                AND r.id &lt;&gt; RESERVATION)
        THEN TRUE
             ELSE FALSE END
      INTO RESULT;
    END$$</code></pre>
  </div>
  </div>
  </p>
  
  <p>I already have good indexes on the 'actions' table, but I'm not sure that my approach with single table for all actions related to the reservation is really scalable. I've heard about approach where I store all pending and actually approved reservations into separate tables, and synchronize them manually into transaction.</p>
  
  <p>My question is about which approach from your POV is better in terms of database performance, scalability and good data design?</p>