RE: New SQL Datatype RECURRINGCHAR

On Sat, 7 Jul 2001, David Bennett wrote:

> -----
> In a nutshell you are recommending:
> -----
> 
>   create table contact_type (
>     code      int2,
>     type    char(16),
>     PRIMARY KEY ( code )
>   );
> 
>   create table contact (
>     number      serial,
>     name        char(32),
>     type          int2,
>     PRIMARY KEY ( number ),
>     FOREIGN KEY ( type ) REFERENCES contact_type ( code )
>   );
> 
>   create view contact_with_readble_type as (
>     select c.number as number,
>            c.name as name,
>            t.type as type
>     from
>            contact c,
>            contact_type t
>   );
> 
> * To build a type lookup table:
> 
>   1) Select type and code from contact_type
>   2) Build UI object which displays type and returns code
Just 'select distinct' on a view should do just fine. 

> * In order to insert a new record with this model:
> 
>   1) Look up to see if type exists
>   2) Insert new type
>   3) Get type ID
>   4) Insert contact record
This can be encapsulated with "ON INSERT" rule on a view.

> * The adhoc query user is now faced with
>   the task of understanding 3 data tables.
No, only one view. All the logic is encapsulated there.

> 
> -----
> With recurringchar you could do this easily as:
> -----
> 
>   create table contact (
>     number      serial,
>     name        char(32),
>     type          recurringchar1,
>     PRIMARY KEY ( number ),
>   );
> 
> * To build a type lookup table:
> 
>   1) Select distinct type from contact (optimized access to recurringchar
> dictionary)
>   2) Build UI object which displays and returns type.
> 
> * In order to insert a new record with this model:
> 
>   1) Insert contact record
> 
> * The adhoc query user has one data table.
> 
> -----
> 
> Granted, changing the value of contact_type.type would require edits
> to the contact records. It may be possible to add simple syntax to
> allow editing of a 'recurringchar dictionary' to get around isolated
> problem which would only exist in certain applications.
> 
> Actually, maybe 'dictionary' or 'dictref' would be a better name for
> the datatype.
These things belong in application or middleware (AKA views/triggers), not
in database server itself.

There are multiple problems with your implementation, for example,
transaction handling, assume this situation:

Tran A inserts a new contact with new type "foo", but does not commit.
Dictionary assigns value of N to 'foo'.

Tran B inserts a new contact with type foo. What value should be entered
in the dictionary? N? A new value? 

If a type disappears from database, does its dictionary ID get reused?

All these questions are not simple questions, and its not up to database
to decide it. Your preferred solution belongs in your triggers/views, not
in core database.