Manage login details?

1

I have a membership database and am wondering how/where to store the member's login information. In the same table that handles the member's information or in a different table that only handles the login information? Right now I store passwordHash, salt, iterations, etc. in a separate table, but I'm wondering if I should put it in the table with the member's other information. What is a good database design for handling login information?
Used in an asp.net application.......

2

It is better to have 2 separate tables.
One for Members and the other one for Member_LoginInfo.
You should have a MemberID as a primary key in the Member table and use it as a foreign key in the Member_LoginInfo table.

3

You could also look at off-loading authentication with Entra ID, Google etc

4

That's how I have it now, I was going to put it in the same table. But I'll probably keep it as it is now in 2 different tables :slight_smile:

5

hi hope this helps

CREATE TABLE Members (  
    MemberID INT PRIMARY KEY,  
    Name VARCHAR(255),  
    Email VARCHAR(255) UNIQUE,  
    DateOfBirth DATE,  
    CreatedAt DATETIME,  
    UpdatedAt DATETIME  
);  

CREATE TABLE Authentication (  
    AuthID INT PRIMARY KEY,  
    MemberID INT UNIQUE FOREIGN KEY REFERENCES Members(MemberID),  
    PasswordHash VARCHAR(255),  
    Salt VARCHAR(255),  
    Iterations INT,  
    LastLogin DATETIME,  
    PasswordResetToken VARCHAR(255),  
    PasswordResetExpires DATETIME,  
    TwoFactorSecret VARCHAR(255),  
    AccountLockoutUntil DATETIME,  
    FailedLoginAttempts INT  
);

This design is particularly useful in ASP.NET applications as it aligns well with ASP.NET Identity Framework's architecture, which also separates user and login information into different tables.