How to ensure non overlapping startDate and endDate columns in database?-CodePudding

The following table stores price of a product for different date ranges

id
productID
startDate
endDate
price

How to ensure at database level that there are no multiple entries for a given date. (MongoDB / MySQL)

Correct SET

startDate     endDate
2020-01-01    2020-01-05
2020-01-07    2020-01-07
2020-01-08    2020-01-20

INCORRECT SET

There are two entries for date 2020-01-04 (first and second)

startDate     endDate
2020-01-01    2020-01-05
2020-01-04    2020-01-07
2020-01-08    2020-01-20

CodePudding user response：

CREATE TRIGGER check_for_overlapping
BEFORE INSERT
ON prices
FOR EACH ROW
BEGIN
    IF EXISTS ( SELECT NULL
                FROM prices
                WHERE prices.productID = NEW.productID
                  AND prices.startDate <= NEW.EndDate
                  AND NEW.startDate <= prices.EndDate ) THEN
        SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = 'Entered data overlaps with existing data';
    END IF;
END;

DEMO

CodePudding user response：

Change the schema.

Seriously. You have redundant info. And missing info (cf '2020-01-6').

Instead of having a start and end, have only a start. (Equivalently, you could have only an "end" date.)

The "end" of one entry is given by the "start" of the next row. (See example below)

Since you seem to have unassigned days; were the 'products' unavailable during that time (eg, 2020-01-06)? If that is valid business logic, add a row that somehow indicates such.

For IP-address ranges, I did the above. (And gaps are valid -- they refer to "unassigned" IPs.) See http://mysql.rjweb.org/doc.php/ipranges . It includes code for many operations (insert, lookup, modify, etc) that would need to be adapted from "ip-address" to "date".

As Akina points out, the onus is on the insertion code to make sure that the data lives up to the business-logic rules.

Does 2020-01-07 2020-01-07 in your example mean "all of the 7th"? If so then, it is logically >= '2020-01-07' AND < '2020-01-08'. That works with my IP analogy, etc.

Note that I chose to have stored routines for all actions. This avoids the need to "execute a validation query after every operation"