Modeling a ticket system based on a set of disparate kinds of tickets?

Question

I'm working on a project that allows for the creation of "support tickets." These are not confined to something that needs fixing, however, so in a way they could more accurately be called "jobs."

For the frontend of the project, the tickets are to be created out of predefined "templates." I've listed three examples below:

1. Removing a discontinued product(s) from our online storefronts. This template would require information on which vendor the discontinued product(s) belong to, which product(s) is/are discontinued, the reasoning for the discontinuing.
2. Resolving product upload errors. Information would need to be provided on the upload's batch id, the number of errors needing fixing, from which vendor the errors came from.
3. Fixing a workstation. Information on the workstation number, what the problem is, and the urgency are fields that would be required.

Of course, each kind of ticket would share common attributes like the name of the ticket (a short summary of the issue/assignment), the issuer's user_id, the deadline, whether or not it has been resolved, etc.

I am trying to model this system relationally, but I am not sure if it's appropriate. The biggest struggle I'm having is how I can model and relate the different "kinds" of tickets. This seems perfect for some kind of inheritance, but should this be stored in a relational database?

It has felt natural to try some sort of EAV model, but I've heard that this should be avoided like the plague (I don't know whether or not this is an accurate evaluation).

Here is a diagram of my current attempt:

I've included the rest of the relations in the image for context.

The accompanying (generic) SQL code as generated by SQLEditor:

CREATE TABLE actions
(
  action_id INTEGER NOT NULL AUTO_INCREMENT  UNIQUE,
  name VARCHAR NOT NULL,
  PRIMARY KEY (action_id)
);

CREATE TABLE departments
(
  department_id INTEGER NOT NULL AUTO_INCREMENT  UNIQUE,
  name VARCHAR NOT NULL UNIQUE,
  PRIMARY KEY (department_id)
);

CREATE TABLE entities
(
  entity_id INTEGER NOT NULL AUTO_INCREMENT  UNIQUE,
  entity_name VARCHAR NOT NULL UNIQUE,
  PRIMARY KEY (entity_id)
);

CREATE TABLE activity_types
(
  activity_type_id INTEGER NOT NULL AUTO_INCREMENT  UNIQUE,
  entity_id INTEGER NOT NULL,
  name VARCHAR NOT NULL,
  PRIMARY KEY (activity_type_id)
);

CREATE TABLE objectives
(
  objective_id INTEGER NOT NULL AUTO_INCREMENT  UNIQUE,
  name VARCHAR NOT NULL,
  PRIMARY KEY (objective_id)
);

CREATE TABLE statuses
(
  status_id INTEGER NOT NULL AUTO_INCREMENT  UNIQUE,
  name VARCHAR,
  PRIMARY KEY (status_id)
);

CREATE TABLE notifications
(
  notification_id INTEGER NOT NULL AUTO_INCREMENT  UNIQUE,
  target_id INTEGER NOT NULL,
  active INTEGER NOT NULL,
  action_id INTEGER,
  closed INTEGER NOT NULL,
  activity_id INTEGER NOT NULL,
  PRIMARY KEY (notification_id)
);

CREATE TABLE ticket_keys
(
  ticket_key_id INTEGER NOT NULL AUTO_INCREMENT  UNIQUE,
  name VARCHAR NOT NULL UNIQUE,
  PRIMARY KEY (ticket_key_id)
);

CREATE TABLE tasks
(
  task_id INTEGER NOT NULL AUTO_INCREMENT  UNIQUE,
  ticket_id INTEGER NOT NULL,
  name VARCHAR NOT NULL,
  resolved INTEGER NOT NULL,
  PRIMARY KEY (task_id)
);

CREATE TABLE ticket_vals
(
  task_val_id INTEGER NOT NULL AUTO_INCREMENT  UNIQUE,
  ticket_key_id INTEGER NOT NULL UNIQUE,
  ticket_id INTEGER NOT NULL,
  val VARCHAR NOT NULL,
  PRIMARY KEY (task_val_id)
);

CREATE TABLE users
(
  user_id INTEGER NOT NULL AUTO_INCREMENT  UNIQUE,
  username VARCHAR NOT NULL UNIQUE,
  department_id INTEGER NOT NULL,
  first_name VARCHAR NOT NULL,
  last_name VARCHAR NOT NULL,
  hash VARCHAR NOT NULL,
  salt VARCHAR NOT NULL UNIQUE,
  PRIMARY KEY (user_id)
);

CREATE TABLE comments
(
  comment_id INTEGER NOT NULL AUTO_INCREMENT  UNIQUE,
  ticket_id INTEGER NOT NULL,
  commenter_user_id INTEGER NOT NULL,
  comment VARCHAR NOT NULL,
  PRIMARY KEY (comment_id)
);

CREATE TABLE targets
(
  target_id INTEGER NOT NULL AUTO_INCREMENT  UNIQUE,
  ticket_id INTEGER,
  task_id INTEGER,
  objective_id INTEGER,
  user_id INTEGER,
  department_id INTEGER,
  PRIMARY KEY (target_id)
);

