Which approach is better in terms of database performance - one table group by operations or multiple tables serializable transaction?

Question

I'm developing a reservation system based on an SQL database (no limitations on the specific DB).

Schema:

Workflow:

Now I'm using a special 'actual_reservations' view based on a cusom group-by with filtration logic to identify actually placed reservations like:

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`;

And to determine whether there are any overlapping with other reservations with procedure like this:

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 < ENDS_AT
              AND r.reservation_end > STARTS_AT
              AND r.id <> RESERVATION)
      THEN TRUE
           ELSE FALSE END
    INTO RESULT;
  END$$

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.

My question is about which approach from your POV is better in terms of database performance, scalability and good data design?

Which approach is better in terms of database performance - one table group by operations or multiple tables serializable transaction?

2 Answers