If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Design Question
On Jul 17, 5:46 am, "Chris2"
wrote: Your proposed table wants a sequenced 'primary key' e.g. 12, 2, 4, 07/14/2007 10:30 AM, 07/14/2007 10:45 AM creates an overlapping period i.e. duplication. 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, he http://groups.google.com/group/micro...c3f233ba3336cc 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. -- |
#22
|
|||
|
|||
Design Question
On 17 Jul, 05:55, "Chris2"
wrote: 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. -- |
#24
|
|||
|
|||
Design Question
On Jul 20, 4:46 pm, Michael Gramelspacher wrote:
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 )); Are you sure this constraint works? No, that's why I said "something like this" g. 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. -- |
#25
|
|||
|
|||
Design Question
|
#26
|
|||
|
|||
Design Question
On Jul 20, 5:48 pm, Michael Gramelspacher wrote:
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. -- |
#27
|
|||
|
|||
Design Question
|
Thread Tools | |
Display Modes | |
|
|