How can I design Person table in MySQL?

I have two person type: Users and Clients.
Users can login the panel but clients can’t login the panel.
My current database: (but this design not good for me. I need to connect these tables to the Persons table.)

My database:

You can see these tables storing same data like: firstName, lastName… but the difference is that the customer does not have a password.

How can I connect these tables to Persons table.

• Users can login with email and password.
• Client can’t login but have email for contact.

I would be happy, If you answer it with a database diagram.

simply, use only one table with a column isclient, which determines if you can log in.

Or if you have lots of more columns for a client a column clientid, which is Null if user is no Client and else use the unique id to the table with clientmoreinformation.

Generally don’t have redundant columns or tables.

Generally from what I’ve seen is to ask if a client can be a user, and if a user can be a client. If the two lists do not overlap, then it is perfectly fine to treat them as two different entities. It would be no different than having a table for salespeople and a different table for sales inventory. Just because a computer desk has height and weight attributes, doesn’t make it a user like me.

If in the event that there is a lot of overlap between the lists, like if you were looking at regular members of a club and premium members, then it would be very appropriate to have a couple of additional columns in a single table that shows their status.

If you needed to reference an event that had both a user and a client, then a join table with foreign keys could be used to link them together.

This is an instance of exclusive subtypes. Read this SO answer for an in-depth treatment of the topic, I will only address the example provided in your question.

You are correct that your current design has flaws, namely there’s redundancy, and the relation (a person is either a client OR a user, but not both) is not clear or enforced.

So it does make more sense to have a Person entity with two subtypes, Client and User. Since the only difference between the two is a User has a Password, that is the only additional information stored with User. Client exists to enforce any relations specific to Clients and not Users.

ER Diagram

Since you requested a diagram, here is a logical model of the solution, with a few tweaks I will explain:

Physical implementation

It’s important the the relations between Client and User to Person are enforced through foreign keys. In MySQL, the exclusive subtype will need to be enforced through triggers.

CREATE TABLE PersonType
(
  PersonTypeCd CHAR(1)      NOT NULL
 ,Name         VARCHAR(50)  NOT NULL
 ,CONSTRAINT PK_PersonType PRIMARY KEY (PersonTypeCd)
 ,CONSTRAINT AK_PersonType UNIQUE (Name)
)
;

CREATE TABLE Person
(
  PersonId      INT           NOT NULL
 ,PersonTypeCd  CHAR(1)       NOT NULL
 ,Email         VARCHAR(64)   NOT NULL
  /* Everything else */
 ,CONSTRAINT FK_Person_Classified_By_PersonType FOREIGN KEY (PersonTypeCd) REFERENCES PersonType (PersonTypeCd)
 ,CONSTRAINT PK_Person PRIMARY KEY (PersonId)
 ,CONSTRAINT AK_Person UNIQUE (Email)
)
;

CREATE TABLE User
(
  UserId     INT           NOT NULL
 ,Password   VARCHAR(128)  NOT NULL
 ,Salt       VARCHAR(8)    NOT NULL
 ,CONSTRAINT FK_User_Is_Person FOREIGN KEY (UserId) REFERENCES Person (PersonId)
 ,CONSTRAINT PK_User PRIMARY KEY (UserId)
)
;

CREATE TABLE Client
(
  ClientId  INT  NOT NULL
 ,CONSTRAINT FK_Client_Is_Person FOREIGN KEY (ClientId) REFERENCES Person (PersonId)
 ,CONSTRAINT PK_Client PRIMARY KEY (ClientId)
)
;

Other considerations

These are the changes I made to your example:

1. Identifiers require context, so PersonId,UserId and ClientId in the parent tables, not just child tables.
2. Email should be unique and enforced through constraints.
3. Having three timestamps is unnecessary and doesn’t provide much use in terms of recording changes. Have one timestamp for when the record was updated (that includes creating) and store the prior versions of the data in an audit table.
4. Passwords shouldn’t be encrypted without a salt

Other things you may consider:

1. It’s possible for people to have more than one phone, so it’s worth considering modeling an entity PersonPhone with the primary key (PersonId,PhoneNbr) in case you will start collecting more than one phone number in the future.
2. If you will store more information than just the password for User, you will probably want an audit table for that entity as well.