Design Question

[Jamie Collins]

What is your solution to the prevention of overlapping dates in DDL SQL for this case?

What fully working solution would you provide to the OP?

I wouldn't encourage the OP or anyone else to seek a solution in a
newsgroup thread, it's simply not the forum for anything beyond hints,
general approaches, ideas, suggestions, proposals, etc.

My general approach would be, in a test environment, to add some 'bad'
data to the table, write a query to detect the 'bad' data, then remove
the bad data, incorporate the query into a CHECK constraint and try to
add the 'bad' data. If the CHECK constraint is bug free then 'bad'
data will be prevented from getting into the table.

I detailed an example a while back, here:

http://groups.google.com/group/microsoft.public.access.forms/msg/04c3f233ba3336cc

As a footnote to that, and knowing you like a Calendar table approach,
I'd replace the overlapping periods constraint with something like
this:

ALTER TABLE EarningsHistory ADD
CONSTRAINT earnings_history__no_overlapping_periods
CHECK (NOT EXISTS (
SELECT C1.dt
FROM EarningsHistory AS E1, Calendar AS C1
WHERE C1.dt BETWEEN E1.start_date AND IIF(E1.end_date IS NULL, NOW(),
E1.end_date)
GROUP BY C1.dt
HAVING COUNT(*) > 1
));

Jamie.

--

 

[Jamie Collins]

In practice, triggers have to be implemented as VBA code in Form objects in MS Access . .
. which I find to be, well, unpleasant to say the least.

That, or you go with unbound forms and have Class Modules containing all code that
accesses tables, which in turn are instantiated as an object on any form needing to access
the data via the object (that represents an entity, which in turn is represented by a
table) and have any required "additional logic" controlling the table be inside the Class
Module along with the rest of the data access code.

Another approach (more proactive than triggers which are reactive) is
to provide SQL procedures to achieve the task in hand. To use the
tables in this thread, the following SQL procedure (Access/Jet ANSI-92
Query Mode syntax) adds a row to the Devlivery_Status table
incorporates the test for overlapping periods:

CREATE PROCEDURE AddDevliveryStatus (
arg_delivery_id INTEGER,
arg_status_id INTEGER,
arg_start_date DATETIME,
arg_end_date DATETIME
)
AS
INSERT INTO Devlivery_Status (delivery_id,
status_id, start_date, end_date)
SELECT DISTINCT arg_delivery_id,
arg_status_id, arg_start_date, arg_end_date
FROM Statuses AS S1
WHERE NOT EXISTS
(
SELECT *
FROM
(
SELECT T3.delivery_id, T3.status_id, T3.start_date, T3.end_date
FROM Devlivery_Status AS T3
WHERE T3.delivery_id = arg_delivery_id
UNION ALL
SELECT DISTINCT arg_delivery_id,
arg_status_id, arg_start_date, arg_end_date
FROM Statuses AS S2
) AS T1,
(
SELECT T3.delivery_id, T3.status_id, T3.start_date, T3.end_date
FROM Devlivery_Status AS T3
WHERE T3.delivery_id = arg_delivery_id
UNION ALL
SELECT DISTINCT arg_delivery_id,
arg_status_id, arg_start_date, arg_end_date
FROM Statuses AS S2
) AS T2
WHERE T1.start_date < T2.start_date
AND T2.start_date <
IIF(IIF(T1.end_date IS NULL, NOW(), T1.end_date) > IIF(T2.end_date
IS NULL, NOW(), T2.end_date), IIF(T2.end_date IS NULL, NOW(),
T2.end_date), IIF(T1.end_date IS NULL, NOW(), T1.end_date))
)
;

Now that a means to add a rows has been provided the INSERT
permissions can be revoked from the base table.

Jamie.

--

 

[Michael Gramelspacher]

I wouldn't encourage the OP or anyone else to seek a solution in a
newsgroup thread, it's simply not the forum for anything beyond hints,
general approaches, ideas, suggestions, proposals, etc.

My general approach would be, in a test environment, to add some 'bad'
data to the table, write a query to detect the 'bad' data, then remove
the bad data, incorporate the query into a CHECK constraint and try to
add the 'bad' data. If the CHECK constraint is bug free then 'bad'
data will be prevented from getting into the table.

I detailed an example a while back, here:

http://groups.google.com/group/microsoft.public.access.forms/msg/04c3f233ba3336cc

As a footnote to that, and knowing you like a Calendar table approach,
I'd replace the overlapping periods constraint with something like
this:

