I am developing a database that will store users of different types. Currently, there are only two types of users,
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:
- 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.
- 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).
- 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:
- 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.
- Doubts about the need for the "client" table, as it currently has no additional fields and complicates the system.