Database structure for multiple user types
-1
votes
1
answer
722
views
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**

Asked by Fapalz
(1 rep)
Apr 18, 2023, 11:18 AM
Last activity: May 29, 2025, 06:09 PM
Last activity: May 29, 2025, 06:09 PM