still on joining array/inline values was and is: design, ref integrity and performance

On Tue, 27 Oct 2009 10:54:06 +0000
Richard Huxton <dev(at)archonet(dot)com> wrote:

> > 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)
> > );

> > 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...

Trying to understand why I can't do the other way around I made
clearer to myself the constraints.

- There is no reason to have a password without an associated
recipient.
- There is no reason to have a password without an associated
resource resource.
- I don't want the same password for more than one resource.

So to satisfy the first 2 requirements... it looks to me the table
should be:

create table resources(
res int primary key
/* other characteristics of the resource... */
);

create table pw(
res int references resources(ref) on delete cascade,
password varchar(16) not null,
email varchar(64) not null,
/* Should I chose some unique constraint on the couples? which? */
);

To have the 3rd constraint I'd have a table:
create table pw_res(
password varchar(16) primary key,
res int references resources (res) on delete cascade
);

This comes handy for 2 reasons:
- it helps me to enforce the third constraint
- it makes it easier to find which resource is associated with a
password that will be a common search

But this introduces one more problem if I decide to delete a
password. This could happen for "shared" and non shared passwords.
I don't think it is something that may happen frequently... but it
may happen... and once you start to introduce mess in your data it
is hard to come back.

So a fk on pw.password may help... but... once I've that fk I can't
insert res,pw,email without pw baing in pw_res.

If I do the other way around inserting password(s) in pw_res I may
end up with passwords with no destination associated.

Surely I could wrap everything in a transaction so that if I can't
succede to insert email/pw records everything will be rolled back.

But I can't see how to make it cleaner.

I can get emails and associate them with a resource and a password
in one sql statement if I can defer the constraint check on password.
The next statement would be used to fill pw_res with distinct values
from pw.

If I do the other way around in case I need different passwords for
different emails I'll have to:
- count the emails and fill pw_res with as many password as needed.
I think I'll need generate_series.
- find a way to associate these passwords with emails

I don't know how to do the later.
Still supposing I knew how to write the sql statement that will take
values('email1', 'email2'...)
as suggested by Merlin Moncure in a different thread I can see some
pros and cons of these approaches.

The first approach requires a distinct/group by that may be
expensive.
The second one requires I keep in memory all the emails while the
first statement run.

I'd think that if memory consumption start to be an issue, that
means that the number of emails start to be too large to be loaded
from an HTTP request, so I could have it on file and load it in a
temp table and so on...

Still before I make any test I need to find how to associate emails
with passwords considering that I'd like to pass email "inline" the
SQL statement and I'll have the passwords in pw_res.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it