Alex,
I think I fully understand your position. Let me put wrap up our
conversation so far.
Given the application requirements:
1) contacts have a type.
2) new types must be added on the fly as needed.
3) types names rarely change.
4) the number of contacts should scale to support millions of records.
5) the number of types will be limited to under 64k
6) Users must be able to easily query contacts with readable types.
-----
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 ),
FOREIGNKEY ( 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
* 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
* The adhoc query user is now faced with the task of understanding 3 data tables.
-----
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.
