I've now implemented the below. It ends up with a LOT of columns in
'usr_addrs', but I guess that's OK, it works. It looks like below
(again, simplified):
usrs{
usr_id serial,
addr_id_usr_mail int4, -- FK to addrs table
username var(54));
addrs(
addr_id serial,
addr var(54)};
usr_addr_type(
usr_addr_type_id serial,
type var(16)); -- one of which is 'mail'
usr_addrs{
usr_id int4 , --FK to usrs table
addr_id_usr_mail int4 , --FK to usrs table
addr_id int4 , -- FK to addrs table
usr_addr_type_id int4); -- FK to usr_addr_type table
Even though the address which is used for uniqueness of the usr is for
their 'mail' address, I could make a type of usr_addr_type called 'mail'
and have that listed in the 'user_addrs' table. This would make scripts
trying to find addresses of Usrs more consistent. The fields
'addr_id_usr_mail' and 'addr_id' would be the same though!
Dustin Sallings wrote:
>
> Around 03:09 on Feb 9, 2003, gearond@cvc.net said:
>
> # I have the following tables (mucho simplified):
> #
> # usrs{
> # serial usr_id,
> # var(54) username);
> #
> # addrs(
> # serial addr_id,
> # var(54) addr};
> #
> # usr_addr_type(
> # serial usr_addr_type_id,
> # var(16) type -- one of which is 'primary'};
> #
> # usr_addrs{
> # int4 usr_id, --FK to usrs table
> # int4 addr_id, -- FK to addrs table
> # int4 usr_addr_type_id -- FK to usr_addr_type table);
> #
> # ================================
> # My problem: The usrs can have as many addresses as there are types. The
> # addresses are also used for 'org_addrs' in combination with 'org_addr_types'.
> # (This explains the 'usr_addrs' table)
> #
> # I want to EFFECTIVELY do a unique index on:
> #
> # 'usrs.username' their 'primary' address in usr_addrs'. How would I have many
> # addresses in 'usr_addrs' for a particular address, but one of them used for
> # uniqueness of the of the 'usr'
> # ?
>
> The easiest way is to have a primary address ID on the user table
> then have a mapping for alternate addresses.
>
> Otherwise, you can do what you're asking with a trigger.
>
> --
Carpe Dancem ;-)
-----------------------------------------------------------------
Remember your friends while they are alive
-----------------------------------------------------------------
Sincerely, Dennis Gearon
