Re: design, ref integrity and performance

Ivan Sergio Borgonovo wrote:
> On Tue, 27 Oct 2009 09:17:59 +0000
> Richard Huxton <dev(at)archonet(dot)com> 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(at)example(dot)com');
...
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(at)example(dot)com');
...
UPDATE pw_resource SET res=12345 WHERE password='abcde';

--
Richard Huxton
Archonet Ltd