Database structure for multiple user types

Question

I am developing a database that will store users of different types. Currently, there are only two types of users, clients and agents. In the future, there may be a new type of user, "organization," which will create and manage agents. The system also includes administrators, implemented through roles.

The idea is to have a "users" table, which is mainly responsible for login functionality (email and password, token, and other technical fields), and additional tables for each of the corresponding user types, which have their own unique set of columns. For example, agents may have fields such as specialty, experience, diplomas, etc. Currently, clients do not have any unique fields, but this may change in the future.

However, as I have not managed a database of this complexity before, I am interested in how to organize a few aspects:

1. Firstly, users can be any of the aforementioned types. For example, if a user is logged in as an agent, they should still be able to place an order.
2. Secondly, it is necessary to provide the ability to create clients or agents without creating a user account. For example, if a client contacts the company by phone or email, without registering on the website. It is also possible to add the "organization" type, where the organization's administrator can create agents with or without login credentials (saving the record in the "agent" table, without recording in the "user" table).
3. Thirdly, I would like to draw attention to the "appointment" table. The table contains foreign keys for clients and agents, not users.

The main problems I am facing are:

1. Duplication of basic data (first_name, last_name) for clients and agents, as this data is stored in the "user" table, but I need to be able to create clients and agents without a user account.
2. Doubts about the need for the "client" table, as it currently has no additional fields and complicates the system.

Here's what I've got so far

Answer 1

There is an easy, one-table solution:

create table Person (
   id integer primary key,
   first_name varchar(30),
   last_name varchar(30),
   is_agent bool,
   is_client bool,
   is_organisation bool
)

Another, slightly more complicated solution would be to use a secondary table:

create table Person (
   id integer primary key,
   first_name varchar(30),
   last_name varchar(30),
)

create table Person_Role(
   person_id integer foreign key references Person(id),
   role char(1) check (role in ('A', 'C', 'O'),
   primary key (person_id, role)
)

Or you can go to a full scale EAV:

create table Person (
   id integer primary key,
   first_name varchar(30),
   last_name varchar(30),
)

create table Person_Attribute(
   person_id integer foreign key references Person(id),
   attr_name varchar(10),
   attr_value varchar(255)
   primary key (person_id, attr_name)
)

--- And have a user like:
insert into Person values(1, 'John', 'Doe')
insert into Person_Attribute values(1, 'role', 'agent')
insert into Person_Attribute values(1, 'role', 'client')