Database design for assign ticket or task

Below is my tables for tickets, tasks an assigned. now i have to assigned task or ticket to user as per my design i created only one table for assign both tasks and ticket. whenever task is assigned then ticket_id column is NULL and if ticket assigned then task_id is NULL so every time one column is NULL so i want to know this is right way for database design or i have to create two different table for assign task and ticket. Plz guide me.

tickets
-------------------------------------------------------------   
id  | title | description | priority | status | created_by  |
-------------------------------------------------------------
1   | tkt1  |this is tkt1 | 1        | 1      | 10          |
-------------------------------------------------------------
2   | tkt2  |this is tkt2 | 3        | 2      | 11          |
-------------------------------------------------------------

tasks
-------------------------------------------------------------------------   
id  | title | description | priority | status | ticket_id   |created_by |
-------------------------------------------------------------------------
1   | tkt1  |this is tkt1 | 2        | 1      | 1           | 23        |
-------------------------------------------------------------------------
2   | tkt2  |this is tkt2 | 2        | 2      | 2           | 23        |
-------------------------------------------------------------------------

Assigned
---------------------------------------------
 id | ticket_id | task_id   | assigned_to   |
---------------------------------------------
1   |   1       |   NULL    |       25      |
---------------------------------------------
2   |   NULL    |   1       |       25      |
---------------------------------------------
3   |   2       |   NULL    |       25      |
---------------------------------------------
4   |   NULL    |   2       |       25      |
---------------------------------------------

With most designs, there is some kind of trade-off. One down side with what you suggested is that it is not possible to find a candidate key in ASSIGNED. Consider what it would mean if someone added:

INSERT INTO ASSIGNED (ID, TICKET_ID, TASK_ID, ASSIGNED_TO)
VALUES (5, 1, NULL, 25);

From my perspective, you would be better off with two tables:

CREATE TABLE ASSIGNED_TICKETS
( USER_ID ... NOT NULL REFERENCES USERS (USER_ID)
, TICKET_ID ... NOT NULL REFERENCES TICKETS (TICKED_ID)
,    PRIMARY KEY (USER_ID, TICKET_ID) );

If a ticket cannot be assigned to several users, make TICKET_ID the primary key.

A similar table can be used for ASSIGNED_TASKS.

An alternative design is to use a super table, I'll call that WORK_UNIT for lack of a better word.

CREATE TABLE WORK_UNIT
( WORK_UNIT_ID ... NOT NULL PRIMARY KEY
, WORK_UNIT_TYPE CHAR(4) NOT NULL 
,   CHECK (WORK_UNIT_TYPE IN ('TICK', 'TASK'))
, ... );

The relationship between TICKET and TASK can be represented by a separate table:

CREATE TABLE TICKES_FOR_TASKS
( TASK_ID ... NOT NULL
      REFERENCES WORK_UNIT (WORK_UNIT_ID)
, TICKET_ID ... NOT NULL     
      REFERENCES WORK_UNIT (WORK_UNIT_ID)
,   PRIMARY KEY (TASK_ID, TICKET_ID) );

If you want to enforce the types in that relation (for example so that a TICKET can't consist of other TICKETS), you can add a UNIQUE constraint to WORK_UNIT:

ALTER TABLE WORK_UNIT ADD CONSTRAINT ...  
    UNIQUE (WORK_UNIT_ID, WORK_UNIT_TYPE)

and "inherit" WORK_UNIT_TYPE in TICKES_FOR_TASKS:

CREATE TABLE TICKES_FOR_TASKS
( TASK_ID ... NOT NULL
, TASK_TYPE CHAR(4) NOT NULL
,     CHECK ( TASK_TYPE = 'TASK' )
,     FOREIGN KEY ... (TASK_ID, TASK_TYPE) 
      REFERENCES WORK_UNIT (WORK_UNIT_ID, WORK_UNIT_TYPE)
, TICKET_ID ... NOT NULL
, TICKET_TYPE CHR(4) NOT NULL
,     CHECK ( TICKET_TYPE = 'TICK' )
,     FOREIGN KEY ... (TICKET_ID, TICKET_TYPE) 
      REFERENCES WORK_UNIT (WORK_UNIT_ID, WORK_UNIT_TYPE)
,   PRIMARY KEY (TASK_ID, TICKET_ID) );

I've heard of (but never used myself) DBMS that support CHECK constraints with selects. You could then avoid "inheriting" the type attributes. Another way to avoid this is to use triggers that throws an exception if a ticket or task is of the wrong type.

In this scenario you only need one table for ASSIGNMENTS:

CREATE TABLE ASSIGNED_WORK_UNITS
( USER_ID ... NOT NULL REFERENCES USERS (USER_ID)
, WORK_UNIT_ID ... NOT NULL REFERENCES WORK_UNIT (WORK_UNIT_ID)
, ... -- additional attributes
,    PRIMARY KEY (USER_ID, WORK_UNIT_ID) );

As, before. If a WORK_UNIT_ID can't be shared between USERS, make WORK_UNIT_ID the primary key.

No design is without flaws so you have to carefully consider for and against each possible one. Hopefully, his will give you some ideas on what to consider.

StackExchange Database Administrators Q#183309, answer score: 3