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)
I've as input a list of emails and according to the kind of
passwords I've to generate I fill a table that is
create table pw_email(
password varchar(16),
email varchar(64)
);
So data inside may look like
/* bunch of shared passwords */
abcdefg, 1@example.com
abcdefg, 2@example.com
abcdefg, 3@example.com
abcdefg, 4@example.com
/* bunch of individual passwords */
abcdefg1, 1@example.com
abcdefg2, 2@example.com
abcdefg3, 5@example.com
abcdefg4, 6@example.com
Now each password is linked to the access of one or more resource.
I could just add the resource id to the pw_email table but:
- it is not normalized
- considering many password will be duplicated, I'll have to check
on a larger table to see which password give access to what
- I'd like to associate different passwords to different resource so
that one password doesn't grant access to more than one resource.
create table pw_resource(
res int references ...
password varchar(16)
);
Of course if one password is in pw_email but it is not in
pw_resource I have a problem.
But I've to generate password/email couples first before filling
pw_resource.
I could make the constraint deferrable, add a on delete cascade to
pw_email.password but:
- I'm not sure it is a good design
- I'm still not sure it can work as expected
Before testing if it can work I'd like to hear some comment on the
design.
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it