Database structure for ticketing system

Question

I would like to create a ticketing PHP application, but I stucked at the planning of database structure.

I would have completely different type of ticket templates (user / shared folder / end-device processes etc..) which require different inputs.

The question is that, shoud I store them in one table, or should I create a separate table for every ticket group?

If I store them in one table, there will be many empty fields in the table because different tickets use different fields.

I think the common fields would be the unique ID, status and responsible person only, the others are different.

My preferred solution would be that if I store them in separate tables with the required fields only.

But this would cause troubles in a simple SELECT query when I would like to list every tickets. If I join them together during the query it will create a result table with many empty fields because of the difference between the structure of the tables which is not optimal. To avoid this I should run separate queries for every table which is not so simple.

What way would you choose? Could you please give me an advice? Thank you in advance!

Answer 1

Never put different entities in the same table. Yes, they have some common fields, but a bunch of empty columns is a clear signal that such design should be avoided, and good sign is that you are aware of it :) If we jump at the moment from the database world into application world with OO patterns and classes, this would be typically solved by inheritance (is-a relationship). How to enforce this in SQL (specifically, MySQL)?

Divide data in separate tables with one common table, let's call it ticket_template, that will contain common fields (you mentioned template_id, status and responsible person). Other tables, such as user_template will be created like this:

CREATE TABLE user_template(
    user_template_id int PRIMARY KEY REFERENCES ticket_template(template_id)
    ...
);

and they should keep fields specific to that particular entity. If you want a common report, write multiple SQL selects and show their results one after another. If you want all of them together, just list common fields together.

Caveat - this design does not prevent confused user to insert a ticket_template that is not related to any of concrete templates (such as user_template), or that is related to many of them (more than 1). If the database supports deferred constraints (MySQL unfortunately doesn't), that can be enforced too, read more here.