Re: challenging constraint situation - how do I make it

[Alban Hertroys <alban(at)magproductions(dot)nl>]

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(at)magproductions(dot)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 //