Search Postgresql Archives

Re: design, ref integrity and performance

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Ivan Sergio Borgonovo wrote:
> On Tue, 27 Oct 2009 09:17:59 +0000
> Richard Huxton <dev@xxxxxxxxxxxx> wrote:
> 
>> Ivan Sergio Borgonovo wrote:
>>> Hi,
> 
>>> I've to generate unique password and associate them with emails.
>>> Association with emails is just to mail the password, email +
>>> password aren't "the password", just the password is.
> 
>>> So a bunch of emails may be associated with the same password.
> 
>>> So there are 2 kind of passwords:
>>> - shared, multiple use
>>> - individual (they could be multiple or single use)
> 
>> So are you saying I login using *just* a password, not using my
>> email as a user-name?
> 
> yes
> 
>> How do you know who is logging in and what does it mean for the
>> password to be shared?
> 
> I don't care who "logged in".
> Shared just mean several people could use a resource providing the
> same password.
> They are a sort of "promotional codes". Some of them can be shared,
> I don't mind if people give them to others provided they let our
> service be known by others.
> Then there are unique passwords on which we may or may not check if
> they are used by multiple users of the site... but that's another
> problem.

OK. So not really passwords at all.

> Association between email and password is just meant to build up a
> queue for mailing and there is no uniqueness constraint on
> (password, email) pair.

> create table pw_email(
>   password varchar(16),
>   email varchar(64)
> );

> create table pw_resource(
>   res int references ...
>   password varchar(16)
> );

So the tables are clear enough, with pw_resource having "password" as a
primary key.

> But I've to generate password/email couples first before filling
> pw_resource.

The simplest thing would be to do them the other way around, but
assuming you can't...

The "most relational" way would be to have a separate table containing
just passwords, then reference that from both pw_resource and pw_email.

INSERT INTO passwords VALUES ('abcde');
INSERT INTO pw_email VALUES('abcde', 'someone@xxxxxxxxxxx');
...
INSERT INTO pw_resource VALUES ('abcde', 12345);

Failing that you could allow nulls for pw_resource.res and do something
like:

INSERT INTO pw_resource VALUES ('abcde', null);
INSERT INTO pw_email VALUES('abcde', 'someone@xxxxxxxxxxx');
...
UPDATE pw_resource SET res=12345 WHERE password='abcde';

-- 
  Richard Huxton
  Archonet Ltd

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux