First of all, I need to have a UNIQUE ID to manage all IDs from all 3 tables. For example, having an ID_MAIN where I could have all values from: ID_WORK, ID_INCIDENT and ID_TASK (W00x, I00x and T00x) with frequency = 11 on this case (4+4+3).
As you may have noticed, I have many common fields over the 3 tables. And there are also specific fields for every table.
I wanted to know which is the best way to create my data model. My general request is the following so far:
- Quantity of Tickets (Works/Incidents/Tasks) by: CREATE_DATE/GROUP/CATEGORY1/STATUS/PRIORITY
- Another thing to considerate is the ID_PARENT in table TASKS. The thing is that each TASK is assigned to a WORK or INCIDENT. That means that for example if I have in table TASKS ID_MAIN=T001, I could have ID_PARENT=I003, meaning that TASK belongs to an INCIDENT. I say this because I will have to determine which tasks are assigned to a WORK or an INCIDENT. Please, note that the field ID_PARENT is only available in table TASKS.