How many entity tables should I have? User table attribute role or three tables for the roles?

Question

Roles are handler, sitter, and client in the Pet Sitting Tracking Service.

Should 3 separate tables be made for each role or should one role table linked to user will be sufficient?

CREATE DATABASE PSIRT;

USE PSIRT;

CREATE TABLE ORDER(
    OrderNumber INT(20) NOT NULL,
    TypeOfOrder VARCHAR(255) NOT NULL,
    DateCreated DATE NOT NULL,
    ServiceState VARCHAR(50) NOT NULL,
    CommentID INT(20) NOT NULL,
    CONSTRAINT ORDER_PK PRIMARY KEY(OrderNumber),
    CONSTRAINT ORDER_FK FOREIGN KEY(CommentID) REFERENCES COMMENT(CommentID)
    );

CREATE TABLE USER(
    UserID INT(20) NOT NULL,
    LastName VARCHAR(80) NOT NULL,
    FirstName VARCHAR(80) NOT NULL,
    Role VARCHAR(50) NOT NULL,
    Phone VARCHAR(12) NOT NULL,
    EmailAddress VARCHAR(100) NOT NULL,
    OrderNumber INT(20) NOT NULL,
    IPAddress VARCHAR(15) NOT NULL,
    CONSTRAINT USER_PK PRIMARY KEY(UserID),
    CONSTRAINT USER_FK FOREIGN KEY(OrderNumber) REFERENCES ORDER(OrderNumber)
);

CREATE TABLE COMMENT(
    CommentID INT(20) NOT NULL,
    CommentDate DATE NOT NULL,
    CommentText VARCHAR(255) NULL
    UserID INT(20) NOT NULL,
    OrderNumber INT(20) NOT NULL,
    CONSTRAINT COMMENT_PK PRIMARY KEY(CommentID),
    CONSTRAINT COMMENT_FK FOREIGN KEY(UserID) REFERENCES USER(UserID),
    CONSTRAINT COMMENT_FK_ORDER FOREIGN KEY(OrderNumber) REFERENCES ORDER(OrderNumber)
);

Answer 1

In my opinion, no, you shouldn't create separate table for every role.

Currently, there are 3 roles. Imagine you create the whole application which is based on data model that uses one table per role. Everything goes OK, application works, data is being inserted, reports are printed ... cool.

Then, customer says that a new role should be added. What will you do? Create another table, modify all queries you wrote so far, modify all reports, everything. Suppose you do that (and spend a lot of time and effort ... not to mention actual cost of doing it).

Then, next month, yet another role appears. Will you do that again? You could, but you certainly should not.

If you keep everything in a single table, it is easy to add a new role - that's just INSERT into a table. All queries work, all reports work ... you don't have to do anything.

---

Though, consider creating a separate ROLE table which is then referenced from USER via foreign key constraint, e.g.

create table role
  (role_id  int(20)         constraint pk_role primary key,
   name     varchar(20)
  );
create table user
  (user_id  int(20)         constraint pk_user primary key,
   ...
   role_id  int(20)         constraint fk_user_role references role (role_id)
  );