Re: still on joining array/inline values was and is: design, ref integrity and performance - Mailing list pgsql-general

On Wed, Oct 28, 2009 at 4:50 AM, Ivan Sergio Borgonovo
<mail@webthatworks.it> wrote:
>
> 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.

Not sure why this is a mess?  Cascaded deletes can clean up the data.
If you need it for some business reason add an active flag.

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

At some point you need business logic to enforce your business
requirements.  There is little point in trying to enforce business
constraints in the database.  Stick to pure low level data integrity
for the database and keep the business constraints at some higher
level.

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

Unless you're dealing with 100,000's of these things I think you're
engaging in a process of "premature optimization".  Group by can work
efficiently over millions of rows.

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

Do the simplest thing possible.  Get it working, then see if you have
any new problems you need to solve.  Every issue you've described so
far is database design 101 and should present no real problem.  I
think you're agonizing over nothing...

--
Peter Hunsberger