0

I'm designing a schema which includes the concept of availability rules. The rules are periods of a day when a linked entity is available. This can be the opening hours of a business, the working hours of a staff member, or the availability hours of a specific service. This schema needs to allow for multiple periods on the same day. The rules aren't shared across entities, a business has one group of rules, and each of the other entities are the same.

My first run through this, gave me this schema, but the fact that availabilityGroup doesn't actually contain any data makes me feel like this could be done differently.

CREATE TABLE `business` (
  `id` integer PRIMARY KEY,
  `name` varchar(255),
  `availabilityId` integer,
  CONSTRAINT `FK_business_to_availability_group` FOREIGN KEY (`availabilityId`) REFERENCES `availabilityGroup` (`id`)
);

CREATE TABLE `staff` (
  `id` integer PRIMARY KEY,
  `availabilityId` integer,
  `name` varchar(255),
  CONSTRAINT `FK_staff_to_availability_group` FOREIGN KEY (`availabilityId`) REFERENCES `availabilityGroup` (`id`)
);


CREATE TABLE `service` (
  `id` integer PRIMARY KEY,
  `availabilityId` integer,
  `name` varchar(255),
  CONSTRAINT `FK_service_to_availability_group` FOREIGN KEY (`availabilityId`) REFERENCES `availabilityGroup` (`id`)
);

CREATE TABLE `availabilityGroup` (
  `id` integer PRIMARY KEY
);

CREATE TABLE `availabilityRule` (
  `id` integer PRIMARY KEY,
  `availabilityGroupId` integer,
  `day` string,
  `startTime` time,
  `endTime` time,
    CONSTRAINT `FK_availability_rule_to_availability_group` FOREIGN KEY (`availabilityGroupId`) REFERENCES `availabilityGroup` (`id`)
);

Another possible solution could be to make use of single table inheritance with something like this, which is supported by the ORM we're using too https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/reference/inheritance-mapping.html#single-table-inheritance

CREATE TABLE `singleAvailabilityRulesTable` (
  `id` integer PRIMARY KEY,
  `discriminator` enum('business','staff','service'),
  `entityId` integer,
  `day` string,
  `startTime` time,
  `endTime` time
);

Any thoughts?

2
  • Are you tracking availability by generic day of week, a specific date, or both?
    – user212533
    Commented Dec 14, 2020 at 17:23
  • Generic day of week
    – robjmills
    Commented Dec 14, 2020 at 19:01

1 Answer 1

0

It's not worth trying to cram everything into one generic table. Better to just build off each entity like so:

CREATE TABLE business
(
  business_id  INT           NOT NULL
 ,name         VARCHAR(255)  NOT NULL
 ,CONSTRAINT PK_business PRIMARY KEY (business_id)
 ,CONSTRAINT AK_business UNIQUE (name)
)
;

CREATE TABLE business_hours_of_operation
(
  business_id  INT              NOT NULL
 ,day_of_week  TINYINT          NOT NULL
 ,start_hour   DECIMAL(4,2)     NOT NULL /* Expressed as fractional - i.e. 12:30 = 12.5 */
 ,hours_open   DECIMAL(5,2)     NOT NULL
 ,CONSTRAINT FK_Hours_Of_Operation_For_Business FOREIGN KEY (business_id) REFERENCES business (business_id)
 ,CONSTRAINT PK_Business_Hours_Of_Operation PRIMARY KEY (business_id, day_of_week, start_hour)
 ,CONSTRAINT CK_Business_Hours_Of_Operation_Day_Of_Week_Is_Valid CHECK(day_of_week >= 1 AND day_of_week <= 7)
 ,CONSTRAINT CK_Business_Hours_Of_Operation_Start_Hour_Is_Valid CHECK(start_hour >= 0.00 AND start_hour < 24.00)
 ,CONSTRAINT CK_Business_Hours_Of_Operation_Hours_Open_Is_Valid CHECK(hours_open > 0.00 AND hours_open <= 168.00)
)
;

To display everything in intervals:

SELECT
  business_id
 ,day_of_week AS start_day_of_week
 ,hour AS start_hour
 ,1 + MOD(day_of_week - 1 + FLOOR((hour + hours_open)/24),7) AS end_day_of_week
 ,MOD(hour + hours_open,24) AS end_hour
FROM
  business_hours_of_operation
;

You'll need to lock things down with stored procedures and/or triggers to ensure that the intervals don't overlap and any other business rules.

See Fiddle here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.