I am working on a ticket application where non agents can be assigned a ticket and they are called auditors. The way this current works is when filling out a ticket you must select a category. When the category is selected the ticket submitter is then presented with tags that are assign to that category. Users are required to select at least one.
Auditors are assigned to tickets by the tags that are chosen. On the back end you can assign one or more auditors to a tag.
So now we have three tables:
category
Id Name
tag
Id Name
category_Tag
Id category_id Tag_id
auditor_tag
id user_id Tag_id
This has become slightly more complicated because a tag that is assigned to more than one category could now have different auditors.
So for instance currently:
{Tag A} has {auditor A} assigned to it and {Tag A} is assign to {Category A}
{Tag A} can also be assigned to {Category B}
This now needs to be changed to
{Tag A} that is Assigned to {Category A} needs to have one or more Auditors assigned to it
{Tag A} that is assigned to {Category B} may have one or more different Auditors assigned to it.
So now I need:
category_tag
Id category_id Tag_id
PIVOT
category_tag_users
Id category_tag_id users_id
users
Id
I think this is possible but I am not sure if this would be the best way to approach this. I am not very familiar with pivot models but does this look like a situation where that would be used?