ALTER TABLE EarningsHistory ADD
CONSTRAINT earnings_history__no_overlapping_periods
CHECK (NOT EXISTS (
SELECT C1.dt
FROM EarningsHistory AS E1, Calendar AS C1
WHERE C1.dt BETWEEN E1.start_date AND IIF(E1.end_date IS NULL, NOW(),
E1.end_date)
GROUP BY C1.dt
HAVING COUNT(*) > 1
));

Jamie.

--

Are you sure this constraint works? It works until you add a second employee.
How do I make the constraint apply by employee_id, not for the table as a
whole?

 

[Jamie Collins]

Are you sure this constraint works?

It works until you add a second employee.
How do I make the constraint apply by employee_id, not for the table as a
whole?

I suggest adding E1.employee_number to the GROUP BY clause.

Jamie.

--

 

[Michael Gramelspacher]

I suggest adding E1.employee_number to the GROUP BY clause.

Jamie.

given this table:

With CurrentProject.Connection
.Execute _
"CREATE TABLE EarningsHistory" & _
" (employee_id INTEGER NOT NULL," & _
" start_date datetime NOT NULL," & _
" end_date datetime," & _
" CHECK (start_date < end_date)," & _
" UNIQUE (employee_id, start_date)," & _
" UNIQUE (employee_id, end_date)," & _
" salary DECIMAL(12,2) NOT NULL," & _
" CONSTRAINT no_overlapping_periods" & _
" CHECK (NOT EXISTS" & _
" (SELECT C1.calendar_date" & _
" FROM EarningsHistory AS E1, Calendar AS C1" & _
" WHERE C1.calendar_date BETWEEN E1.start_date" & _
" AND IIF(E1.end_date IS NULL, NOW(),E1.end_date)" & _
" GROUP BY E1.employee_id,C1.calendar_date" & _
" HAVING COUNT(*) > 1)));"
End With

and assuming there is a calendar table with column calendat_date,
will this work? Or is this not the same as your constraint?

 

[Jamie Collins]

given this table:

With CurrentProject.Connection
.Execute _
"CREATE TABLE EarningsHistory" & _
" (employee_id INTEGER NOT NULL," & _
" start_date datetime NOT NULL," & _
" end_date datetime," & _
" CHECK (start_date < end_date)," & _
" UNIQUE (employee_id, start_date)," & _
" UNIQUE (employee_id, end_date)," & _
" salary DECIMAL(12,2) NOT NULL," & _
" CONSTRAINT no_overlapping_periods" & _
" CHECK (NOT EXISTS" & _
" (SELECT C1.calendar_date" & _
" FROM EarningsHistory AS E1, Calendar AS C1" & _
" WHERE C1.calendar_date BETWEEN E1.start_date" & _
" AND IIF(E1.end_date IS NULL, NOW(),E1.end_date)" & _
" GROUP BY E1.employee_id,C1.calendar_date" & _
" HAVING COUNT(*) > 1)));"
End With

and assuming there is a calendar table with column calendat_date,
will this work?

I'm perplexed as to why you are asking me this but I'll play with a
straight bat and say, "I don't know. Shall we test it?"

First, note that a typical Calendar table has one row per day. If we
are using it in the EarningsHistory table constraint to test periods,
the smallest granule time for those periods must be one day (and
further note we've gone a little OT because this does not fit the OP's
case, where the smallest time granule is less than one day). I would
be happier if there were constraints to ensure this is indeed the case
e.g. the following assumes closed-closed representation of periods:

ALTER TABLE EarningsHistory ADD
CONSTRAINT start_date__granule
CHECK
(
DATEPART('H', start_date) = 0
AND DATEPART('N', start _date) = 0
AND DATEPART('S', start _date) = 0
);

ALTER TABLE EarningsHistory ADD
CONSTRAINT end_date__granule
CHECK
(
DATEPART('H', end_date) = 23
AND DATEPART('N', end_date) = 59
AND DATEPART('S', end_date) = 59
);

Next, populate the table with some base data:

INSERT INTO EarningsHistory (employee_id, start_date, end_date,
salary) VALUES
(1, #1990-01-03 00:00:00#, #1990-01-06 23:59:59#, 100)
;
INSERT INTO EarningsHistory (employee_id, start_date, end_date,
salary) VALUES
(2, #1990-01-03 00:00:00#, NULL, 200)
;

The way I see it, the row where employee_id = 1 would be overlapped
by

1) a row with an end_date after its start date
2) a row with a start_date before its end date

