Does it make sense to separate the table of user profile to that of the login details

Question

Currenly, I'm working on an inventory system. I have just started and now gathering user requirements. This is my first time to create a system from scratch so I still have questions regarding simple things.

My question is, does it make sense to separate the table of user profile to the login details. Example :

User Profile (table) :

o firstName
o middleName
o lastName
o dateLastModified
o userLastModified
o userLoginId

User Login Detail (table)

o loginId
o userName
o password
o userLastModified
o dateLastModified
o lastLogIn

Or the best practice would be to have 1 table for each? I've seen some system that combines those 2 tables into 1. And I'm confused because if I remember it right, when making tables, you should separate fields which you rarely change the value to the ones that change frequently, right?

So my question is 1, what is the best practice in making database tables for login, is it correct to use 2 tables like the one shown above? or should I put all fields in one table?

Answer 1

I have seen many systems that use User Information and User Login Details as separate tables which is really useful if the User is setup by an admin User and then the User can set their own password.

CREATE TABLE users (
   userID           integer NOT NULL,
   userFirstname    character(25) NOT NULL,
   userMiddlename   character(25),
   userLastname     character(25) NOT NULL,
   userModified     timestamp(4) without time zone,
   CONSTRAINT userID_key UNIQUE (userID)
);

CREATE TABLE credentials (
   userID          integer NOT NULL,
   credLogin       character(25) NOT NULL,
   credPassword    character(25) NOT NULL,
   credModified    timestamp(4) without time zone,
   credLastLogin   timestamp(4) without time zone
);

This means the password field can be Not Null and the User profile can exist without an entry into the Credentials table at all.

If the User will always be created with a password then it would make more sense to use one table only.

More than one table should only be used if it is necessary, for one to many relationships for example.