Re: database field "pointer"

Jeff Davis <list-pgsql-general(at)dynworks(dot)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