Similarly, the row where employee_id = 2 would be overlapped by

1) a row with an end_date after its start date
2) a row with a start_date before the current timestamp.

Therefore, if the constraint is doing its job, the following additions
should cause the constraint to bite:

INSERT INTO EarningsHistory (employee_id, start_date, end_date,
salary) VALUES
(1, #1990-01-01 00:00:00#, #1990-01-04 23:59:59#, 30)
;
INSERT INTO EarningsHistory (employee_id, start_date, end_date,
salary) VALUES
(1, #1990-01-05 00:00:00#, #1990-01-08 23:59:59#, 300)
;
INSERT INTO EarningsHistory (employee_id, start_date, end_date,
salary) VALUES
(2, #1990-01-01 00:00:00#, #1990-01-04 23:59:59#, 20)
;
INSERT INTO EarningsHistory (employee_id, start_date, end_date,
salary) VALUES
(2, #1990-01-05 00:00:00#, #1990-01-08 23:59:59#, 600)
;

In my testing (VBA code below), the CHECK does indeed bite for each.

However, to be working correctly, the CHECK should allow non-
overlapping data to be added. To this end, the following are
contiguous periods which should NOT cause the CHECK to bite:

INSERT INTO EarningsHistory (employee_id, start_date, end_date,
salary) VALUES
(1, #1990-01-01 00:00:00#, #1990-01-02 23:59:59#, 15);
INSERT INTO EarningsHistory (employee_id, start_date, end_date,
salary) VALUES
(1, #1990-01-07 00:00:00#, #1990-01-08 23:59:59#, 115);
INSERT INTO EarningsHistory (employee_id, start_date, end_date,
salary) VALUES
(2, #1990-01-01 00:00:00#, #1990-01-02 23:59:59#, 25);

Again, this works for me as expected.

Here's the full VBA. As written, you have to manually step over the
expected INSERT failures. Note that because the current timestamp is
beyond the limits of the very limited example Calendar table, I've
replaced NOW() with a lookup of the largest date in the Calendar
table:

Sub TestCon()
On Error Resume Next
Kill Environ$("temp") & "\DropMe.mdb"
On Error GoTo 0
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
..Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
Dim Sql As String
Sql = _
"CREATE TABLE Calendar (calendar_date" & _
" DATETIME NOT NULL PRIMARY KEY);"
..Execute Sql
Sql = _
"INSERT INTO Calendar (calendar_date)" & _
" VALUES (#1990-01-01 00:00:00#);"
..Execute Sql
Sql = _
"INSERT INTO Calendar (calendar_date)" & _
" VALUES (#1990-01-02 00:00:00#);"
..Execute Sql
Sql = _
"INSERT INTO Calendar (calendar_date)" & _
" VALUES (#1990-01-03 00:00:00#);"
..Execute Sql
Sql = _
"INSERT INTO Calendar (calendar_date)" & _
" VALUES (#1990-01-04 00:00:00#);"
..Execute Sql
Sql = _
"INSERT INTO Calendar (calendar_date)" & _
" VALUES (#1990-01-05 00:00:00#);"
..Execute Sql
Sql = _
"INSERT INTO Calendar (calendar_date)" & _
" VALUES (#1990-01-06 00:00:00#);"
..Execute Sql
Sql = _
"INSERT INTO Calendar (calendar_date)" & _
" VALUES (#1990-01-07 00:00:00#);"
..Execute Sql
Sql = _
"INSERT INTO Calendar (calendar_date)" & _
" VALUES (#1990-01-08 00:00:00#);"
..Execute Sql
Sql = _
"INSERT INTO Calendar (calendar_date)" & _
" VALUES (#1990-01-09 00:00:00#);"
..Execute Sql
Sql = _
"CREATE TABLE EarningsHistory (employee_id" & _
" INTEGER NOT NULL, start_date" & _
" datetime NOT NULL, end_date datetime," & _
" CHECK (start_date < end_date)," & _
" UNIQUE (employee_id, start_date)," & _
" UNIQUE (employee_id, end_date)," & _
" salary DECIMAL(12,2) NOT NULL," & _
" CONSTRAINT no_overlapping_periods" & _
" CHECK (NOT EXISTS (SELECT C1.calendar_date" & _
" FROM EarningsHistory AS E1, Calendar" & _
" AS C1 WHERE C1.calendar_date" & _
" BETWEEN E1.start_date AND IIF(E1.end_date" & _
" IS NULL, (SELECT MAX(C2.calendar_date)" & _
" FROM Calendar AS C2) ,E1.end_date)" & _
" GROUP BY E1.employee_id,C1.calendar_date" & _
" HAVING COUNT(*) > 1)));"
..Execute Sql
Sql = _
"ALTER TABLE EarningsHistory ADD" & _
" CONSTRAINT start_date__granule" & _
" CHECK (DATEPART('H', start_date)" & _
" = 0 AND DATEPART('N', start_date)" & _
" = 0 AND DATEPART('S', start_date)" & _
" = 0)"
..Execute Sql
Sql = _
"ALTER TABLE EarningsHistory ADD" & _
" CONSTRAINT end_date__granule" & _
" CHECK (DATEPART('H', end_date)" & _
" = 23 AND DATEPART('N', end_date)" & _
" = 59 AND DATEPART('S', end_date)" & _
" = 59)"
..Execute Sql

' Starting point:
Sql = _
"INSERT INTO EarningsHistory (employee_id," & _
" start_date, end_date, salary)" & _
" VALUES (1, #1990-01-03 00:00:00#," & _
" #1990-01-06 23:59:59#, 100);"
..Execute Sql
Sql = _
"INSERT INTO EarningsHistory (employee_id," & _
" start_date, end_date, salary)" & _
" VALUES (2, #1990-01-03 00:00:00#," & _
" NULL, 200);"
..Execute Sql

' The following rows are overlaps and
' should cause the CHECK to bite:
Sql = _
"INSERT INTO EarningsHistory (employee_id," & _
" start_date, end_date, salary)" & _
" VALUES (1, #1990-01-01 00:00:00#," & _
" #1990-01-04 23:59:59#, 30);"
..Execute Sql
Sql = _
"INSERT INTO EarningsHistory (employee_id," & _
" start_date, end_date, salary)" & _
" VALUES (1, #1990-01-05 00:00:00#," & _
" #1990-01-08 23:59:59#, 300);"
..Execute Sql
Sql = _
"INSERT INTO EarningsHistory (employee_id," & _
" start_date, end_date, salary)" & _
" VALUES (2, #1990-01-01 00:00:00#," & _
" #1990-01-04 23:59:59#, 20);"
..Execute Sql
Sql = _
"INSERT INTO EarningsHistory (employee_id," & _
" start_date, end_date, salary)" & _
" VALUES (2, #1990-01-05 00:00:00#," & _
" #1990-01-08 23:59:59#, 600);"
..Execute Sql

' The following rows are continguous
' (the closest legal thing to an overlap)
' and should NOT cause the CHECK to bite:
Sql = _
"INSERT INTO EarningsHistory (employee_id," & _
" start_date, end_date, salary)" & _
" VALUES (1, #1990-01-01 00:00:00#," & _
" #1990-01-02 23:59:59#, 15);"
..Execute Sql
Sql = _
"INSERT INTO EarningsHistory (employee_id," & _
" start_date, end_date, salary)" & _
" VALUES (1, #1990-01-07 00:00:00#," & _
" #1990-01-08 23:59:59#, 115);"
..Execute Sql
Sql = _
"INSERT INTO EarningsHistory (employee_id," & _
" start_date, end_date, salary)" & _
" VALUES (2, #1990-01-01 00:00:00#," & _
" #1990-01-02 23:59:59#, 25);"
..Execute Sql
End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--

 

[Michael Gramelspacher]

I'm perplexed as to why you are asking me this but I'll play with a
straight bat and say, "I don't know. Shall we test it?"

First, note that a typical Calendar table has one row per day. If we
are using it in the EarningsHistory table constraint to test periods,
the smallest granule time for those periods must be one day (and
further note we've gone a little OT because this does not fit the OP's
case, where the smallest time granule is less than one day). I would
be happier if there were constraints to ensure this is indeed the case
e.g. the following assumes closed-closed representation of periods:

ALTER TABLE EarningsHistory ADD
CONSTRAINT start_date__granule
CHECK
(
DATEPART('H', start_date) = 0
AND DATEPART('N', start _date) = 0
AND DATEPART('S', start _date) = 0
);

ALTER TABLE EarningsHistory ADD
CONSTRAINT end_date__granule
CHECK
(

Jamie, this works as advertised. Problem was entirely on my end. Thenks for
the clarification.