Kenneth Downs wrote:
Alban Hertroys wrote:
The approach I tried was to have a "range" or "interval" type. You
place a column into a table named "resv_date" or whatever and it would
expand the definition into two columns, you'd get resv_date_beg and
resv_date_end. If you declared the "resv_date" column a primary key
column, it would build trigger code to detect overlaps and nesting and
reject those.
As I said, defining behavior and implementing it was not hard. I even
had foreign keys into ranges that were "smart". If the foreign key was
a single column instead of two, it would satisfy RI if the single value
was between the interval values in the parent table.
Been there, done that ;)
The problem comes from the split-personality of the "resv_date" column.
Sometimes its one column, sometimes its two. This made writing the
tools nasty and difficult, and I scratched it and (gasp!) did some
validation in client code.
What's the benefit of allowing it to be only one column?
I have it in mind to restore the feature, but in a different way. The
two columns should be defined separately, not as one, and then the
second of the two gets a flag setting, like:
column range_beg { primary_key: Y; }
column range_end { primary_key: Y; range_from: range_beg; }
The "range_from" setting ties one column to the other and should give me
all the behavior I had without all of the confusion. It would have
three effects:
1) Force range_end >= range_beg
2) Convert the primary key into overlap/nest exclusion
3) Allow a single column foreign key in another table to "know" that it
should do a within match instead of an equality match
And making that one column of a composite type would be just the thing,
I thought somewhere at the start of this thread (Thanks for mentioning
"composite types", Florian, couldn't remember what they're called).
Cheers,
--
Alban Hertroys
alban@xxxxxxxxxxxxxxxxx
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
