Skip site navigation (1) Skip section navigation (2)

RE: New SQL Datatype RECURRINGCHAR

From: "David Bennett" <dbennett(at)bensoft(dot)com>
To: "Alex Pilosov" <alex(at)pilosoft(dot)com>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Subject: RE: New SQL Datatype RECURRINGCHAR
Date: 2001-07-07 18:02:55
Message-ID: NEBBIHDFALGPGDHNLKBBIELICJAA.dbennett@bensoft.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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 ),
    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

* 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.



In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2001-07-07 18:13:44
Subject: Re: Async PQgetResult() question.
Previous:From: Matthew HagertyDate: 2001-07-07 17:40:57
Subject: Re: Async PQgetResult() question.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group