[FIXED] User table design seperating password related columns

Issue

I'm designing an application and I need to create an user registration system. I have the following table structure

Where it doesn't click for me is that should I separate all password related columns onto another table as PASSWORD and connect this to main user table with a foreign key. Having said that, currently passwords are derived with a key derivation algorithm meaning two passwords wouldn't yield the same output digest. However, I wonder if having the user table like this or with a foreign key connecting to the password related columns would increase the performance by any means?

Solution

You seem to have an interest in historical passwords. That suggests that you have the wrong data model. It sounds like you want a type-2 table -- one that keeps track of passwords over time:

create table user_passwords (
    user_password_id int auto_increment primary key,
    user_id int not null,
    password varchar(100),
    eff_date datetime not null,
    end_date datetime,
    constraint fk_user_passwords_user_id (user_id) references users(user_id)
);

When a user changes the password, you would then insert a new row into this table, adjusting the eff_date and end_dates.

Note: The purpose of doing this is not for performance. The purpose is to accurately represent the data that you seem to need for your application.

This doesn't include the "trials". I'm not sure what that really means and it probably doesn't need to be kept historically, so that can stay in the users table.

Answered By - Gordon Linoff