RE: New SQL Datatype RECURRINGCHAR

On Fri, 6 Jul 2001, David Bennett wrote:

<rest snipped>
> In either model you would:
> 
>     update master_table set status='OPEN_PENDING_SOMETHING' where status='OPEN'
> 
> This would not change, in fact, even in a normalized design you
> wouldn't change the lookup table (parent) key.  Perhaps you are
> misunderstanding my initial concept.  The MySQL 'enum' is close.  
> However, it is static and requires you to embed business data (your
> key list) in the DDL.  The idea I have here is to dynamically extend
> this list as needed.  I am not saying that the value can't relate to a
> parent (lookup) table.  It's just not necessary if the value is all
> that is needed.
You are making absolutely no sense. 

Let me break it down:


a) To do an update of a key to a different value, you would need to do
following:
1) look up the new value in entire table, find if its already exists
2) If it exists, good.
3) if it doesn't, pick a next number. (out of some sequence, I suppose) to
represent the key.
4) do the actual update.

Step 1 without an index is a killer. Then, you need to have a certain
'table' to map the existing key values to their numerical representations.

How would this 'table' get populated? On startup? On select?

Its one thing to take 'enum' datatype, which I wouldn't disagree too
much with. Its another thing to suggest this kind of a scheme, which
should be really done with views and rules.

I.E. instead of (as you would have) table a(...,  x recurringchar), 
you must have two things:

table a_real (..., x int4)
table lookup (x int4, varchar value)

Then, have a view:
create view a as select ..., value from a_real, lookup where
a_real.x=lookup.x

Then create a rule on insert: (syntax may be rusty)
create rule foo
on insert on table a
do instead
...whatever magic you need to do the actual inserton, lookup, etc.


> --Dave (Hoping some other SQL developers are monitoring this thread :)