I'm trying to decide the best way to manage a change in requirements. Currently when an admin is assigned to a ticket, the ticket header record is given an owner and the ownerID field is updated with the value of the id which corresponds to the record in the TeamMembers table. The owner (Team member) is notified when a user (Customer) updates the ticket with a comment. I now need to add additional Team Members to the ticket so they are also notified.
- Make ownerID field ownerIDs and store a comma separated list of IDs in a string ?
- Add a new field additionalOwnerIDs and store any extra IDs after the main owner as a comma separated string ?
- Create a new table that simply links ticketID to assigned owners and have one row per owner ?
- Do something else ?
I want to deliver the new functionality as quickly as possible, repurposing the existing field might seem like the simplest way forward but it would involve changing the schema type of existing data so will need a migration script to rewrite hundreds of thousands of rows.
Adding a new field then wouldn't need as much migration work as it would be an empty string for all existing records and default to an empty string for new records.
A new link table seems the most programatically correct way (IMHO) but would require the most time to implement and test it and the migrations would be the most complicated in terms of extracting existing ticket owners into the new table. Changing the code for retrieving / updating an owner to query the new table. Then removing the defunct ownerID column from the existing table.
Have a missed anything? Which approach is the best in terms of maintaining the code base in the future?