0

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 enter image description here

1
  • Note how WhiteOwl avoided UUIDs, spelled out names (not necessary to normalize simple strings), used fewer tables, etc.
    – Rick James
    Apr 19, 2023 at 0:22

1 Answer 1

0

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')
3
  • 1
    Thanks for the answer. And where is the best place to store login information in this case? I just need the ability to create clients and agents without registering individual users. And can you explain why you are using id instead of uuid
    – Fapalz
    Apr 19, 2023 at 10:27
  • Login? If you want to keep it simple, the Person table can also have a password field. If a password is empty - that user cannot login, if has something - can. But this assuming that client application is connecting to DB with some "super-user" password and the authentication would be on the client. Normal for web, bad for standalone application.
    – White Owl
    Apr 19, 2023 at 12:25
  • uuid or id - does not really matter in the example. As long as you see the primary key. But if start a discussion of uuid vs id - you would find almost equal amount of pros and cons for both, which eventually lead to a personal preferences. I gravitate toward id integer for this task, since in this case I doubt you would have more than 9223372036854775807 people to exhaust the int64 used by most all DBMS today. And work with integer is faster than with uuid, by minuscule amount, but tiny bit faster.
    – White Owl
    Apr 19, 2023 at 12:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.