So, I have made a package that handles all of our prospects, clients, and project management for the most part and I am slowly adding more and more features, but just now I hit a wall and I am looking for input.
The system has multiple users (The Staff), each which has its own Calendar and Alerts(reminders, automated alerts from the system).
I have a users table, and I have an alerts table.
My question is this:
What is the best way to handle this data if the alerts are capable of being assigned to MULTIPLE users? Currently my table is setup to take the UserID and AlertID. Obviously, that is best used for just one user.
Here are a few scenarios that I am familiar with....
1.) Simply just duplicate the alert for each user it's assigned to. Duplicate data sucks though.
2.) Create another table for alerts_to_users which will just hold the branching data to bring the two together. But again, this will have many many records for just one alert (assigned to multiple people)
3.) Change the UserID field on the alerts to a text field and just store each UserID separated by a delimiter which I can later parse.
Does anyone else know a better way to handle this? I want to avoid duplicate data. I don't want to have complex queries that slows the entire system down. And #3 would require a lot of processing of data which could slow it down quite a bit.
Thanks for your help in advance