Re: database field "pointer"

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