Thread: database field "pointer"

    I was wondering is there is a good method to make a database field a
pointer, similar to C. Here is an example I made up of why this could be
useful:
    Suppose I have a table 'coworkers' with 2 email address fields: work_email
and home_email. It would be useful to have another field that was something
like 'preferered_email' that pointed to one or the other. Then, updates would
only need to happen once, and it would be easy to change back and forth.
    Tell me if there is some better, more sensible method to accomplish this
task.

Thanks,
    Jeff Davis

CREATE TABLE coworkers (
    id serial PRIMARY KEY,
    name varchar(63)
);

CREATE TABLE email_types (
    type VARCHAR(10) PRIMARY KEY
);

INSERT INTO email_types VALUES ('home');
INSERT INTO email_types VALUES ('work');

CREATE TABLE emails (
    owner INTEGER REFERENCES coworkers,
    type INTEGER REFERENCES email_types, --You could have a CHECK
        --instead offcourse.
    email VARCHAR(127) PRIMARY KEY,
    preferred BOOLEAN,
    UNIQUE(owner, preferred),
    UNIQUE(type, owner)
);

No guarantees, I have not run it, but I think it should work.


On Wed, 10 Oct 2001 13:39:31 -0700, Jeff Davis <list-pgsql-general@dynworks.com> wrote:
>     I was wondering is there is a good method to make a database field a
> pointer, similar to C. Here is an example I made up of why this could be
> useful:
>     Suppose I have a table 'coworkers' with 2 email address fields: work_email
> and home_email. It would be useful to have another field that was something
> like 'preferered_email' that pointed to one or the other. Then, updates would
> only need to happen once, and it would be easy to change back and forth.
>     Tell me if there is some better, more sensible method to accomplish this
> task.
>
> Thanks,
>     Jeff Davis
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Aasmund Midttun Godal

aasmund@godal.com - http://www.godal.com/
+47 40 45 20 46

Jeff Davis <list-pgsql-general@dynworks.com> writes:

> I was wondering is there is a good method to make a database field a
> pointer, similar to C. Here is an example I made up of why this could be
> useful:
> Suppose I have a table 'coworkers' with 2 email address fields: work_email
> and home_email. It would be useful to have another field that was something
> like 'preferered_email' that pointed to one or the other. Then, updates would
> only need to happen once, and it would be easy to change back and forth.
> Tell me if there is some better, more sensible method to accomplish this
> task.

The "SQLish" way to do this would be:

create table email (
        id serial primary key,
        user_id integer references my_user(id),
        addr text not null
);

create table my_user (
        id serial primary key,
        name text not null,
        preferred_email integer references email(id)
);

To get a user's preferred email:

select email.addr from email, my_user
  where my_user.name = 'Joe Blow'
    and email.id = my_user.preferred_email;

To get all emails for a user (so you can select a new preferred
email):

select email.id, email.addr from email, my_user
  where my_user.name = 'Joe Blow'
    and email.user_id = my_user.id;

Then,

update my_user set preferred_email = <one of the IDs>
  where name = 'Joe Blow';

There are probably better ways to do this but this is the one that
sprang to mind.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863