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@magproductions.nl
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 //