Thread: How to do unique users

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'
?

---------------------------------------------
This message was sent using CVC Internet's MailMan.
http://www.cvc.net

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.

--
SPY                      My girlfriend asked me which one I like better.
pub  1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
|    Key fingerprint =  87 02 57 08 02 D0 DA D6  C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________

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

Dennis Gearon wrote:
>
> The fields
> 'addr_id_usr_mail' and 'addr_id' would be the same though!
>

 Only for that one record that contained the mail addr of the user.


Carpe Dancem ;-)
-----------------------------------------------------------------
Remember your friends while they are alive
-----------------------------------------------------------------
                         Sincerely, Dennis Gearon