Re: Table design for basic user management

2009/11/7 Jasen Betts <jasen(at)xnet(dot)co(dot)nz>:
> On 2009-11-06, Thomas Løcke <thomas(dot)granvej6(at)gmail(dot)com> wrote:
[...]
>> I've come up with an initial design for this database:
>> http://pastebin.com/f5255453e
[...]
> CREATE TABLE log (
>    userid integer PRIMARY KEY REFERENCES REFERENCES user(id) ON DELETE CASCADE,
>    registered timestamp  DEFAULT now() NOT NULL,
>    lastvisit  timestamp  NOT NULL,
>    visits integer DEFAULT 0 NOT NULL
> );
>
> then you can log-in with a single query,
>
> update user set lastvisit=default where username='fred' and
> password=md5('salt'||'password') returning id;
[...]

I agree with using a salt, but you appear to be advocating a fixed
salt for everyone? Normally the salt is stored along with the
password hash, so you'd need one query to retrieve the salt and
another query to calculate the hash and compare it to the stored hash.
Something like this:

select salt from "user" where username='fred';
update "user" set lastvisit=default where username='fred' and
password=md5(salt||'password');

>> As far as I can see, PostgreSQL handles indexes automatically on primary and
>> unique columns. Is this assumption correct?
[...]

Yes, but foreign keys are not automatically indexed. So, e.g. your
log table has a userid column that references "user"(id). You would
have to create the index on log.userid yourself if that's what you
wanted.

--
Michael Wood <esiotrot(at)gmail(dot)com>