Database design for same items with various properties

Question

I'm just getting into database design and structures. I'm facing a problem now. Let's say I want to have a ticket database, there are three types of tickets:

Ticket #1

 ---------------------------
| id     creation_time  .... |
 ---------------------------

Ticket #2

 ------------------------------------------------
| id     creation_time    auto_closure_time .... |
 ------------------------------------------------

Ticket #3

 -------------------------
| id       permanent .... |
 -------------------------

Data types:

creation_time, auto_closure_time - datetime

permanent - boolean

Ticket #3 Does not have creation_time for specific reasons

Four dots (....) means some other columns which have all tickets

My question is: Should I separate these tables or merge them into one table which would look like this?

 -----------------------------------------------------------
| id        creation_time   auto_closure_time     permanent | 
| 1              xyz               NULL               0     |
| 2              xyz               xyz                0     |
| 3              NULL              NULL               1     |
 -----------------------------------------------------------

ID 1 represents Ticket #1 and so on..

What could be the benefits of separated tables and the merged one?

Thanks for all answers, appreciate your time.

Asking for pros & cons is not a valid question. Also options for this situation is a faq. [Re DB/SQL subtyping/inheritance](https://stackoverflow.com/q/3579079/3404097), [more](https://stackoverflow.com/q/190296/3404097), etc. Before considering posting please read the manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. — philipxy, Apr 06 '20 at 21:19
Start wuth the second and when the need arises , you can build the other tables from there — nbk, Apr 06 '20 at 21:43

score 2 · Accepted Answer · answered Apr 06 '20 at 22:57

I would have one table with only creation_time and auto_closure_time, and use an "impossible" value to signify permanence. That could be the beginning of time for creation, or end of time for closure (however defined). Type 1 tickets, as you depicted, simply get a NULL auto_closure_time.

The answer to "how many tables" is determined by the commonality of the properties of the entities represented. In your case, the only distinguishing features of the 3 kinds of tickets is whether or not it expires.

Database design for same items with various properties

1 Answers1