CREATE TABLE sessions
(
  session_id INTEGER NOT NULL AUTO_INCREMENT  UNIQUE,
  user_id INTEGER NOT NULL,
  time_in VARCHAR NOT NULL UNIQUE,
  time_out VARCHAR UNIQUE,
  duration INTEGER,
  PRIMARY KEY (session_id)
);

CREATE TABLE tickets
(
  ticket_id INTEGER NOT NULL AUTO_INCREMENT  UNIQUE,
  date_created DATE NOT NULL,
  name VARCHAR NOT NULL UNIQUE,
  issuer_user_id INTEGER NOT NULL,
  deadline VARCHAR NOT NULL,
  resolved INTEGER NOT NULL,
  recurring INTEGER NOT NULL,
  recur_interval VARCHAR,
  objective_id INTEGER,
  status_id INTEGER NOT NULL,
  PRIMARY KEY (ticket_id)
);

CREATE TABLE activities
(
  activity_id INTEGER NOT NULL AUTO_INCREMENT  UNIQUE,
  user_id INTEGER,
  activity_type_id INTEGER NOT NULL,
  source_id INTEGER,
  PRIMARY KEY (activity_id)
);

ALTER TABLE activity_types ADD FOREIGN KEY (entity_id) REFERENCES entities (entity_id);

ALTER TABLE notifications ADD FOREIGN KEY (target_id) REFERENCES targets (target_id);

ALTER TABLE notifications ADD FOREIGN KEY (action_id) REFERENCES actions (action_id);

ALTER TABLE notifications ADD FOREIGN KEY (activity_id) REFERENCES activities (activity_id);

ALTER TABLE ticket_keys ADD FOREIGN KEY (ticket_key_id) REFERENCES ticket_vals (ticket_key_id);

ALTER TABLE tasks ADD FOREIGN KEY (ticket_id) REFERENCES tickets (ticket_id);

ALTER TABLE ticket_vals ADD FOREIGN KEY (ticket_key_id) REFERENCES ticket_keys (ticket_key_id);

ALTER TABLE ticket_vals ADD FOREIGN KEY (ticket_id) REFERENCES tickets (ticket_id);

ALTER TABLE users ADD FOREIGN KEY (department_id) REFERENCES departments (department_id);

ALTER TABLE comments ADD FOREIGN KEY (ticket_id) REFERENCES tickets (ticket_id);

ALTER TABLE comments ADD FOREIGN KEY (commenter_user_id) REFERENCES users (user_id);

ALTER TABLE targets ADD FOREIGN KEY (ticket_id) REFERENCES tickets (ticket_id);

ALTER TABLE targets ADD FOREIGN KEY (task_id) REFERENCES tasks (task_id);

ALTER TABLE targets ADD FOREIGN KEY (objective_id) REFERENCES objectives (objective_id);

ALTER TABLE targets ADD FOREIGN KEY (user_id) REFERENCES users (user_id);

ALTER TABLE targets ADD FOREIGN KEY (department_id) REFERENCES departments (department_id);

ALTER TABLE sessions ADD FOREIGN KEY (user_id) REFERENCES users (user_id);

ALTER TABLE tickets ADD FOREIGN KEY (issuer_user_id) REFERENCES users (user_id);

ALTER TABLE tickets ADD FOREIGN KEY (objective_id) REFERENCES objectives (objective_id);

ALTER TABLE tickets ADD FOREIGN KEY (status_id) REFERENCES statuses (status_id);

ALTER TABLE activities ADD FOREIGN KEY (user_id) REFERENCES users (user_id);

ALTER TABLE activities ADD FOREIGN KEY (activity_type_id) REFERENCES activity_types (activity_type_id);

So the idea was to store all of the possible attributes in ticket_keys and relate the relevant attributes to a ticket through ticket_vals. But this seems to be more of a workaround than a solution: there is no concept of a "template" with this model. Perhaps that is not even necessary?

One of the reasons I felt like it would be necessary to include the concept of a template is for privileges: only certain users can see certain templates. For instance, only admin can open a ticket for Uploading products from a new vendor. But feels like it'd be a pain given my current setup. Maybe I could just add a privilege_required to ticket_keys, but again it does not seem quite right.

I would appreciate any advice regarding my naive attempt and whether or not the relational model is a good fit for this project. Is a document store more appropriate?

Answer 1

You mentioned the term "template" in your description but don't use it in your design. So, first, you need table that lists all possible template types - template_type with id and name columns. As an example it may contains the following data:

Id    Name
 1    Removing a discontinued product(s) from online storefronts
 2    Resolving product upload error
 3    Fixing a workstation

Then you need storage for configuration of each template type - template. It contains records for each ticket_key which includes into particular template as well as reference to id column in template_type table. Now each particular ticket should have template_type_id column to reference to its template type.

And now, you can implement your authorization system by setting access rights for particular ticket_key in particular template.

EDIT: corrected according comment to the answer.