On 1/18/2010 6:52 PM, Colin Streicher wrote:
> On January 18, 2010 01:34:15 pm Tompkins Neil wrote:
>> I'm in the process of designing a login system to a secure web page using
>> MySQL. One of the features is we need to record and ensure that the user
>> password is different from any of the last four passwords he/she has used.
>> I was thinking of create four fields called Password1, Password2,
>> Password3 and Password4 to record the old passwords.
>> Is this a preferred method - or does anyone else have any recommendations ?
> I'm not an awesome database designer, most of what I do is code related stuff,
> I think what I would do for this is 1. hash the password( sha256/512 whatever)
> and then 2. store the hash in a string with delimiters. In that way, you solve
> 2 problems.
> You can store as many as you want to because you can just check hashes to make
> sure it isn't the same, and second, you aren't storing passwords in plain-
> text, which is a personal pet peeve.
As others appointed, having another table with old passwords is a good
"design" solution, and can allow you to have more than 4 passwords on
your history. But in fact your solution is the best solution for
performance and is called "denormalization", this solution gives good
performance because in 1 read you get all the passwords but has the
limitation of be "fixed" to only 4 passwords (which is not so bad
because you can add new columns as needed, you will never have 20
history passwords anyway, do you ?).
So, thats the trade, design vs performance, you should pick the best for
The solution proposed by Colin is another way to do it but, from the
good design perspective is NOT a good solution, is what its called a
"multivalued attribute" and all those should be avoided. But again, is
up to you.