Enforcing validation rules by constraints

[Martin Vashko]

 

Brian Tkatch wrote:If the business rule is within the table itself, within that one record, use a CONSTRAINT. If it is multiple records in the same table, an aggregate column with fancy logic can be added, or a function based index.
If the business rule is amongst multiple tables, a materialized view (oracle) or indexed view (sql server) can be used, with constraints.

Not every database is this powerful.

Besides, I've come across really simple validation requirements that I don't know how to enforce declaratively even in Oracle (for example, in a table with START and END columns of the DATE type, the rule is that no two records in the table should overlap).

[Brian Tkatch]

 

Martin Vajsar wrote:

Brian Tkatch wrote:If the business rule is within the table itself, within that one record, use a CONSTRAINT. If it is multiple records in the same table, an aggregate column with fancy logic can be added, or a function based index.
If the business rule is amongst multiple tables, a materialized view (oracle) or indexed view (sql server) can be used, with constraints.

Not every database is this powerful.

Not for fancy VIEWs maybe, but for constraints and indexes they ought to be.

Besides, I've come across really simple validation requirements that I don't know how to enforce declaratively even in Oracle (for example, in a table with START and END columns of the DATE type, the rule is that no two records in the table should overlap).

Because the (middle) dates are not actually listed, that becomes a complex case, which is usually not enforced. Regardless, it can be done easily in Oracle with a materialized view, as the view can list out all the dates and make the column unique.

Regardless, this can be done in any database with a child table, which is technically the logical way to do it.

CREATE TABLE Date_Child ( The_Date DATE UNIQUE, Parent REFERENCES Parent_Table );

Start and end dates are a convenience that comes at the cost of not listing the individual dates. If you then want to apply constraints to the individual dates, you have to list them out somehow or rely on some detached logic in a trigger or elsewhere. But why use a bad implementation to support a bad implementation when you can simply remove the convenience that caused the issue in the first place?

[Martin Vashko]

I've split these posts off from the original thread.

Brian Tkatch wrote:Start and end dates are a convenience that comes at the cost of not listing the individual dates. If you then want to apply constraints to the individual dates, you have to list them out somehow or rely on some detached logic in a trigger or elsewhere. But why use a bad implementation to support a bad implementation when you can simply remove the convenience that caused the issue in the first place?

In one particular case I've encountered, the intervals can span years, while the resolution of the dates is in hours (and might be quarter hours in the future). Enumerating all the intermediate dates would multiply the number of needed rows by hundreds or thousands, impacting the speed of additions and deletions of such long records.

As always, it's probably a matter of striking the right balance (and perhaps I haven't managed to get it really right).

[Brian Tkatch]

 

In one particular case I've encountered, the intervals can span years, while the resolution of the dates is in hours (and might be quarter hours in the future). Enumerating all the intermediate dates would multiply the number of needed rows by hundreds or thousands, impacting the speed of additions and deletions of such long records.

Enterprise databases can easily handle that. Just list out each hour and partition by day.

Other databases can cheat by faking partitions, and just have a new table for each year. At well under 10,000 records, the impact should be small.