List:General Discussion« Previous MessageNext Message »
From:Justin French Date:September 5 2003 3:15am
Subject:advice on change of data design
View as plain text  
Hi all,

I'm trying to merge and existing list of 900-odd email-list subscribers 
into an existing membership system.

The existing system uses the userid (eg "Justin") as the primary key.  
Obviously, I don't have the leisure of asking 900-odd people what their 
preferred userid is, so I've decided that I need to change the way that 
users login from userid|pass to email|pass.

This way I can just send out a random password to each existing 

I have some concerns about members needing to type such a long email 
address in to login, but putting that aside, my main concern is that 
the email address should be something that can be changed, so it can't 
be the primary key.

I've come up with the following data design, which I'd appreciate 
comments on:

userid (INT 5, primary key)
email (varchar 255, unique)
password (varchar 32, md5hash)
firstname (varchar 50)
lastname (varchar 50)

So, when a user logs in, I check for a match on email and password, 
then assign the userid to the session, probably also assigning their 
first & last names as another session variable, for a human-readable 
name on message boards etc etc.

When the user changes his/her email address, it won't affect their 
primary keys, scattered across many tables... it will just affect how 
they log in.

Any comments?

Justin French

advice on change of data designJustin French5 Sep