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
